Solved Lopping through a query to export separate Excel files

[Drand]

Hi,
I have the following code to export a query result to Excel.

Dim db As DAO.Database
Dim rs As Recordset
Set db = CurrentDb
Dim mySql As String
Dim MYPath As String
Dim Country As String



mySql = "SELECT * From QryMissingData"
Country = DLookup("Country", "tblCountries", "CountryCode = 1")
MYPath = "Missing Data For " & Country & " Created " & Date

db.CreateQueryDef "CountryFile", mySql

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & MYPath & "", True
DoCmd.DeleteObject acQuery, "CountryFile"

At present, this creates a file with all records and the path shows the country for country code 1.

I have never created a loop but understand that this would enable me to create a separate file for each country which is what I need.

Is someone able to show me how to create the loop. I have tried various solutions from Google but without success!

Further, once the loop runs and separate files are created, is it possible to update the Country variable each time the loop changes to a new record?

Would appreciate any assistance with this.

Thanks

 

[561414]

Here's a bare minimum code to loop your Country table:

Sub xxx()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblCountries")
    Do While Not rs.EOF
        Debug.Print rs!Country
        rs.MoveNext
    Loop
End Sub

It will print every Country to the immediate window. But what you want to do is create a file for each of these records using your query definition, so this could work:

Sub xxx()
    On Error GoTo ErrorHandler
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblCountries")
    Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
    Dim strSQL As String
    Dim WhereClause As String
    Do While Not rs.EOF

        ' check tblCountries.CountryCode actually exists in your query, you might have it aliased differently
        WhereClause = "tblCountries.CountryCode = " & rs!CountryCode
        ' access the sql string in the querydef and replace the semicolon with the where clause
        strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").SQL, ";", " WHERE " & WhereClause)
        CurrentDb.CreateQueryDef "CountryFile", strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True
        DoCmd.DeleteObject acQuery, "CountryFile"   

        rs.MoveNext
    Loop
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
       ' make sure CountryFile is deleted anyway
        DoCmd.DeleteObject acQuery, "CountryFile"
    End If
End Sub

I suppose you should watch out for some other things, but let me know how it goes.

 

[Drand]

Hi

Thanks for your response. I appreciate it. As I stated I have never done this before!

Your code produced an error message as follows:

I am not too sure what that means.

I have been thinking about this a bit more and I don't think what I asked help for is what I actually need.

To explain, I am trying to output those records from "qryMissingData" which determines which records have missing data from "tblConsolRawdata".

The key is a numeric field "CountryCode"

I need the loop to export a separate file of missing data for each country (based on the country code) which can then be sent back to them for updating.

The data I am using is only dummy/test data but when I implement this, "tblConsolRawData" will hold over 230,000 lines from 134 countries.
For that reason, I think the query should be the source of the export.

I hope this makes sense and again, thanks for your help.

 

[561414]

You could upload it to check it out, since it has dummy data.

Which line does it highlight after the error? It expects qryMissingData to end with ";" so if it does not, then it produces the error, I suppose. I can also see the message is missing a single quote at the end. By the way, does it produce results if you just open the query with double click?

The code works on my end, but it would be good if you post the entire SQL statement from qryMissingData, you can open it in design view and then change it to SQL to see it. Or you can print debug it from the VBA editor with this:

Sub getSQL()
  Debug.Print CurrentDb.QueryDefs("QryMissingData").SQL
End Sub

I think I do understand what you're trying to accomplish. The code I posted should go and check country by country creating a where clause for your qryMissingData and outputting it to Excel.

 

[Gasman]

Create a query qryCountry that either groups by countrycode or DISTINCT on country code.
That will be the input for your loop.

Open that query as a recordset, check it is not EOF.
Then while not EOF
Use your code replacing country code with the recordset record country code with concatenation
After export move next on that recordset, continue until EOF

Close recordset and clean up.

 

[561414]

By the way, does this work?

