[RESOLVED] Docmd.TranferSpreadSheet problem

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

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:

Code:

---

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

---

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:wave:

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..

???:blush: