EXCEL import to Access

Question

I am having some troubles with a VBA code that should import excel data to my access database. When i run the code, i get a RunTime error "Runtime error 438 Object doesn't support this property or method ". From my readings in other forums; I understand that the problem might be a library problem (referencing a library while using another one) I'm somewhat new to VBA. I don't know if it's related, but before i had this error i had an "erreur 429 activeX component can't create object" that i solved.

here is the portion of the code that i think is responsible.

      On Error GoTo erreur

'déclaration des variables
Dim app As Object
Dim wkb As Object
Dim wks As Object

'initialisation des variables
Set app = CreateObject("Excel.Application")
Set wkb = app.Workbooks.Add
Set wks = wkb.Worksheets(1)
wkb = app.Workbooks.Open("C:\Users\souleimane\Desktop\Internship\donnee_excel\table_de_corresp.xls")
wks = wkb.Worksheets("sheet1")

I tried to run the compile option on the debug menu, but nothing poped up. Any help is appreciated, and if you think this is not the portion of the code responsible then i can post the remaining.

barrowc · Accepted Answer · 2013-07-28 22:32:41Z

3

If you are assigning an object type to a variable then you need to use Set so the last two lines should be:

Set wkb = app.Workbooks.Open("C:\Users\souleimane\Desktop\Internship\donnee_excel\table_de_corresp.xls")
Set wks = wkb.Worksheets("sheet1")

Also, you might be able to leave out these lines:

Set wkb = app.Workbooks.Add
Set wks = wkb.Worksheets(1)

depending on whether you use the added workbook later in your code. You can definitely leave out the Set wks = wkb.Worksheets(1) though because that line has no side-effects and you reassign wks two lines later

answered Jul 28, 2013 at 22:32

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2497624 Over a year ago

GEat thanks, this immediatly solved that issue. Although a new one came to rise. This one is named "Erreur 9 Subscript out of range" . Would you happen to know something about that.

barrowc Over a year ago

Probably means you don't have a Worksheet called "sheet1" so it will error on the Set wks = wkb.Worksheets("sheet1") line

user2497624 Over a year ago

Well, in fact the excel file is a "french" excel file. So sheet 1 is referenced as feuil 1 . It solved the problem but another one occured again :s . "Erreur 1009 Appliecation-defined or object-defined error" .

barrowc Over a year ago

Would need to see the lines of code after Set wks = wkb.Worksheets("feuil1") to work that one out. You should probably create a new question for that

Collectives™ on Stack Overflow

EXCEL import to Access

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related