Export Excel spreadsheet to access table

[cassidybklyn]

Please can anyone assist me with the VB code to export a spreadsheett to access table(database). I am doing this and it's telling me that object is required:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ExcelImport", "C:\UnclaimedChecksover60days_20070515_12.51.38.xls", True

Note: "ExcelImport" is my access table name.
Name of spreadsheet and its location:
"C:\UnclaimedChecksover60days_20070515_12.51.38.xls"
Both my spreasheet and Access database on located on the C:\ drive.
Thanks.
Cassidy.

 

[PHV]

This code should run in access VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Okay. but I'm running VB 6.0. How can I tweak it?
Thanks.
C.

 

[PHV]

DoCmd is a method of the Access.Application object, so to call it you must have instantiated such object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Thanks PHV,
I instantiated the access object as such:
Dim AccObj As object
Set AccObj = CreateObject("Access.Application")

Then I called the DoCmd, but I still got the same message: Object required.

What am I doing wrong?

 

[SkipVought]

How did you call the DoCmd???

AccObj.DoCmd......

Skip,

_[red][/red]

 

[cassidybklyn]

No I just did a straight "DoCmd" as I'm in access environment. I guess that was wrong huh?

 

[bubba100]

I can't see where you told the program to look for the db.

 

[SkipVought]

EXPLICIT references! Remember?

Skip,

_[red][/red]

 

[PHV]

You must have an access application with an opened CurrentDatabase and use full qualified objects.

BTW, what is the meaning of I'm in access environment vs I'm running VB 6.0 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Thanks guys, after much tweaking, it worked like a charm, just as you said.
Thanks and STARS for y'all.
Cassidy.