Is there a better way to create this Excel file?

[sheusz]

Hi all

Thanks for taking the time to read this post.

I need to create an excel workbook with 3 sheets in it. The contents of the sheets will be determined by the value of a field in a query. The field is Region and will have a value of either N, M or B

The result I want is that all records where Region = "N" are written to sheet1 in the spreadsheet. Records where Region = "M" are written in to sheet2 and so on...

I have a solution that I use, but it uses 3 queries each based on the Region value, rather than 1 query with the region values in it. It works OK, but I feel that there must be a more "elegant" solution using a single query.

It's not a "deal breaker" but I'm keen to try and be as efficient in my code as possible.



This is my current code

Private Sub ExportTechRefbtn_Click()
On Error GoTo Err_ExportTechRefbtn_Click
    Dim wsShell As Object
    Dim stDocName As String
    Dim strSaveFile As String
    strSaveFile = "C:\Temp\TechRefAppServiceContactExport.xlsx"  'location to write excel file
    
    If MsgBox("Confirm creation of output file..." & _
        vbCrLf & vbCrLf & strSaveFile & _
        vbCrLf & vbCrLf & _
        "(Folder location will open after successful creation of the file.)", vbYesNoCancel + vbQuestion, " Confirm Export") <> vbYes Then
        Exit Sub
    End If
    
    If Dir("c:\Temp\") = "" Then
        MkDir ("c:\Temp\")
    End If
        
    If Dir(strSaveFile) <> "" Then
        Kill strSaveFile
    End If
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TechAppExport-N", strSaveFile
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TechAppExport-M", strSaveFile
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TechAppExport-B", strSaveFile
    
    Set wsShell = CreateObject("WScript.Shell")
    wsShell.Run "c:\Temp"
 
    Set wsShell = Nothing
       
Exit_ExportTechRefbtn_Click:
    Exit Sub
:confused:
Err_ExportTechRefbtn_Click:
    If Err.Number <> 2501 Then
        If Err.Number = 70 Then
            MsgBox "The spreadsheet " & strSaveFile & " file is open and cannot be written to.", vbOKOnly + vbExclamation, "Error"
        End If
        Else
        MsgBox "Error Code " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Error"
    End If

    Resume Exit_ExportTechRefbtn_Click
End Sub

[Welshgasman]

Other than Excel automation, I cannot see how you can do it in one pass?

[pbaldy]

Untested but what about a single query with a criteria pointing to a form control? Your code would place each value into the control and then export. Still 3 exports but a single query.

[sheusz]

Untested but what about a single query with a criteria pointing to a form control? Your code would place each value into the control and then export. Still 3 exports but a single query.

Hi Pbaldy
That's an interesting thought. Currently there is no form associated with this code as it really doesn't need it. It runs from a command button on a general form that doesn't have these controls on it. I could add them, but it would be a bit superfluous.

[pbaldy]

You could also try a TempVar instead of a form control.

[pbaldy]

Oh, and the form doesn't need to be associated with the data. I use hidden controls on main menu forms for criteria sometimes.

[sheusz]

Thanks. I'll give that a try.