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.
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.
Code:
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