matrix20xx
09-28-2007, 12:45 AM
Hi, current I am writing a VBA to import a file (which may exceed 65536 rows of data) into excel. Then I want it to search through all the worksheets in the workbook for a word, and get the neccessary data from all the worksheets. Paste it in another worksheets.
Currently, I am able to import the file and split it into 2 or more sheets. But I cannot search all the worksheets for the specific word :banghead: . It return an error msg as mention in the title (the error is the highlighted in RED :help ).
THANKS in advance.
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim ws As Worksheet
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Remove Message From Status Bar
Application.StatusBar = False
I = 0
For Each ws In Worksheets
m = m + WorksheetFunction.CountIf(ws.Cells, "*PRES CHAMBER*")
Next
For Each ws In Worksheets
For n = 1 To m
ws.Cells.Find(What:="PRES CHAMBER", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWithinWorkbook, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
'row number
r = ActiveCell.Row
'column number
c = ActiveCell.Column
'time of the when the process started
tm = Left(Split(Cells(r - 1, c), " ")(1), 8)
I = I + 1
'copy the required raw data from the excel
PCA = Split(Cells(r + 0, 1), "'")(2)
TWX = Split(Cells(r + 1, 1), "'")(2)
TWY = Split(Cells(r + 2, 1), "'")(2)
TR = Split(Cells(r + 3, 1), "'")(2)
ALCPX = Split(Cells(r + 4, 1), "'")(2)
ALCPY = Split(Cells(r + 5, 1), "'")(2)
ALCPR = Split(Cells(r + 6, 1), "'")(2)
ACurrent = Split(Cells(r + 7, 1), "'")(2)
ATarget = Split(Cells(r + 8, 1), "'")(2)
BCurrent = Split(Cells(r + 9, 1), "'")(2)
BTarget = Split(Cells(r + 10, 1), "'")(2)
Offset = Split(Cells(r + 11, 1), "'")(2)
Humidity = Split(Cells(r + 12, 1), "'")(2)
'Front
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.FontStyle = "Bold"
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").HorizontalAlignment = xlCenter
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").VerticalAlignment = xlBottom
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.Name = "Arial"
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.Size = 12
'Lengend
Workbooks("Book1.xls").ActiveSheet.Cells(5, 1) = "DATE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 2) = "TIME"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 3) = "PRES CHAMBER AIR"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 4) = "TEMP WAFER X"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 5) = "TEMP WAFER Y"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 6) = "TEMP RETICLE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 7) = "ALCP WAFER X"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 8) = "ALCP WAFER Y"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 9) = "ALCP RETICLE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 10) = "PRES A CURRENT"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 11) = "PRES A TARGET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 12) = "PRES B CURRENT"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 13) = "PRES B TARGET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 14) = "LC FCS OFFSET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 15) = "HUMIDITY"
'Format row
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 1) = Format(Date, "long date")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 2) = Format(Time, "long time")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 3) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 4) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 5) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 6) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 7) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 8) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 9) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 10) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 11) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 12) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 13) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 14) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 15) = Format(dblNumber, "6")
'paste it into the workbooks
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 1) = dt
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 2) = tm
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 3) = PCA
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 4) = TWX
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 5) = TWY
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 6) = TR
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 7) = ALCPX
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 8) = ALCPY
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 9) = ALCPR
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 10) = ACurrent
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 11) = ATarget
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 12) = BCurrent
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 13) = BTarget
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 14) = Offset
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 15) = Humidity
Next
'create new sheet
Workbooks("Book1.xls").Sheets.Add Before:=ActiveSheet
Next
End Sub
Currently, I am able to import the file and split it into 2 or more sheets. But I cannot search all the worksheets for the specific word :banghead: . It return an error msg as mention in the title (the error is the highlighted in RED :help ).
THANKS in advance.
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim ws As Worksheet
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Remove Message From Status Bar
Application.StatusBar = False
I = 0
For Each ws In Worksheets
m = m + WorksheetFunction.CountIf(ws.Cells, "*PRES CHAMBER*")
Next
For Each ws In Worksheets
For n = 1 To m
ws.Cells.Find(What:="PRES CHAMBER", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWithinWorkbook, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
'row number
r = ActiveCell.Row
'column number
c = ActiveCell.Column
'time of the when the process started
tm = Left(Split(Cells(r - 1, c), " ")(1), 8)
I = I + 1
'copy the required raw data from the excel
PCA = Split(Cells(r + 0, 1), "'")(2)
TWX = Split(Cells(r + 1, 1), "'")(2)
TWY = Split(Cells(r + 2, 1), "'")(2)
TR = Split(Cells(r + 3, 1), "'")(2)
ALCPX = Split(Cells(r + 4, 1), "'")(2)
ALCPY = Split(Cells(r + 5, 1), "'")(2)
ALCPR = Split(Cells(r + 6, 1), "'")(2)
ACurrent = Split(Cells(r + 7, 1), "'")(2)
ATarget = Split(Cells(r + 8, 1), "'")(2)
BCurrent = Split(Cells(r + 9, 1), "'")(2)
BTarget = Split(Cells(r + 10, 1), "'")(2)
Offset = Split(Cells(r + 11, 1), "'")(2)
Humidity = Split(Cells(r + 12, 1), "'")(2)
'Front
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.FontStyle = "Bold"
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").HorizontalAlignment = xlCenter
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").VerticalAlignment = xlBottom
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.Name = "Arial"
Workbooks("Book1.xls").ActiveSheet.Range("A5:O5").Font.Size = 12
'Lengend
Workbooks("Book1.xls").ActiveSheet.Cells(5, 1) = "DATE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 2) = "TIME"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 3) = "PRES CHAMBER AIR"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 4) = "TEMP WAFER X"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 5) = "TEMP WAFER Y"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 6) = "TEMP RETICLE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 7) = "ALCP WAFER X"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 8) = "ALCP WAFER Y"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 9) = "ALCP RETICLE"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 10) = "PRES A CURRENT"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 11) = "PRES A TARGET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 12) = "PRES B CURRENT"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 13) = "PRES B TARGET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 14) = "LC FCS OFFSET"
Workbooks("Book1.xls").ActiveSheet.Cells(5, 15) = "HUMIDITY"
'Format row
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 1) = Format(Date, "long date")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 2) = Format(Time, "long time")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 3) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 4) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 5) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 6) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 7) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 8) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 9) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 10) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 11) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 12) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 13) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 14) = Format(dblNumber, "6")
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 15) = Format(dblNumber, "6")
'paste it into the workbooks
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 1) = dt
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 2) = tm
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 3) = PCA
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 4) = TWX
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 5) = TWY
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 6) = TR
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 7) = ALCPX
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 8) = ALCPY
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 9) = ALCPR
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 10) = ACurrent
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 11) = ATarget
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 12) = BCurrent
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 13) = BTarget
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 14) = Offset
Workbooks("Book1.xls").ActiveSheet.Cells(5 + I, 15) = Humidity
Next
'create new sheet
Workbooks("Book1.xls").Sheets.Add Before:=ActiveSheet
Next
End Sub