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]

Delete all rows (not clear) below your header. Insert has a nasty way of remembering what was once housing data. You may want to incorporate this thinking into your macro.

 

[bdee1]

if i go into the sheet and manually delete the rows (instead of clearning them), then everything works. problem is that i use a macro to clear all the sheets. the user clicks a button on sheet A and all the appropriate fields in the workbook are cleared for the user to process more data.

in my macro that i use to clear the sheets i had Selection.ClearContents before - so i changed it to Selection.delete - and when i run the clear sheets macro excel just hangs - i let it run for about 10 minutes and nothing - it just hangs.

specifically - here is what happens when the user clicks the clear sheets button:

the button calls a function with

 Call ClearSheet("SV_Download")

and the function looks like this:

Sub ClearSheet(sheetName)
'
' selectSheet Macro
' Macro recorded 2/12/2004 by BDEE1
'

'
       
    Sheets(sheetName).Select
    Cells.Select
    Selection.ClearContents

End Sub

what am i doing wrong that would make it hang

 

[NateO]

Hello again, don't delete your headers eh! Try:

Sheets(1).Range([a2], [a65536].End(3)).EntireRow.Delete

 

[bdee1]

ok i just tried

Sheets("SV_Download").Range([a2], [a65536].End(3)).EntireRow.Delete

and i get a "Application-defined or object-defined error" on that line.

 

[NateO]

Yep, that was pretty sloppy and might not work for a few reasons... Try:

Sheets("SV_Download").[a2:a65536].EntireRow.Delete

 

[bdee1]

ok i think that did the trick - but one more question remains unanswered - is there something special i have to do tocompare dates other then using < or > ?

in my WHERE clause, i am comparing the value from the DTPicker1 control to the value inthe DISCH_DATE column of the closed worksheet. but it never returns the expected rows.

my SQL looks liek this now:

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:h65536] Where DISCH_DATE >" & UserForm4.DTPicker1.Value

i also tried removing the reference to the DTPicker and replacign it with an absolute date value and it still gives weird results.

 

[bdee1]

any ideas on this part?

 

[NateO]

Try coercing them both with a double urinary minus. E.g.,

--x > --y

 

[Zzyzzyth]

You may be able to specify the specific address of the table such as:

"SELECT * FROM [a$a1:E89].....

or maybe you can give the range a name and access it through

"SELECT * FROM tblName......

 

[NateO]

Zzyzzyth, you have submitted your last post to this thread three times now, two of which I have deleted. Is there a problem?

Also, I don't think the Select part is the issue at hand (while the issue is opaque)...