CODEWITHSUNDEEP
ms-accessvba
sikas
sikas

Reputation: 5523

How to connect to Excel xls file from Access 2010 VBA

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.

Upvotes: 1

Views: 3870

Answers (1)

Fionnuala
Fionnuala

Reputation: 91306

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

Upvotes: 1

Related Questions