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.

 

[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.

 

[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!