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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TomMicrosoft 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.