Automating Export From Access To Excel Sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Usually I'm responsible for the Systems Integration work on the networks. However I opened my big mouth and now I have the responsibility for the following. Please excuse the lameness of the question -- I've tried searching MSDN and this group but just can't find the precise answer....

I need to get data from an access table into unique spreadsheets. The access table has a field called prod_line. What I'd like to be able to do is automatically create a unique excel spreadsheet for each prod_line code. I could do this manually but it would take forever and was hoping I could write some code that would do this based on the prod_line field entries.

I have created some Outlook custom forms and am familiar with inserting vb code into command buttons.

Would it work something like this? Or would I be better off using an Access macro?

As you can see I'm pretty much in the dark here and any guidance would be appreciated.

Thanks,

Lars
 
=?Utf-8?B?TGFycw==?= said:
Hello,

Usually I'm responsible for the Systems Integration work on the networks.
However I opened my big mouth and now I have the responsibility for the
following. Please excuse the lameness of the question -- I've tried
searching MSDN and this group but just can't find the precise answer....

I need to get data from an access table into unique spreadsheets. The access
table has a field called prod_line. What I'd like to be able to do is
automatically create a unique excel spreadsheet for each prod_line code. I
could do this manually but it would take forever and was hoping I could write
some code that would do this based on the prod_line field entries.

I have created some Outlook custom forms and am familiar with inserting vb
code into command buttons.

Would it work something like this? Or would I be better off using an Access
macro?

As you can see I'm pretty much in the dark here and any guidance would be
appreciated.

Thanks,

Lars
Click to expand...

Lars

One way of doing this is below. The example is using the Northwind sample
database that comes with Access. It basically loops through the different
Categories that are listed in the products table, creates a query that only
selects these records, and then outputs this to an individual Excel workbook.


Public Sub sExportExcel()
On Error GoTo E_Handle
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Dim strSQL As String
Set db = DBEngine(0)(0)
Set qdf = db.CreateQueryDef("Out")
Set rs = db.OpenRecordset("SELECT DISTINCT CategoryID FROM Products;")
If Not (rs.BOF And rs.EOF) Then
Do
strSQL = "SELECT * FROM Products WHERE CategoryID=" & rs!CategoryID
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"Out", "C:\Test" & rs!CategoryID & ".xls"
rs.MoveNext
Loop Until rs.EOF
End If
fExit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set qdf = Nothing
DoCmd.DeleteObject acQuery, "Out"
Set db = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & "sExportExcel", vbOKOnly + vbCritical,
"Error: " & Err.Number
Resume fExit
End Sub
 
Jon,

Thank you very much for the sample code! Much appreciated. Is applecore99 your web?

Lars
 
=?Utf-8?B?TGFycw==?= said:
Jon,

Thank you very much for the sample code! Much appreciated. Is applecore99
your web?

Lars
Click to expand...

Yep. The site is a place for me to post various bits of code that I think are
useful, mostly from newsgroup postings (mainly so that *I* can easily refer
back to them when I need them!!)
 
Back
Top