How to connect to Excel xls file from Access 2010 VBA

Question

I have googled alot for this but I couldn't get to it ..

I have an excel file (.xls) that is around 13K rows .. I want to be able to connect to it from the Access 2010 VBA and then be able to copy data from it that I require for my work.

Anyone suggest how I can accomplish this.

Where are you stuck? Have you linked the Excel file as a table? — Fionnuala
– Fionnuala, Commented Feb 7, 2013 at 21:50
I don't want to do this .. I have the VBA to give the user the option to choose the file through the Application.FileDialog .. When the user chooses the file, the VBA should select some columns to copy to a table in the database .. Later, another form will do some searches based on user input. — sikas
– sikas, Commented Feb 7, 2013 at 22:03

Fionnuala · Accepted Answer · 2013-02-08 00:50:25Z

1

You can create a query in MS Access:

INSERT INTO Table1 
SELECT FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xlsm].[Sheet1$] s
WHERE s.SomeField=2

Or

SELECT * INTO Table1 
FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xlsm].[Sheet1$] s
WHERE s.SomeField=2

You can also refer to named ranges and ranges. You can also simply set up a query and allow users to create their own make table queries.

In VBA

 Dim db As Database
 Set db = CurrentDB

 ssql="SELECT * INTO Table1 " _
  & "FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xlsm].[Sheet1$] s " _
  & "WHERE s.SomeField=2"
 db.Execute ssql, dbFailOnError

edited Feb 8, 2013 at 0:50

answered Feb 7, 2013 at 22:04

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

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

4 Comments

sikas Over a year ago

how can I do this using the VBA?

sikas Over a year ago

Your update gives me an error! "run-time error '3067': Query must contain at least one table or query" .. Also, how can I select specific columns? say columns A, B, C, E and H

sikas Over a year ago

I managed to solve this one, but I get another issue .. run-time error '3061': Too few parameters. Expected 1.

sikas Over a year ago

Well, I managed that too :) .. And was also able to select the specific columns .. Thanks very much for guiding :)

Collectives™ on Stack Overflow

How to connect to Excel xls file from Access 2010 VBA

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