automatically query and convert access db table to excel using vbs

Question

I have an Access database. I would like to automatically query the table Data_01 and export the results to an Excel spreadsheet using ADO in VBScript on a daily basis. At present my skills in ADO are lacking.

1. I have a date time column that I would select items from yesterday and today. In a GUI query the criteria would be Between Date() And Date()-1
2. I have a PartNumber column that I would like select a specific part number. In a GUI query the criteria would be Series 400
3. I would then like to select other columns based on the criteria in items 1. and 2.
4. I would like to get the header row for the columns also.

I am presently exporting the entire table to Excel and then using a VBScript to select the columns that I want and then deleting all unwanted data, then auto-fitting the columns for my final output file. This appears to be somewhat processor- and time intensive.

Solution 1

Have you tried the built in functions in Excel for importing data? I don't have a English language version of Excel, so I won't guide you to them, but I think the menu is called "Data".

Solution 2

Here is some sample VBScript

Dim cn 
Dim rs

strFile = "C:\Docs\LTD.mdb"

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM tblTable " _
& "WHERE CrDate Between Now() And Date()-1 " _
& "AND OtherField='abc' " _
& "AND PartNumber=1 " _
& "ORDER BY CrDate, PartNumber"

rs.Open strSQL, cn

Set xl = CreateObject("Excel.Application")
Set xlBk = xl.Workbooks.Add

With xlbk.Worksheets(1)
    For i = 0 To rs.Fields.Count - 1
        .Cells(1, i + 1) = rs.Fields(i).Name
    Next

    .Cells(2, 1).CopyFromRecordset rs
    .Columns("B:B").NumberFormat = "m/d/yy h:mm"
End With

xl.Visible=True