query another excel file?

bdee1

Board Regular
Joined
Feb 17, 2003
Messages
105
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?

:rolleyes:
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

bdee1

Board Regular
Joined
Feb 17, 2003
Messages
105
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That statement should also be in a sub, eg:

Code:
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

Board Regular
Joined
Feb 17, 2003
Messages
105

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2003
Messages
105
Andrew Poulsom said:
That statement should also be in a sub, eg:

Code:
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

Board Regular
Joined
Feb 17, 2003
Messages
105

ADVERTISEMENT

when querying a closed workbook with
Code:
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?
 

bdee1

Board Regular
Joined
Feb 17, 2003
Messages
105
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

Board Regular
Joined
Feb 17, 2003
Messages
105
anyone? there must be a way during import to tell it to start on row 4 of the closed worksheet.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,109
Members
425,260
Latest member
worldbfreebase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top