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
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