transferspreadsheet outside of Access

[DebbieInFlorida]

Hi,

I have gotten this to work if inside Access, but I need it to work from an executable outside. The code I have keeps trying to open the database, I don't think it should or need to..
Basically, I need to import Excel (multiple sheets per file, each sheet updates a different table) into the database.
What am I doing wrong,
Thanks


Private Sub cmdGO_Click()

Dim objAccess As Object 'New Access
Dim objExcel As Object 'New Excel

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\temp\Test_Wetlands\Test_Wetlands.mdb"

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

strExcelFileName = "C:\temp\Test_Wetlands\ImportData.xls"

'== must import specifically the Excel Sheet to the table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Evaluated_Wetland", strExcelFileName, True, "Sheet1!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Evaluated_Wetland_Unit", strExcelFileName, True, "Sheet2!"


Set objAccess = Nothing
Set objExcel = Nothing


End Sub

[Hack]

You probably aren't doing anything wrong.

However, it can't import something unless it is open to receive the import. The fact that it keeps trying to open the database makes perfect sense.

Could you import your hand into a glove that was sewn shut?