Sub xxx()
    On Error GoTo ErrorHandler
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblCountries")
    Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
    Dim strSQL As String
    Dim WhereClause As String
    Do While Not rs.EOF

        ' check tblCountries.CountryCode actually exists in your query, you might have it aliased differently
        WhereClause = "CountryCode = " & rs!CountryCode
        ' access the sql string in the querydef and replace the semicolon with the where clause
        strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").SQL, ";", " WHERE " & WhereClause)
        CurrentDb.CreateQueryDef "CountryFile", strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True
        DoCmd.DeleteObject acQuery, "CountryFile"   

        rs.MoveNext
    Loop
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
       ' make sure CountryFile is deleted anyway
        DoCmd.DeleteObject acQuery, "CountryFile"
    End If
End Sub

I removed the alias

 

[Drand]

You could upload it to check it out, since it has dummy data.

Which line does it highlight after the error? It expects qryMissingData to end with ";" so if it does not, then it produces the error, I suppose. I can also see the message is missing a single quote at the end. By the way, does it produce results if you just open the query with double click?

The code works on my end, but it would be good if you post the entire SQL statement from qryMissingData, you can open it in design view and then change it to SQL to see it. Or you can print debug it from the VBA editor with this:

Sub getSQL()
  Debug.Print CurrentDb.QueryDefs("QryMissingData").SQL
End Sub

I think I do understand what you're trying to accomplish. The code I posted should go and check country by country creating a where clause for your qryMissingData and outputting it to Excel.

Hi

Sorry. Have to do some work on the file. It is too large. It may be a few hours as I have to go to work!

 

[Drand]

You could upload it to check it out, since it has dummy data.

Which line does it highlight after the error? It expects qryMissingData to end with ";" so if it does not, then it produces the error, I suppose. I can also see the message is missing a single quote at the end. By the way, does it produce results if you just open the query with double click?

The code works on my end, but it would be good if you post the entire SQL statement from qryMissingData, you can open it in design view and then change it to SQL to see it. Or you can print debug it from the VBA editor with this:

Sub getSQL()
  Debug.Print CurrentDb.QueryDefs("QryMissingData").SQL
End Sub

I think I do understand what you're trying to accomplish. The code I posted should go and check country by country creating a where clause for your qryMissingData and outputting it to Excel.

Hi again

This is the sql for "qryMissingData"

SELECT tblConsolRawData.ID, tblConsolRawData.Country, tblConsolRawData.CountryCode, tblConsolRawData.Data, tblConsolRawData.Fruit, tblConsolRawData.FirstName, tblConsolRawData.LastName, tblConsolRawData.Region, tblConsolRawData.WorkTimes, tblConsolRawData.Breaks, tblConsolRawData.Office, tblConsolRawData.Holidays, tblConsolRawData.Pens, tblConsolRawData.Pencils, tblConsolRawData.ITEquip, tblConsolRawData.ITSupport, tblConsolRawData.Manager, tblConsolRawData.BigBoss, tblConsolRawData.Subordinates, tblConsolRawData.Parking, tblConsolRawData.Wages
FROM tblConsolRawData
WHERE (((tblConsolRawData.Data) Is Null)) OR (((tblConsolRawData.Fruit) Is Null)) OR (((tblConsolRawData.FirstName) Is Null)) OR (((tblConsolRawData.LastName) Is Null)) OR (((tblConsolRawData.Region) Is Null)) OR (((tblConsolRawData.WorkTimes) Is Null)) OR (((tblConsolRawData.Breaks) Is Null)) OR (((tblConsolRawData.Office) Is Null)) OR (((tblConsolRawData.Holidays) Is Null)) OR (((tblConsolRawData.Pens) Is Null)) OR (((tblConsolRawData.Pencils) Is Null)) OR (((tblConsolRawData.ITEquip) Is Null)) OR (((tblConsolRawData.ITSupport) Is Null)) OR (((tblConsolRawData.Manager) Is Null)) OR (((tblConsolRawData.BigBoss) Is Null)) OR (((tblConsolRawData.Subordinates) Is Null)) OR (((tblConsolRawData.Parking) Is Null)) OR (((tblConsolRawData.Wages) Is Null));

 

[561414]

