Solved Exporting Table to Excel by Category

[abrody]

I need help with VBA. I have a table called tblProducts that I need to export to several Excel files. I need to loop through the table and export the records for each ProductCategory into it's own Excel file. Each file should be named with it's ProductCategory.

For example (using the table example below):
The records for ProductIDs 165729 and 620098 need to be exported into a Excel file named Antibiotic.
The records for ProductIDs 835521 and 911543 need to be exported into a Excel file named NSAID.
The records for ProductID 91752 need to be exported into a Excel file named DIURETIC.

DateOfService	ProductID	ProductName	ProductStrength	ProductForm	ProductCategory
Ceftriaxone	1 GM	VIAL	Antibiotic
Vancomycin	500 MG	VIAL	Antibiotic
Ibuprofen	200 MG	TAB	NSAID
Diclofenac	75 MG	TAB	NSAID
Furosemide	20 MG	TAB	DIURETIC

[td]

2/16/2024​

[/td][td]

165729​

[/td]​

[td]

1/31/2024​

[/td][td]

620098​

[/td]​

[td]

4/17/2024​

[/td][td]

835521​

[/td]​

[td]

2/2/2024​

[/td][td]

911543​

[/td]​

[td]

7/3/2024​

[/td][td]

91752​

[/td]​

I'm not very good with vba. Does anyone have an example of some vba that can accomplish these goals?

Thank you.

 

[June7]

Look at examples from https://accessmvp.com/KDSnell/EXCEL_MainPage.htm, in particular the second Export.

 

[Uncle Gizmo]

I need to export to several Excel files.

I did something similar but instead of exporting to separate Excel files my code exports to separate Excel sheets.

So you might find my solution adaptable to your particular situation...

Here's a YouTube video demonstrating how it works:-

Export Query into Separate Excel Sheets - Nifty Access​

 

[arnelgp]

also you can use this demo as your template for the code.
run Form1.

 

[abrody]

also you can use this demo as your template for the code.
run Form1.

Thank you all for the help.

Arnelgp-Could you please tell me how to modify your example to export each Product Category into it's own separate Excel file instead of separate tabs in one file? Thank you.

 

[arnelgp]

here is a modified version.

 

[Uncle Gizmo]

Export Query into Separate Excel Sheets - Nifty Access

Hi abrody,

I’ve noticed you're looking to export data into separate Excel files. While my solution demonstrates how to put the data into separate Excel pages within one Excel file, the code can be easily modified to meet your specific needs of creating separate Excel files instead.

I’ve already posted a video showing how the method works, and all of the VBA code is available for you to build on. It shouldn’t take much tweaking to adapt it for your case. You can check it out here:- https://www.niftyaccess.com/make-excel-sheets-from-access-table/

Please note, the formatting on the website is not displaying correctly at the moment due to a PHP error, but it's being fixed. The content itself is still accessible and should guide you through the process.

FIXED!!!

Let me know if you have any questions while modifying the solution, and I’d be happy to help!

 

[abrody]

here is a modified version.

Thanks arnelgp! Works great! And thank you all for your suggestions.

While waiting for your reply, I did one more search. Somehow I made the search gods happy and found the code below.
It works, but when I try to open the Excel file I get a "Excel cannot open the file because the file format or file extension is not valid. Verify the file has not been corrupted and that the file extension matches the format of the file." error. What would cause this? Thanks.

Function TEST()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim v As String

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Select ProductCategory From tblProducts ")

Dim strQry As String
Dim qdfTemp As DAO.QueryDef
Dim strQDF As String
strQDF = "_TempQuery_"

Do While Not rs1.EOF
v = rs1.Fields(0).Value

strQry = "SELECT * FROM tblProducts WHERE ProductCategory = '" & v & "'"

Set qdfTemp = CurrentDb.CreateQueryDef(strQDF, strQry)
qdfTemp.Close
Set qdfTemp = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
strQDF, "C:\Testing\" & v & ".xlsx", True

CurrentDb.QueryDefs.Delete strQDF
rs1.MoveNext
Loop

rs1.Close

End Function

 

[Gasman]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _

 

[abrody]

Thanks Gasman!