Export Excel data to Access table thru TransferSpreadsheet

[tomodachi]

Help guys!!!

I want to transfer my Excel data in a spreadsheet to an Access table.. but after I run my codes, it always pops up a run time error of 438 saying that the object does notsupport this property/method. Please help me.. thanks!!!

[VBA]Sub test()

Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "excelfilesfordata.xlsx", "F:\Database51.mdb", True, ""

End Sub[/VBA]


tomodachi.

[Kenneth Hobs]

If you are doing this from Excel, then Application is for Excel, not Access. You need to first reference the Access object to access the method of properties of the Access object.

There are other ways to do that in excel. See my recent posts for an alternative.

[tomodachi]

Hi Kenneth,

Really thankful for ur reply! I have seen ur recent posts but I am still not sure how to do it.. Sorry, I am a real newbie...

And how do you reference the Access object? And Can Application still be used in this case?

Thank you!
'tomodachi.

[tomodachi]

Hi, this is my new code that i modified. But it produces another error.... Help!!!
[IMG]file:///C:/Users/user/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/user/AppData/Local/Temp/moz-screenshot-1.png[/IMG][IMG]file:///C:/Users/user/AppData/Local/Temp/moz-screenshot-2.png[/IMG][IMG]file:///C:/Users/user/AppData/Local/Temp/moz-screenshot-3.png[/IMG]There is a runtime error of 3170. Application defined or object defined error. I dont know how to solve it....

[vba]Sub happy()

'Identify MS Access as an Object
Dim a As Object
Set a = CreateObject("access.application")


'
'Opens the MS Access Database
a.OpenCurrentDatabase ("F:\Database51.mdb")
a.Visible = True



'Transfer identified range from Excel worksheet to MS Access
a.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\Users\user\Desktop\excelfilesfordata.xlsx", True, ""
a.Visible = True

End Sub[/vba]


Thank you!

[Kenneth Hobs]

Here is the thread that I told you about. http://www.vbaexpress.com/forum/showthread.php?t=33393