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?

 

[NateO]

The following worked for me:

http://www.mrexcel.com/board2/viewtopic.php?t=68224&start=3

It went the opposite direction, pushing versus pulling, so you'll want to flip that aspect of it.

 

[bdee1]

well i am not sure i want the user to open the target file (which they would have to do with the "push" method, right?)...the method i am using is getting the data imported,but it just wont let me use the WHERE clause in my SQL.

with no where clause, the data imports but since the first few rows of the recordset are blank (header rows), it imports the header names ad F!, F2, F3, F4 and so on.

so if it automatically names these Fields wouldn't i be correct in assuming that i could refer to the Fields in my where clause as F!, F2, F3 and so on?

is there any special notation i need to use in my where clause? every time i try the following where clause, not data imports

WHERE f1 > DatePicker1.value

and i know that it is not that there are no rows that meet the criteria because the criteria should return ALL rows.

any help would be appreciated.

 

[NateO]

well i am not sure i want the user to open the target file (which they would have to do with the "push" method, right?)...the method i am using is getting the data imported,but it just wont let me use the WHERE clause in my SQL.

Nope this pushes to a closed file, opening need not apply.

Show your revised code. Simply flip the target and source.

 

[bdee1]

i am not sure we are on the same page here - let me explain.

i have to excel files - File A and File B.

File A is the primary file. i would like to import data from file B into File A.

I would like the user to initiate this data transfer from File A, and never have to open File B.

but using a Push method, wouldn't that require the user to open file B and click something to initiate the data Push to file A?

so are you saying that yoru code will do this or did you misunderstand what iwas doing.

 

[Ekim]

Se the following thread regarding getting data form a closed workbook. Various routines are discussed.
http://www.mrexcel.com/board2/viewtopic.php?t=77003&highlight=

If you want precise help, you need to provide specific information:

Name of the closed file
File path
Name of the sheet on which the source data is located
The source data’s range.

Another technique to get data from a closed workbook (not referred to in the above thread) is the SQL.Request worksheet function. A file with a number of working examples can be downloaded from http://www.bygsoftware.com/examples/examples.htm
Scroll down to “Using SQL.Request”.

HTH

Mike

 

[NateO]

I'm saying a slight revision of the code will do what you're enquiring about, e.g.,

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> ExToEx()
<SPAN style="color:darkblue">Dim</SPAN> cn <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>

<SPAN style="color:darkblue">Set</SPAN> cn = CreateObject("ADODB.Connection")

cn.<SPAN style="color:darkblue">Open</SPAN> "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
     "c:\temp\test.xls;Extended Properties=Excel 8.0;"

cn.Execute "Insert Into [Sheet3$] <SPAN style="color:darkblue">In</SPAN> <SPAN style="color:green">'' " & _
    "[Excel 8.0;Database=" & ThisWorkbook.FullName & ";HDR=Yes] Select " & _
    "col1, col2, col5, col7 From [Sheet3$a4:g65536] Where --col5 > 8000"</SPAN>

cn.Close: <SPAN style="color:darkblue">Set</SPAN> cn = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Where c:\temp\test.xls is the closed Excel file, col1, col2, col5 & col7 are valid headers in row 4 and col7 is to be tested. I had strings in col5, so I coerced them to numeric data types.

Hope this helps.

 

[bdee1]

hey NateO - i get it now - sorry, i misunderstood what you were saying before.
so looking at the code i have a couple questions:

1) when it says

Insert Into [Sheet3]

that is where the data is to be imported to, right? so if the name of the sheet i want to import into is named SV_Download i would say

INSERT INTO [SV_Download$]

right?


2) what are you referring to when you say:

ThisWorkbook.FullName

is that the file name of the the workbook i want to import from? or is that the sheet name in the file i want to import from?

3) in the sheet I want to import from, some of the the column headers include spaces, is that allowed? do i have to tell the owner of the worksheet to change her column headers?

now that i understand what you were saying, i think this will work, i just have to fill in some of these gaps.

thanks for your help!!

 

[NateO]

Hello again,

hey NateO - i get it now - sorry, i misunderstood what you were saying before.

No worries, it probably is not the typical approach to such a quandary, I can see the process being opaque.

so looking at the code i have a couple questions:

1) when it says

Insert Into [Sheet3]

that is where the data is to be imported to, right? so if the name of the sheet i want to import into is named SV_Download i would say

INSERT INTO [SV_Download$]

right?

Correct.

2) what are you referring to when you say:

ThisWorkbook.FullName

is that the file name of the the workbook i want to import from? or is that the sheet name in the file i want to import from?

This is the importing workbook's full name (path and name), which you want to pass. The sheet was addressed with question #1.

3) in the sheet I want to import from, some of the the column headers include spaces, is that allowed? do i have to tell the owner of the worksheet to change her column headers?

They should match the headers of the source book. Spaces might be dicey, I haven't tried this, but you've wandered from Spreadsheet technology to DB technology, it's best to follow the rules of a DB here.

now that i understand what you were saying, i think this will work, i just have to fill in some of these gaps.

thanks for your help!!

Sorry about the confusion, I definitly was assuming a bit of a leap there... You are welcome.

 

[bdee1]

well i am working on it but i have not gotten it to work just yet
i did take out the spaces in the column headers but i am still getting an error.

my code looks like this:

Dim cn As Object
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & UserForm4.TextBox1.Value & ";Extended Properties=Excel 8.0;"
  
  cn.Execute "Insert Into [SV_Download$] In '' " & _
    "[Excel 8.0;Database=" & ThisWorkbook.FullName & ";HDR=Yes] Select " & _
    "PATIENT_NUM, CLASSN, ADMIT_DATE, DISCH_DATE, Last_Name, First_Name, CMG, PAYTS From [A$a4:g65536] Where --DISCH_DATE > " & UserForm4.DTPicker1.Value

  cn.Close: Set cn = Nothing

problem is that when i run it i get the followign error:

Run-time error '-2147217904 (80040e10)':
No value given forone or more required parameters.

i'm not sure what it is talking about - as far as i knwo there are values for all the parameters.

 

[NateO]

What does 'UserForm4.TextBox1.Value' equal?