Access: Transfer data then automatically open Excel

Hello,

I currently have a click event that upon pressing a control button:

1) Prompts a Yes/No question. If No, then nothing. If yes:
2) Download two queries to separate excel sheets

Is it possible to modify this click event so that it includes the additional steps:

3) MSG box "Download complete. Do you want to open the spreadsheet?" (option yes/no). If no is clicked then do nothing. If yes is selected:
4) Open the excel document C:\data.xlsx

Below is my current code
Private Sub cmd_ExportdatatoExcel_Click()
On Error GoTo Err_cmd_ExportdatatoExcel_Click
 
Dim Msg As String
Msg = "Do you want to export patient and treatment data to Excel? This will automatically overwrite existing data?"
Style = vbYesNo
 
Response = MsgBox(Msg, Style)
Select Case Response
  Case vbYes
DoCmd.TransferSpreadsheet acExport, 10, "qry_excel_patient", "C:\data.xlsx", True, "Patientsheet"
DoCmd.TransferSpreadsheet acExport, 10, "qry_excel_treatment", "C:\data.xlsx", True, "Treatmentsheet"
   '/do nothing
End Select
Exit_cmd_ExportdatatoExcel_Click:
    Exit Sub
 
Err_cmd_ExportdatatoExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmd_ExportdatatoExcel_Click
 
End Sub

Open in new window

DozingquinnAsked:
Who is Participating?
 
TomConnect With a Mentor Microsoft ISV PartnerCommented:
Private Sub cmd_ExportdatatoExcel_Click()
On Error GoTo Err_cmd_ExportdatatoExcel_Click
 
Dim Msg As String
Msg = "Do you want to export patient and treatment data to Excel? This will automatically overwrite existing data?"
Style = vbYesNo
 
Response = MsgBox(Msg, Style)
Select Case Response
  Case vbYes
DoCmd.TransferSpreadsheet acExport, 10, "qry_excel_patient", "C:\data.xlsx", True, "Patientsheet"
DoCmd.TransferSpreadsheet acExport, 10, "qry_excel_treatment", "C:\data.xlsx", True, "Treatmentsheet"
   '/do nothing

Msg = "Do you want to open Excel sheet?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
Select Case Response
Case vbYes
Shell ("excel.exe C:\data.xls")
End Select


End Select
Exit_cmd_ExportdatatoExcel_Click:
    Exit Sub
 
Err_cmd_ExportdatatoExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmd_ExportdatatoExcel_Click
 
End Sub


End Select
Exit_cmd_ExportdatatoExcel_Click:
    Exit Sub
 
Err_cmd_ExportdatatoExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmd_ExportdatatoExcel_Click
 
End Sub
0
 
TomMicrosoft ISV PartnerCommented:
opps typo error:
Shell ("excel.exe C:\data.xlsx")
0
 
DozingquinnAuthor Commented:
Thanks for the code. There were some slight bugs that I managed to fix (end select twice & two err_cmd statements) but apart from that the solution was great. Thanks!
0
All Courses

From novice to tech pro — start learning today.