Openning an Excel File in Access

[joshroberts]

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.

 

[strive4peace]

Hi Josh,

you can find the number of rows like this:

numrows = excelworkbook.activesheet.UsedRange.Rows.Count


make sure you release your object variables when you are done:

on error resume next
excelworkbook.close
Set excelworkbook = nothing
xlApp.quit
Set xlApp = nothing

There may be a faster way to access Excel data -- like linking to it
instead of using automation

in case Excel is already open, this is how I initialize xlApp for
automation...

'~~~~~~~~~~~~~~~~
On Error GoTo Proc_Err

Dim xlApp As Excel.Application, booClose as boolean

'if Excel is already open, use that instance
booClose = false
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo error_handler

'What did we find?...
If TypeName(xlApp) = "Nothing" Then
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
booClose = true
End If

'... more statements

Proc_Exit:
On Error Resume Next
If TypeName(xlApp) <> "Nothing" Then
if booClose then xlApp.Quit
Set xlApp = Nothing
End If

Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " ProcedureName"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume

Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Klatuu]

You are doing it correctly. Using COM to work with an Excel Application
Object is just slow. That is because everything first has to go through
Access, then it goes to the Excel Application and has to go through it. So,
basically, you are running two applications at once.