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.

Answer 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".

Answer 2

My first reaction is to do the following:

1. Create a query object in MS Access that finds the data you want to export [Database Window -> Queries -> New (use the GUI builder for now)]
2. Create a macro that exports the query to an Excel file. I talk more about that here. You could do this in VBA as well... many would say that was more "pure" (I have macros as well); but whatever floats your boat.
3. Set up an autoexec macro (this will run automatically when the MS Access opens) that runs the export macro you just created and then exits MS Access (you can override this my holding down the shift key while Access is loading). It would be slightly better to also create a separate MS Access file to preform these operations without affecting the original MS Access file, just by creating table links to the original.
4. Set up a Scheduled Task to open the MS Access file once a day.

Answer 3

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

Answer 4

Remou,

Your answer looks very promising. I will edit the code to match my situation, and give it a try. I hope this is the correct way to respond. My first time posting here so klutzing around a little bit. Thanks for your answer.

Nat

Tester101,Remou

I tried to reply by adding a comment to your answer, but not enough reputation points to use that method. Tough being the newbie. Thank you also for your answer, also a promising solution. I have done quite a bit of wmi and wsh scripting for admin purposes, but this is my first time being tasked to deal with databases. I knew ADO was the right solution but shortly after looking at it my eyes glazed over :-) Lots for me to learn. Thanks for opening the door with some real world solutions.

Nat

Svinto,

I am looking at the data function for automated charting of the data, which is another portion of the project. Thanks for your response.

Codeslave,

When I was first starting this project I actually did exactly as you suggested building a query and a macro and was going to script the execution of the macro. Very fast and works very nicely. Unfortunatly due to the number of machines that I am dealing with and the variations in the databases, available time on the machines, and some other contraints it became problematic. Thanks for your response, and comments. Again my apologies for not using the commenting function still not enough points.

Tester101,

Thanks for the csv output code. I do like csv files in many instances. Just about anything will read them nicely (ASCII) :-). Still can't use comment function :-(

Nat