[RESOLVED] Docmd.TranferSpreadSheet problem

[DebbieInFlorida]

Hi,

I am trying to import an Excel file into Access, I have followed all the forums and blogs, but nothing happens, it hangs on the DoCmd line..
What am I missing???

Thanks

Private Sub ImportXLSheets()
On Error GoTo ERROR

Dim WrksheetName As String
Dim FileName As String

FileName = "C:\temp\test.xls"


Dim xl As Object
Set xl = CreateObject("Excel.Application")

xl.Visible = falsexl.Workbooks.Open FileName

DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, "tbl_In_DB", FileName, True



MsgBox "done"
Set xl = Nothing
End Sub

[anhn]

Do NOT need to open the Excel file before importing it to Access.
Perhaps something wrong has happened with invisible Excel application that may pop up an invisible message box and waiting for you to clik a button but you don't see them.

Just like this to import a spreadsheet:

Private Sub ImportXLSheets()
   On Error GoTo ImportXLSheets_Error
   
   Dim FileName As String

   FileName = "C:\temp\test.xls"
   
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_In_DB", FileName, True
   
   MsgBox "done"
   
   Exit Sub
   
ImportXLSheets_Error:
   MsgBox Err.Description
   
End Sub

[DebbieInFlorida]

Thanks, that worked great, I guess the reason that I had the Excel app there was to retrieve all the sheets, I was going to get the sheet name and add data according to that. But this is great
Thanks

[DebbieInFlorida]

OK, I spoke too soon, I do need to know how to import data from different sheets. Depending on the sheet name is which table the data goes..

???