pull data, text box with file name

**James580** · 10-24-2008, 04:50 PM

I need to input the file name into the text box "DRAW082808" this will look in a folder for that file name

then the macro below looks for

Home Delivery and (123060) in the next colum
and College routes and (627), Carrier Copy ((845) and adds these together and inserts in a cell on the masterfile under Home Delivery

Private Sub Thisyeardraw_Click()
Set sn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
  sn.Open "provider=microsoft.jet.oledb.4.0;data source = m:\cir at a glance\dsi reports\" & TextBox3.Value & ".xls;extended properties = ""Excel 8.0; HDR=yes"""
  
  sqlstr = "select sum(col1_copies) from [" & TextBox3.Value & "$] where col1_head = Home Delivery 
  rs.Open sqlstr, sn
  Range("c42").CopyFromRecordset rs
  rs.Close
   
   
  Set rs = Nothing
  Set cn = Nothing
End Sub

Home Delivery "123060" + "627" + "845"

School "240" + "1133"

Mail "679"

3rd Party "10555"

I have attached the master file and the two files to pull from