SELECT tblConsolRawData.ID, tblConsolRawData.Country, tblConsolRawData.CountryCode, tblConsolRawData.Data, tblConsolRawData.Fruit, ... WHERE (((tblConsolRawData.Data) Is Null)) OR (((tblConsolRawData.Fruit) Is ...

Hello again.
The alias you are using is tblConsolRawData and it already has a WHERE with a lot of parameters. That means your SQL string looked like this to the VBA routine:
SELECT fields FROM table WHERE conditions WHERE CountryCode = 'Something';
And that syntax is wrong, WHERE can not exist twice in the same statement, that's why the error appeared.

I'm going to present two options here, both require you to modify the SQL statement just a little bit + its VBA code.

OPTION 1: YOUR ORIGINAL QUERY WITH ITS OR OPERATORS ENCLOSED IN ANOTHER PARENTHESES

Sub xxx()
    On Error GoTo ErrorHandler
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblCountries")
    Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
    Dim strSQL As String
    Dim AndWhere As String
    Do While Not rs.EOF

        ' this is the right alias
        AndWhere = "tblConsolRawData.CountryCode = " & rs!CountryCode
        ' access the sql string in the querydef and replace the semicolon with the where clause
        strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").SQL, ";", " AND " & AndWhere)
        CurrentDb.CreateQueryDef "CountryFile", strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True
        DoCmd.DeleteObject acQuery, "CountryFile"

        rs.MoveNext
    Loop
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
       ' make sure CountryFile is deleted anyway
        DoCmd.DeleteObject acQuery, "CountryFile"
    End If
End Sub

But I will enclose your OR operators in yet another parentheses so they can be analyzed in group along with an AND operator. You can use this SQL, which looks just like the one you posted + the extra parentheses:

SELECT tblConsolRawData.ID, tblConsolRawData.Country, tblConsolRawData.CountryCode, tblConsolRawData.Data, tblConsolRawData.Fruit, tblConsolRawData.FirstName, tblConsolRawData.LastName, tblConsolRawData.Region, tblConsolRawData.WorkTimes, tblConsolRawData.Breaks, tblConsolRawData.Office, tblConsolRawData.Holidays, tblConsolRawData.Pens, tblConsolRawData.Pencils, tblConsolRawData.ITEquip, tblConsolRawData.ITSupport, tblConsolRawData.Manager, tblConsolRawData.BigBoss, tblConsolRawData.Subordinates, tblConsolRawData.Parking, tblConsolRawData.Wages
FROM tblConsolRawData
WHERE ((((tblConsolRawData.Data) Is Null)) OR (((tblConsolRawData.Fruit) Is Null)) OR (((tblConsolRawData.FirstName) Is Null)) OR (((tblConsolRawData.LastName) Is Null)) OR (((tblConsolRawData.Region) Is Null)) OR (((tblConsolRawData.WorkTimes) Is Null)) OR (((tblConsolRawData.Breaks) Is Null)) OR (((tblConsolRawData.Office) Is Null)) OR (((tblConsolRawData.Holidays) Is Null)) OR (((tblConsolRawData.Pens) Is Null)) OR (((tblConsolRawData.Pencils) Is Null)) OR (((tblConsolRawData.ITEquip) Is Null)) OR (((tblConsolRawData.ITSupport) Is Null)) OR (((tblConsolRawData.Manager) Is Null)) OR (((tblConsolRawData.BigBoss) Is Null)) OR (((tblConsolRawData.Subordinates) Is Null)) OR (((tblConsolRawData.Parking) Is Null)) OR (((tblConsolRawData.Wages) Is Null)));

OPTION 2: MODIFIED QUERY
Or you can also use this version without all those parentheses and with a shorter alias, which is easier to read:

SELECT t.ID, t.Country, t.CountryCode,
t.Data, t.Fruit, t.FirstName,
t.LastName, t.Region, t.WorkTimes,
t.Breaks, t.Office, t.Holidays,
t.Pens, t.Pencils, t.ITEquip,
t.ITSupport, t.Manager, t.BigBoss,
t.Subordinates, t.Parking, t.Wages
FROM tblConsolRawData as t
WHERE
(
t.Data IS NULL OR
t.Fruit IS NULL OR
t.FirstName IS NULL OR
t.LastName IS NULL OR
t.Region IS NULL OR
t.WorkTimes IS NULL OR
t.Breaks IS NULL OR
t.Office IS NULL OR
t.Holidays IS NULL OR
t.Pens IS NULL OR
t.Pencils IS NULL OR
t.ITEquip IS NULL OR
t.ITSupport IS NULL OR
t.Manager IS NULL OR
t.BigBoss IS NULL OR
t.Subordinates IS NULL OR
t.Parking IS NULL OR
t.Wages IS NULL
);

Since the alias is now t, the VBA code should also change accordingly:

Sub xxx()
    On Error GoTo ErrorHandler
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblCountries")
    Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
    Dim strSQL As String
    Dim AndWhere As String
    Do While Not rs.EOF

        ' this is the right alias
        AndWhere = "t.CountryCode = " & rs!CountryCode
        ' access the sql string in the querydef and replace the semicolon with the where clause
        strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").SQL, ";", " AND " & AndWhere)
        CurrentDb.CreateQueryDef "CountryFile", strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True
        DoCmd.DeleteObject acQuery, "CountryFile"

        rs.MoveNext
    Loop
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
       ' make sure CountryFile is deleted anyway
        DoCmd.DeleteObject acQuery, "CountryFile"
    End If
End Sub

Both do the same, but first double click it from Access to confirm they work.

 

[Drand]

Hello again.
The alias you are using is tblConsolRawData and it already has a WHERE with a lot of parameters. That means your SQL string looked like this to the VBA routine:
SELECT fields FROM table WHERE conditions WHERE CountryCode = 'Something';
And that syntax is wrong, WHERE can not exist twice in the same statement. So this code should be more in tune with your query:

Sub xxx()
    On Error GoTo ErrorHandler
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblCountries")
    Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
    Dim strSQL As String
    Dim AndWhere As String
    Do While Not rs.EOF

        ' check tblCountries.CountryCode actually exists in your query, you might have it aliased differently
        WhereClause = "tblConsolRawData.CountryCode = " & rs!CountryCode
        ' access the sql string in the querydef and replace the semicolon with the where clause
        strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").SQL, ";", " AND " & AndWhere)
        CurrentDb.CreateQueryDef "CountryFile", strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True
        DoCmd.DeleteObject acQuery, "CountryFile" 

        rs.MoveNext
    Loop
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
       ' make sure CountryFile is deleted anyway
        DoCmd.DeleteObject acQuery, "CountryFile"
    End If
End Sub

But I will enclose your OR operators in yet another parentheses so they can be analyzed in group along with an AND operator. You can use this SQL, which looks just like the one you posted + the extra parentheses:

SELECT tblConsolRawData.ID, tblConsolRawData.Country, tblConsolRawData.CountryCode, tblConsolRawData.Data, tblConsolRawData.Fruit, tblConsolRawData.FirstName, tblConsolRawData.LastName, tblConsolRawData.Region, tblConsolRawData.WorkTimes, tblConsolRawData.Breaks, tblConsolRawData.Office, tblConsolRawData.Holidays, tblConsolRawData.Pens, tblConsolRawData.Pencils, tblConsolRawData.ITEquip, tblConsolRawData.ITSupport, tblConsolRawData.Manager, tblConsolRawData.BigBoss, tblConsolRawData.Subordinates, tblConsolRawData.Parking, tblConsolRawData.Wages
FROM tblConsolRawData
WHERE ((((tblConsolRawData.Data) Is Null)) OR (((tblConsolRawData.Fruit) Is Null)) OR (((tblConsolRawData.FirstName) Is Null)) OR (((tblConsolRawData.LastName) Is Null)) OR (((tblConsolRawData.Region) Is Null)) OR (((tblConsolRawData.WorkTimes) Is Null)) OR (((tblConsolRawData.Breaks) Is Null)) OR (((tblConsolRawData.Office) Is Null)) OR (((tblConsolRawData.Holidays) Is Null)) OR (((tblConsolRawData.Pens) Is Null)) OR (((tblConsolRawData.Pencils) Is Null)) OR (((tblConsolRawData.ITEquip) Is Null)) OR (((tblConsolRawData.ITSupport) Is Null)) OR (((tblConsolRawData.Manager) Is Null)) OR (((tblConsolRawData.BigBoss) Is Null)) OR (((tblConsolRawData.Subordinates) Is Null)) OR (((tblConsolRawData.Parking) Is Null)) OR (((tblConsolRawData.Wages) Is Null)));

Or you can also use this version without all those parentheses and with a short alias, which is easier to read:

SELECT t.ID, t.Country, t.CountryCode,
t.Data, t.Fruit, t.FirstName,
t.LastName, t.Region, t.WorkTimes,
t.Breaks, t.Office, t.Holidays,
t.Pens, t.Pencils, t.ITEquip,
t.ITSupport, t.Manager, t.BigBoss,
t.Subordinates, t.Parking, t.Wages
FROM tblConsolRawData as t
WHERE
(
t.Data IS NULL OR
t.Fruit IS NULL OR
t.FirstName IS NULL OR
t.LastName IS NULL OR
t.Region IS NULL OR
t.WorkTimes IS NULL OR
t.Breaks IS NULL OR
t.Office IS NULL OR
t.Holidays IS NULL OR
t.Pens IS NULL OR
t.Pencils IS NULL OR
t.ITEquip IS NULL OR
t.ITSupport IS NULL OR
t.Manager IS NULL OR
t.BigBoss IS NULL OR
t.Subordinates IS NULL OR
t.Parking IS NULL OR
t.Wages IS NULL
);

Both do the same, but first double click it from Access to confirm they work.

Fantastic! I just needed to change the whereclause to match the variable declaration and it worked perfectly. Thank you so much for your help on this, I rteally do appreciate it.

 

[Drand]

Fantastic! I just needed to change the whereclause to match the variable declaration and it worked perfectly. Thank you so much for your help on this, I rteally do appreciate it.

Sorry, I may have jumped the gun with this! I copied your sql to "qryMissingData" and it now only returns data for the first 2 countries.

Also, all the country outputted files contain the same data, i.e. Australia and Argentina where I need the file for say, England to have data for that country!

Sorry to be a pain with this.

 

[561414]

Not a problem. I was editing the code while you were posting your answer, so you probably copied an unfinished version. I'm done with the edits, so you can go ahead and try again. REMEMBER that you also need to modify the SQL query adding the extra parentheses. It should look roughly like this: SELECT fields FROM table WHERE (... your OR operations);
Please notice I enclosed your OR operators in a set of parentheses, if those parentheses are not there, then it will be run like this:WHERE 'All these fields are missing' OR Wages AND CountryCode is XX
That is, it will only show results where Wages are missing and CountryCode is XX.

Can you confirm?

 

[Drand]

Not a problem. I was editing the code while you were posting your answer, so you probably copied an unfinished version. I'm done with the edits, so you can go ahead and try again. REMEMBER that you also need to modify the SQL query adding the extra parentheses. It should look roughly like this: SELECT fields FROM table WHERE (... your OR operations);
Please notice I enclosed your OR operators in a set of parentheses, if those parentheses are not there, then it will be run like this:WHERE 'All these fields are missing' OR Wages AND CountryCode is XX
That is, it will only show results where Wages are missing and CountryCode is XX.

Can you confirm?

Ok. I copied your sql statement to the query so that should be fine. It should show results CountryCode = xx and any field has missing data. This could be more than one field.

 

[561414]

How does the qryMissingData's SQL look right now?

 

[Drand]

It seems to be working really well now. One last thing if I may impose again. It is creating excel files for countries that have no missing data.
If we can fix that I think we are there and I will stop bugging you!

 

[561414]

Just replace this line:
Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblCountries")

With this line:
Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CountryCode FROM qryMissingData")

Let me know how it goes.

 

[Drand]

Now I get "item not found in this collection at line
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For " & rs!Country & CreationMoment, True

 

[561414]

My bad
Maybe this?
Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CountryCode, Country FROM qryMissingData")

 

[Drand]

Absolutely perfect! Thanks again. I am relatively new to VBA so am learning on the way. Help like this really is so useful and I just keep learning. Your time and effort is greatly appreciated.
Cheers

 

[561414]

Glad it was helpful