Solved Nested Loop

[Drand]

Morning All

I have the following code (created with a lot of help from forum users) which loops through a query, creates an Excel file (with formatting) in a directory on C:\

The query examines a fairly large table to identify missing data in 32 fields of the table for 134 different countries.

Dim rs As Recordset
Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
Dim strSql As String
Dim AndWhere As String

Dim xlApp As Object     'Excel.Application
Dim xlWB As Object      'Excel.Workbook
Dim xlSh As Object      'Excel.Worksheet
Dim sFilePath$

On Error GoTo ErrorHandler
    
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CountryCode, Country FROM qryMissingData")
    Set xlApp = CreateObject("Excel.Application")   'New Excel.Application
    
    Do While Not rs.EOF
      
       
        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
        
        sFilePath = "C:\KPMG\Missing data Files\" & "Missing Data For Country Code  " & rs!CountryCode
        
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", sFilePath, True
        DoCmd.DeleteObject acQuery, "CountryFile"

        Set xlWB = xlApp.Workbooks.Open(sFilePath)
        Set xlSh = xlWB.Sheets(1)
    
        xlApp.Visible = False
        xlSh.Range("A1").Select
        xlSh.Range(xlApp.Selection, xlApp.Selection.End(-4121)).Select   '-4121 = xlDown
        xlSh.Range(xlApp.Selection, xlApp.Selection.End(-4161)).Select   '-4161 = xlToRight
        xlApp.Selection.FormatConditions.Add 2, , "=LEN(TRIM(A1))=0"     '2 = xlExpression
        xlApp.Selection.FormatConditions(xlApp.Selection.FormatConditions.Count).SetFirstPriority
        With xlApp.Selection.FormatConditions(1).Interior
            .PatternColorIndex = -4105                                '-4105 = xlAutomatic
            .ThemeColor = 4                                           '4 = xlThemeColorLight2
            .TintAndShade = 0.599963377788629
        End With
        xlApp.Selection.FormatConditions(1).StopIfTrue = False
        
        xlWB.Close True
        rs.MoveNext
    Loop

    Call CountFiles
    
cmdExportMissingDataFiles_Bye:
    On Error Resume Next
    rs.Close: Set rs = Nothing
    Set xlWB = Nothing
    Set xlSh = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Err.Clear
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & vbCr & Err.Description
    ' make sure CountryFile is deleted anyway
    DoCmd.DeleteObject acQuery, "CountryFile"
    
    Resume cmdExportMissingDataFiles_Bye

When I was asked to create this, I was advised that all countries needed to complete all fields. Now I am advised that is not the case and some countries (8 in all) are not required to complete some fields. This obviously changes the criteria selection of each of these countries.

I have now created 8 additional queries to match the individual criteria for each country.

My question is, would a nested to loop be the way to use the above to code to send out the individual recordsets (I have never created a nested loop)?

What I am thinking is to loop through query1, create the Excel files and then change the recordset to query 2, create those files, etc until all 8 loops are completed.

Is this the right approach and if so, is someone able to assist me with the code.

Many thanks

 

[Gasman]

I would probably either add a field that identifies whether a country can have missing data or not, or a table of those that can, and link to your sql to only pull in those that cannot.

 

[Drand]

Thanks for your response Gasman

I do actually have a table that identifies the data completion requirements of each country.

I am not sure what you mean "link to your sql to only pull in those that cannot."

 

[Gasman]

By that I meant use that table as criteria, either as a join, or subquery.
The end result is you only bring in the countries you want to report on.

 

[Drand]

Thanks. Got it