Export to Excel not working correctly

[Drand]

Hi

I have the following code to export separate excel files from a query in my database. The problem is that it is exporting all data from the query to each spreadsheet instead of the data applicable to that countrycode. Can someone please help me with this.

Private Sub Command185_Click()
    Dim db As Database
    Dim rst As Recordset
    Dim qdf As queryDef
    Dim CCode As Integer
    Dim MyCountry As String
    Dim strFilename As String
    Dim prm As Parameter
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryNullToLeftFunction")
    Set rst = qdf.OpenRecordset()
    
    Do While Not rst.EOF
        CCode = rst!CountryCode
        MyCountry = DLookup("Country", "TblCountries", "CountryCode=" & CCode)
        Debug.Print "Processing country: " & MyCountry
        
        ' Update parameter value
        For Each prm In qdf.Parameters
            If prm.Name = "CountryCode" Then
                prm.Value = CCode
                Exit For
            End If
        Next prm
        
        ' Export data to Excel file
        strFilename = "C:\KPMG\Erroneous Function Data Files\" & MyCountry & ".xlsx"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNullToLeftFunction", strFilename, True
        
        rst.MoveNext
    Loop

Thank you

    
    rst.Close
    qdf.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
    MsgBox "Data exported successfully!"
End Sub

 

[June7]

I presume you do not have a Parameter defined in qryNullToLeftFunction because if you did, the OpenRecordset would fail (does for me). Post your query SQL.

Looping through the query that you want to export makes no sense to me. Open a recordset of Countries and loop through that.

SELECT Country, CountryCode FROM TblCountries

No DLookup needed.

I don't think a Parameter in query will work for TransferSpreadsheet anyway unless it is referencing a textbox on open form. Loop recordset and set value of unbound textbox. Otherwise, modify query object SQL statement with literal criteria value.

 

[arnelgp]

you may also create another query that will Filter your data (by country) and use it in your Docmd.TransferSpreadsheet.
or dump your records (country-wise) to a temp table and use the table instead when you Transfer.

 

[Pat Hartman]

You need to use a different method. Change the query so that instead of prompting for a country code, it gets it from a control on a form. Loop through a query that contains the country codes. For each country code, copy the country code to a hidden form control and then run the TransferSpreadsheet.