I've looked at Wait()...
But that seems to just be a pre-defined amount of time to wait
Since changing some code, it's become necessary to have my code wait until an external .bat file finishes processing. Sometimes this can be a matter of seconds, but sometimes it can take minutes. So, obviously...the wait() method wouldn't be the way to go as I'd have no idea how long it would take to profile the disc with the MD5 check.
This is a snippet of my existing code (my full code is around 14 pages, I think)--this shows the sequence of events.
I have it prompt the user before clicking OK to make sure they entered everything correctly.
If the user hits OK (to continue), it then will run the .bat file, which, as I mentioned, can run very quickly or very slowly depending on what's on the disc.
The problem arises in that it wants to import the data from the CSV .txt file before the .bat file is finished writing the contents to the text file, so only some of the data gets entered. I need to force the rest of the code to wait until the .bat file is done.
Suggestions?
[edit: for some reason, it keeps screwing up my code...deleting parts of it & submitting the you entered: "&vb script:
Crap...it's still doing the annoying stuff...even outside the code tags. I'm adding semicolons to the start of each line to hopefully fool it. Obviously, the semicolons should not be there (I know)
;BeforeScriptRun:
; lastPrompt = MsgBox("You entered: " & vb script:
CODE
BeforeScriptRun:
lastPrompt = MsgBox("You entered: " & vb script:
'THIS CODE IS USED TO IMPORT THE DATA FROM THE CSV .TXT FILE WRITTEN BY FILELIST.EXE:
If sFilename <> False Then
Sheets("Temp").Select '<--this makes temp the active sheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFilename, Destination:=Range("B1"))
'.Name = "CSV"
Worksheets("Temp").Cells.ClearContents
Columns("A:I").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll Down:=-15 '! check this out
Range("B1").Select
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 4 'if want to skip headers, make this 4, if
'want to have headers, make =3
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Temp").Select
With ActiveSheet
nBottomRow = .Range("B" & .Rows.Count).End(xlUp).Row
'This represents # completed rows in column B of sheet Temp
' MsgBox (nBottomRow & " is the last row of column B in Temp") 'Old Diagnostic code; Ignore
End With
Set shtMain = Sheets("Main")
Set shtTemp = Sheets("Temp")
Set shtFull = Sheets("Full")
Sheets("Temp").Select
r1 = shtFull.Range("A" & Rows.Count).End(xlUp).Row + 1
' MsgBox (r1 & " is r1, or the cell after the last non-blank row") ' AGAIN, commenting out
' to avoid user having to click. This option was for diagnostic purposes during development
rowCountTemp = 0
For rowOfAFull = r1 To r1 + nBottomRow - 1
rowCountTemp = rowCountTemp + 1
shtFull.Cells(rowOfAFull, 1) = "Disc " & DiscNumber
shtFull.Cells(rowOfAFull, 2) = shtTemp.Cells(rowCountTemp, 2).Text
shtFull.Cells(rowOfAFull, 3) = shtTemp.Cells(rowCountTemp, 3).Text
shtFull.Cells(rowOfAFull, 4) = shtTemp.Cells(rowCountTemp, 4).Text
shtFull.Cells(rowOfAFull, 5) = shtTemp.Cells(rowCountTemp, 5).Text
shtFull.Cells(rowOfAFull, 6) = shtTemp.Cells(rowCountTemp, 6).Text ' I used .Text to get around issue of date screwing up
shtFull.Cells(rowOfAFull, 7) = shtTemp.Cells(rowCountTemp, 7).Text
shtFull.Cells(rowOfAFull, 8) = shtTemp.Cells(rowCountTemp, 8).Text
shtFull.Cells(rowOfAFull, 9) = shtTemp.Cells(rowCountTemp, 9).Text
Next rowOfAFull
I give up...no matter what I do it's screwing stuff up. I guess if you want to see my full code, pm me & I'll have to use my personal email to send it.
This post has been edited by cmount: 19 Aug, 2008 - 05:16 AM
| 