PDA

View Full Version : Export Excel data to Access table thru TransferSpreadsheet



tomodachi
08-06-2010, 11:53 AM
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!!!

Sub test()

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

End Sub


tomodachi.

Kenneth Hobs
08-06-2010, 04:27 PM
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
08-06-2010, 09:20 PM
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
08-06-2010, 10:13 PM
Hi, this is my new code that i modified. But it produces another error.... Help!!!
file:///C:/Users/user/AppData/Local/Temp/moz-screenshot.pngfile:///C:/Users/user/AppData/Local/Temp/moz-screenshot-1.pngfile:///C:/Users/user/AppData/Local/Temp/moz-screenshot-2.pngfile:///C:/Users/user/AppData/Local/Temp/moz-screenshot-3.pngThere is a runtime error of 3170. Application defined or object defined error. I dont know how to solve it....

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


Thank you!

Kenneth Hobs
08-07-2010, 06:20 AM
Here is the thread that I told you about. http://www.vbaexpress.com/forum/showthread.php?t=33393