Transferspreadsheet Issues - Hidden Worksheets 1

[mmogul]

I have two issues transferring spreadsheets into an access table.

1) How do I skip hidden worksheets?
2) After I close the application, excel is still running as a process and the system thinks the spreadsheet is still open. How do I close it?

Here's the core part of my code:

Set objexcel = CreateObject("Excel.Application")
'objExcel.Visible = True

strFileName = filePath & fileName

Set objWorkbook = objexcel.Workbooks.Open(strFileName)
Set colWorksheets = objWorkbook.Worksheets

For Each objWorksheet In colWorksheets


Set objRange = objWorksheet.UsedRange
strWorkSheetName = objWorksheet.Name & "!" & objRange.Address(False, False)

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

Next
Objexcel.quit

 

[Remou]

How about:

<...>
        If objWorksheet.Visible = True Then
            DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, strWorkSheetName
        End If

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

 

[mmogul]

Hey Remou,

Thanks so much for taking the time to answer my question.

I really appreciate it.

Mark

 

[mmogul]

After my program completes, if I look at the Windows Task manager, I still see an Excel process running. Here is main part of my code. What am I doing wrong?

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
Else
End if
Next


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