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]

ok - at long last - i think we have it - woo hoo! thank you everyone for all your help!!!


for reference here is the code i used:

for the import from a closed workbook:

Private Sub CommandButton1_Click()
  
  UserForm4.Hide
  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:h65536] Where DISCH_DATE >" & UserForm4.DTPicker1.Value

  cn.Close: Set cn = Nothing

END Sub

and here is the code i used to clear the sheet:

'Delete the SV_Download Sheet
    Sheets("SV_Download").Select
    ActiveWindow.SelectedSheets.Delete
    
    
    'Recreate Sheet
    Sheets("Erehab_NoMatch").Select
    Sheets.Add
    ActiveSheet.Name = "SV_Download"
    Sheets("SV_Download").Select
    ActiveWorkbook.Sheets("SV_Download").Tab.ColorIndex = 41
    
    'Set the headers for the sheet
    Sheets("SV_Download").Range("A1").Value = "PATIENT_NUM"
    Sheets("SV_Download").Range("B1").Value = "CLASSN"
    Sheets("SV_Download").Range("C1").Value = "ADMIT_DATE"
    Sheets("SV_Download").Range("D1").Value = "DISCH_DATE"
    Sheets("SV_Download").Range("E1").Value = "LAST_NAME"
    Sheets("SV_Download").Range("F1").Value = "FIRST_NAME"
    Sheets("SV_Download").Range("G1").Value = "CMG"
    Sheets("SV_Download").Range("H1").Value = "PAYTs"

 

[NateO]

You are welcome.

I might tweak the 2nd procedure to look like:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> BlahBlahBlah()
<SPAN style="color:darkblue">Dim</SPAN> y <SPAN style="color:darkblue">As</SPAN> Worksheet
<SPAN style="color:darkblue">With</SPAN> Application
    .ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
    .DisplayAlerts = <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
Sheets("SV_Download").Delete
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">GoTo</SPAN> 0
<SPAN style="color:darkblue">Set</SPAN> y = Sheets.Add(Sheets(1))
<SPAN style="color:darkblue">With</SPAN> y
   .Name = "SV_Download"
   .Tab.ColorIndex = 41
   .[a1:h1] = Array("PATIENT_NUM", "CLASSN", "ADMIT_DATE", "DISCH_DATE", _
       "LAST_NAME", "FIRST_NAME", "CMG", "PAYTs")
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Set</SPAN> y = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">With</SPAN> Application
    .ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>
    .DisplayAlerts = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>