1

I am trying to read an excel file inside a access application using VBA. I have this code:

Dim xlApp As Excel.Application
Dim xlWrksht As Excel.worksheet

which I got from here:

Best way to read an Excel file into an Access database

but when I try to run it, I am getting error that user defines type is not define.

Should I add a reference to the application?

Is there any way that can do it without adding a reference to my application?

Edit 1

After adding reference to the code, Now I have this code:

Dim excelApp As Excel.Application
Dim workbook As Excel.workbook
Dim Worksheet As Excel.Worksheet
Set excelApp = CreateObject("Excel.application")
Set workbook = excelApp.Open(InputFileName)
Set Worksheet = workbook.Worksheets(1)

But now I am getting error on line:

Set workbook = excelApp.Open(InputFileName)

as the excelApp doesn't have any method called Open. Should I add any other reference?

Improve this question

3 Answers 3

Reset to default
1

If you know the structure of the Excel worksheet, you can link to various ranges in one worksheet, an treat each of them as a table, using queries and/or Recordsets. Last parameter in this line represents a range in the first worksheet: 

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, myTableName, ExcelFileName, False, "f3:L10"

You remove this link as any table:

CurrentDb.TableDefs.Delete (myTableName)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If you do not add a reference to the objects, then Access will not have knowledge of the objects to use with variables. An object/type is either built-in or referenced so it is usable.

You should also include Excel.Workbook, as the hierarchy is App -> Workbook -> Worksheet -> Cells.

Improve this answer

5 Comments

Thanks, I have a new problem, Can you please help me? I updated the question with new prblem.
First, you should usually accept the answer to the question asked and open a new question for a different problem. Second, use workbook.open(InputFileName).
Specifically: excelApp.Workbooks.Open(InputFileName). Whereas the Application object is not referenced if your ran this line inside Excel, outside you need to reference it (being a foreign object).
Correct. I should have been more explicit.
Set workbook = excelApp.Application.Workbooks.Open "InputFileName" Does that do the trick? msdn.microsoft.com/en-us/library/office/…
0

You need to add a reference to your Access project, else it would not recognize it.

G.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.