Reading Worksheets in an Excel Workbook

[mmogul]

I have an application where I am loading data from an excel workbook(s) that can have multiple worksheets including hidden worksheets. I want to skip the hidden worksheets.

My issues are:

1) Do I need to actually open excel to read the worksheets? This slows the process. When I have an application with only one sheet in a workbook, I can run this without opening Excel.

2) When this code finishes, an instance of Excel is still running -- this can be seen in the Windows Task Manager. I also get messages that pop up after the processing is completed that states: "File now available". I wish to avoid these messages and not have any instances of Excel running when the application completes.

This is my core code. Please let me know what I can do to improve it. Thanks.

Set objExcel = CreateObject("Excel.Application")

strFileName = filePath & fileName

Set objworkbook = objExcel.Workbooks.Open(strFileName)
Set colWorksheets = objworkbook.Worksheets

Dim a As String


For Each objWorkSheet In colWorksheets
If objWorkSheet.Visible Then ' This keeps hidden files from being processed
DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, strWorkSheetName
' This data is then processed ....
Else
End if
Next


Set colWorksheets = Nothing
objworkbook.Close
Set objworkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

 

[lbigk]

I don't believe that you can load the data without Excel being open, but you can close it at the end of the code to avoide an Application running on the background.

And, you can turn off warnings at the beginning of the code: DoCmd.SetWarnings False

Hope this helps.

 

[mmogul]

Is the method I used to try to close it correct? It does not appear to be since I still have an instance of excel running after the code completes. I don't believe the warning I receive is a vb type warning but a windows warning letting me know the file is now available.

 

[PHV]

Anyway, strWorkSheetName is never populated.
What about this ?
For Each objWorkSheet In colWorksheets
If objWorkSheet.Visible Then ' This keeps hidden files from being processed
DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, objWorkSheet.Name & "$"
' This data is then processed ....
End if
DoEvents
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmogul]

PHV- thanks for the response. I probably was not clear with my problem.

The problem is not how to process the spreadsheet and its worksheets (I just didn't show populating strWorkSheetName). The problem comes after processing.

After I complete processing, you can see that I try to close the workbook and close Excel - yet Excel still continues to run. I can see this by going into the Windows Task manager.

Any thoughts?

 

[PHV]

Normally, the workbook shouldn't be open at the time of the transfer.
You may try something like this:

Set objExcel = CreateObject("Excel.Application")
strFileName = filePath & fileName
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
For i = 1 To objWorkbook.Worksheets.Count
  If objWorkbook.Worksheets(i).Visible Then
    strSheets = strSheets & vbLf & objWorkbook.Worksheets(i).Name
  End If
Next
objWorkbook.Saved = True
objWorkbook.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
DoEvents
For Each strWorkSheetName In Split(Mid(strSheets, 2), vbLf)
  DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, strWorkSheetName & "$"
  ' This data is then processed ....
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmogul]

Thanks -- I'll give that a try.

 

[mmogul]

PHV -- your approach is helping me. One question:

In your transferspreadsheet statement ... what does the "$" do (at the end of the statement)?

DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, strWorkSheetName & "$"

 

[SkipVought]

The trailing $, is part of the sheet name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}