query another excel file?

[bdee1]

i am working on an excel project which needs to import data from another excel file.

is there any way that i can use VBA from my original sheet to import the records from another excel file where a specific feild is within a specified date range?

 

[Andrew Poulsom]

If your data is organised as a list with field headings you can use ADO/DAO with a SQL statement. There are some examples here (bottom of list):

http://www.erlandsendata.no/english/index.php?t=envbadac

 

[bdee1]

thanks for the link - looks liek some good info.

i grabbed the sub code from http://www.erlandsendata.no/english/index.php?d=envbadacwbdbado

and i made sure to add the reference to ADO in the VB editor in excel.

however when i run the code with this statement:
GetWorksheetData "C:\Documents and Settings\BDEE1\Desktop\2003cmg.xlw", "SELECT * FROM [A$];", ThisWorkbook.Sheets("SV_Download").Range("A3")

i get the following error:
"Compile Error: User-defined type not defined"

and the debugger highlights the first line of the sub and "cn As ADODB.Connection" in the second line.

did i miss something? is there something else i need to do to make it work?

 

[Andrew Poulsom]

That statement should also be in a sub, eg:

Sub Test()
GetWorksheetData "C:\Documents and Settings\BDEE1\Desktop\2003cmg.xlw", "SELECT * FROM [A$];", ThisWorkbook.Sheets("SV_Download").Range("A3") 
End Sub

Is it?

 

[bdee1]

actually i went back and looked and the reference to ado did not stick so i added the reference again and now it works but the data it gets is not exactly right...

the first couple rows of the closed workbook are header rows so row 1 is blank except column E which has the sheet title

row 2 is blank except column E which has the year.

row 3 is blank

row 4 contains the column headings

row 5 starts the data.

so is there a way to use the sub i am using and have it start retrieving from row 5?

 

[bdee1]

That statement should also be in a sub, eg:

Sub Test()
GetWorksheetData "C:\Documents and Settings\BDEE1\Desktop\2003cmg.xlw", "SELECT * FROM [A$];", ThisWorkbook.Sheets("SV_Download").Range("A3") 
End Sub

Is it?

yeah it is in a sub.

 

[bdee1]

when querying a closed workbook with

GetWorksheetData "C:\Documents and Settings\BDEE1\Desktop\2003cmg.xlw", "SELECT * FROM [A$];", ThisWorkbook.Sheets("SV_Download").Range("A3")

if i want to add a WHERE clause, what do i use for column names?

 

[Andrew Poulsom]

Use the field names from row 1 in your list.

 

[bdee1]

unfortunately the sheet i am pulling the data from has the first 3 rows blank with a centered sheet title so most of the first row is blank. is there any way i can use the 4th row for the row headers

so that in my sql query's where clause, my feild names woudl be the headings in row 4 of the sheet?

 

[bdee1]

anyone? there must be a way during import to tell it to start on row 4 of the closed worksheet.