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?

 

[bdee1]

C:\Documents and Settings\MyUsername\Desktop\myfilename.xlw


basically i have a button on the worksheet - when clicked it opens userform 4 where the user can browse for the file to inport from and specify a date range with datepicker controls...then whenthey click ok, this sub is called.

 

[bdee1]

something i just noticed - in the WHERE clause it says WHERE --DISCH_DATE ...

what is with the -- before the column name? is it supposed to be there?

 

[NateO]

The double urinary minus was implemented in my case to coerce a string data type to a numeric data type. You probably do not need this if you're going number to number.

Time to step back. Try running this without the userform, and some fixed values, see if it works as expected then. If so, you may have an object oriented focus issue, or something... It's such a dubious error message, that you'll want to develop this in pieces. I don't see anything glaringly wrong with your adjusted code.

 

[bdee1]

ok i will give that a try - thanks.

 

[bdee1]

ok well i have narrowed down the problem to the following peice of code:

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 > 2/17/2003"

but i still cant get it to work - i have changed all the userform values to absolute values and the error is the same as i was gettign before - "no value given for one or more of therequired parameters". so i dont knwo what parameter it is looking for that is missing.

 

[Andrew Poulsom]

Is it because you have 8 fields and the range A4:G65536 is only 7 columns?
And is your sheet named A as the first 2 characters in A$a4:g65536 suggests?

 

[bdee1]

yes - good call - i changed the range to the correct number of fields and now it is better.

and yes, A is the name of the sheet (i know its weird but i didn't name it - its someone Else's sheet i am working on)

so that got me past the error i was getting before but now it is saying:

"The INSERT INTO statement contains the following unknown field name: 'PATIENT_NUM'. Make sure you have typed the name correctly, and try the operation again"

and i know that all the field names are spelled correctly - its like it is not looking at the right cells for the headers....the headers are in cells A4-H4 - so i don't know why it cant find the cells.

we're getting close - i can feel it.

 

[Andrew Poulsom]

I'm not sure, but have you got the same headers in your target worksheet? I think HDR=YES means you should have.

 

[NateO]

Nice catch Andrew.

I'm not sure, but have you got the same headers in your target worksheet? I think HDR=YES means you should have.

Correct. Make sure they're both spelled the exact same.

 

[bdee1]

yes that was the problem - i didn't have the headers in the target worksheet. so now the data is importing - BUT - for some reason it is importing into the sheet starting at row 2818.

so after the import, my sheet has the headers in row 1 and then there are like 2000 blank rows and then the 100 rows of imported data.

the code i am using is :

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:h100] "

  cn.Close: Set cn = Nothing

notice that i changed the range from [A$a4:h65536] to [A$a4:h100] - when i set the range to be 65536 rows, it take a loooong time to import the data because it has to look at every row of the sheet. is there a way to determine how many rows of data there is in the closed sheet so i don't have to have it scan through the whole sheet for a hundred rows of data?

we have made it this far - we are almost there - just a little tweaking and we will have it - you guys are the greatest!