How to Export Individual Store Reports coming from one table to Multiple Excel Files

[maria1985]

Hi,

I have a table in Access that contains data that I need to export to Excel:

1. A query that selects only the data pertaining to Store 1 and exports it to excel as Store_1.xls.
2. The loop then uses the same query as in 1 to then export the data pertaining to Store 2 into excel as Store_2.xls
3. the loop continues until all of the stores in the table in access have had an excel report exported.

Thank you! Sorry I do not have that much experience with VBA and the project is due on Friday :(

Maria

[jimatqsi]

Look at the TransferSpreads heet method of DoCmd. That will be the key to this project.

Note that we're not a homework service here. We'll give you tips and point helpfully in the right direction.

Jim

[maria1985]

Thank you Jim!
I have looked at the TransferSpreads heet method and have that set. The problem that I am having is the looping.
Would you advice to create a query that lists the Stores with an ID that is numerical and then have the loop add 1 to the previous ID so that it keeps exporting that way?

[maria1985]

I have found the loop but now I am stuck on the query to link the string v to:

Sub TEST()

Dim db As DAO.Database Dim rs1 As DAO.Recordset Dim v As String

Set db = CurrentDb() Set rs1 = db.OpenRecordset("Select Distinct Territory From TEST")

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

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _ "WHAT SHOULD MY QUERY BE TO USE STRING v?", "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True

rs1.MoveNext Loop

rs1.Close

End Sub

thank you!

[jimatqsi]

Maria,
It could be something like this

dim strSQL as string
strSQL = "Select Distinct Territory From TEST"

or

strSQL = "Select Distinct Territory From TEST where [some fieldname]=" & numericvalue

or

strSQL = "Select Distinct Territory From TEST where [some alpha fieldname]='" & alphavalue & "'"

In your case that would be

strSQL = "Select Distinct Territory from TEST where [some alpha fieldname]='" & v & "'"

and then of course use strSQL as the parameter where the query string goes.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _ strSQL, C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True

"Select Distinct Territory from Test" is only a filler for representing your real query. You have mentioned stores 1 and 2 in your first post, as if the stores IDs are numeric in nature. But your v variable is a string; was that a conscious decision based on the actual store ID being defined as strings and not numerics (even if the strings are 1, 2, ...)? In other, words, is that first field in the table TEST an alpha or a numeric and is it the store ID? Assuming the answers are 'numeric' and 'yes' you can then figure out how to make your real query to access the store data; something like

strSQL = "Select * from STORETABLE where StoreID=" & v

or if it's an alpha store ID

strSQL = "Select * from STORETABLE where StoreID='" & v & "'"

Jim

[maria1985]

I have opted to create a numeric ID called storekey and have updated the code to:

Sub TEST()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim v As Integer
Dim strSQL As String

strSQL = "Select * from TEST where storekey=" & v


  Set db = CurrentDb()
  Set rs1 = db.OpenRecordset("Select Distinct storekey From TEST")

  Do While Not rs1.EOF
     v = rs1.Fields(0).Value
          
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strSQL, "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True
     
     rs1.MoveNext
  Loop


  rs1.Close

End Sub

However, now I get runtime error 3011:
Run-Time error '3011':
The Microsoft Office Access Database engine could not find the object 'Select * from TEST where storekey=0'. Make sure the object exists and that you spell its name and path name correctly.

[maria1985]

The code worked as the following:

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

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Select Distinct Territory From TEST")

    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 TEST WHERE Territory = '" & v & "'"

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

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
        strQDF, "C:\Users\me\Desktop\VBA_TEST\" & v & ".xls", True

        CurrentDb.QueryDefs.Delete strQDF
        rs1.MoveNext
    Loop

    rs1.Close

End Sub

Thank you for your help Jim!

[jimatqsi]

Oh, oh, right, you can only use saved queries when you're working with TransferSpreads heet. My bad! Glad you got it worked out, Maria. Good job!!

Jim