Export Access table fields to have name headings in Excel

[djmousie]

I currently have an Access table that I am trying to use the field data as a means of exporting. For example, I have a table with a field labeled [VENDORS]. There are different vendors within the [VENDORS] table. I would like to export so that it groups the same vendor names together and applies the vendor name to a new excel tab in Excel. So vendor "ABC" and all its corresponding information will be exported to a new tab in Excel called "ABC"...and so on with the next vendor.

Is this possible?

 

[BoxHead]

Yes it is possible.

On a form, put a command button with the following click event.

Private Sub Command0_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT SR FROM tblPipeline ORDER By SR;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("rpt_" & strCrt, "SELECT tblPipeline.*  FROM tblPipeline WHERE tblPipeline.SR = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rpt_" & strCrt, "C:\Documents and Settings\John\My Documents\SR_Report_" & strDt & ".xls", True
DoCmd.DeleteObject acQuery, "rpt_" & strCrt
rs.MoveNext
Loop
End Sub

In my example, the table name is tblPipeline and the field I am grouping on is the SR field. Replace those with your own table and field names.

Also, replace the path for the file to be saved.

This is set up to select the distinct SRs from the table, create a query for each, names the query "rpt_" + SR's name, exports the query and then deletes the query before continuing to the next SR.

The file created is named 'SR_Report_030206' for today and since you are exporting multiple data sets to the same file, it forces a new tab and names the tab after the query.

Be sure to NOT follow your normal query naming conventions so that you are not trying to create or delete another query.

HTH

John




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[djmousie]

After replacing field names and table names and paths with that of my own, I get an error on the second string of code....it highlights "db As DAO.Database" with a prompt error of "User-defined type not defined"....

 

[BoxHead]

Open any module so you can view the code, click on Tools > References and make sure you have the Microsoft DAO 3.6 Object Library Selected.


HTH



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[djmousie]

Thanks works great!!

 

[djmousie]

Boxhead, is there a way for me to take this code and now, rather than just seperate out [VENDORS] into new worksheets, it creates an entire new workbook for each vendor?

 

[BoxHead]

Sure. I don't have time to test it at the moment and I'm not sure what naming strategy you chose for the file and tabs/queries, but the following should create a separate excel file for each.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rpt_" & strCrt, "C:\Documents and Settings\John\My Documents\SR_Report_" & strDt [b]& "_" & strCrt [/b]& ".xls", True

HTH


John

 

[djmousie]

Yes this does work! thanks much.

 

[BoxHead]

Glad to help.

 

[djmousie]

Boxhead,

How easy would it be now, to add script that tells how to format the excel spreadsheet, so when it spits out all my data, its all the same font, the column sizes are the same and what not?

 

[BoxHead]

That would be a new thread, my friend. I'm not sure if that can be done with the transfer spreadsheet method.

I have seen posts about this but haven't ventured into it.

Start with thread181-1014324 and check for FAQs here.


John

 

[djmousie]

thanks