Run TransferSpreadsheet cmd from Excel

[mac7attack]

Hello,

I am trying to export a Excel spreadsheet into a Access database using the TransferSpreadsheet command. I am running a macro in Excel 2003 to format the open spreadsheet then export it to Access. I have gotten the Transfer command to work in Access VBA but not in Excel.

The script line I am using in Excel is:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TestNameTest", "db1.mdb", True

Do I have to create a Database object before running the command or is there another way of doing it?

Thanks
Matt

 

[PHV]

DoCmd is a method of the Access.Application object, so you have to instantiate such object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mac7attack]

Thanks for the quick response PH

So the following should work?

Dim appAccess as New Access.Application
Set appAccess = CreateObject("Access.Application")

appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TestNameTest", "db1.mdb", True

Thanks
Matt

 

[mac7attack]

I have tested the script in the last post and now getting an error.

"External table is not in the expected format."

I know in Excel the file name (4th parameter) is a spreadsheet but I am running the script from the spreadsheet that needs to be imported. Where should I specify the Spreadsheet to be imported and the Acces to be imported into?

Matt

 

[mac7attack]

Just so that anyone reading this post can know the answer here it is.

Sub ExportTest()

Dim appAccess As New Access.Application
Set appAccess = CreateObject("Access.Application")
Dim strDB As String
Dim TN As String

TN = Date$ & " " & Time$
MsgBox TN
ActiveWorkbook.Save
strDB = "C:\db1.mdb"
appAccess.OpenCurrentDatabase strDB, True
appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TN, ActiveWorkbook.FullName, True
appAccess.CloseCurrentDatabase

End Sub