G
Guest
I have the folowing code to open an Excel file from a module in access:
Filename = Dir("Q:\Furnace Files" & "\*.csv")
Do While Filename <> ""
filepath = "Q:\Furnace Files\" & Filename
Length = Len(Filename) - 4
Set xlApp = CreateObject("Excel.Application")
Set excelworkbook = xlApp.Workbooks.Open(filepath)
excelworkbook.Application.Visible = True
excelworkbook.Application.Windows(Filename).Visible = True
FinishTemp = 300 'temp that it is okay to remove parts
excelworkbook.Sheets(Left(Filename, Length)).Activate
excelworkbook.activesheet.Cells(3, 1).Select
numrows = 0 'Find the number of rows
Do While numrows = 0
i = i + 1
If excelworkbook.activesheet.Cells(i, 1) = "" Then numrows = i - 1
Loop
i = 0
If numfiles = 1 Then begintime = excelworkbook.activesheet.Cells(3, 1)
'Utilization Calculation
For i = 3 To numrows 'Loop through the rows to find when parts
have cooled down
For j = 6 To 17 'Checks the thermocouples
If excelworkbook.activesheet.Cells(i, j) > Max And _
excelworkbook.activesheet.Cells(i, j) < 2400 And _
excelworkbook.activesheet.Cells(i, 20) = 150 Then
Max = excelworkbook.activesheet.Cells(i, j)
Row = i
End If
Next j
Then I do some more calculations in a similar fashion. The problem is that
the process is very slow. I'm opening around 30 files with about 2000 lines
each. Is there a more efficient way to open and call the file. Thanks.
Filename = Dir("Q:\Furnace Files" & "\*.csv")
Do While Filename <> ""
filepath = "Q:\Furnace Files\" & Filename
Length = Len(Filename) - 4
Set xlApp = CreateObject("Excel.Application")
Set excelworkbook = xlApp.Workbooks.Open(filepath)
excelworkbook.Application.Visible = True
excelworkbook.Application.Windows(Filename).Visible = True
FinishTemp = 300 'temp that it is okay to remove parts
excelworkbook.Sheets(Left(Filename, Length)).Activate
excelworkbook.activesheet.Cells(3, 1).Select
numrows = 0 'Find the number of rows
Do While numrows = 0
i = i + 1
If excelworkbook.activesheet.Cells(i, 1) = "" Then numrows = i - 1
Loop
i = 0
If numfiles = 1 Then begintime = excelworkbook.activesheet.Cells(3, 1)
'Utilization Calculation
For i = 3 To numrows 'Loop through the rows to find when parts
have cooled down
For j = 6 To 17 'Checks the thermocouples
If excelworkbook.activesheet.Cells(i, j) > Max And _
excelworkbook.activesheet.Cells(i, j) < 2400 And _
excelworkbook.activesheet.Cells(i, 20) = 150 Then
Max = excelworkbook.activesheet.Cells(i, j)
Row = i
End If
Next j
Then I do some more calculations in a similar fashion. The problem is that
the process is very slow. I'm opening around 30 files with about 2000 lines
each. Is there a more efficient way to open and call the file. Thanks.