I'm exporting an Access report to Excel and formatting the spreadsheet with the below code in my OnClick event.
I would like to (in order of priority):
1. Remove the prompt to save the file on xcelapp.quit (assuming that's when that prompt appears)
2. Have the user be able to specify the file path instead of having to use the default
3. Have the user be able to specify the file name instead have it hard coded
Any help is appreciated!
Dim stDocName As String
stDocName = "rpt_Report1"
DoCmd.OutputTo acReport, stDocName
Dim xcelwb As Excel.Workbook
Dim xcelapp As Excel.Application
Dim Sheet As Excel.Worksheet
Set xcelapp = New Excel.Application
Set xcelwb = xcelapp.Workbooks.Open("rpt_Report1.xls")
Set Sheet = xcelapp.ActiveWorkbook.Sheets(1)
'format the excel data
.Rows("1:1").Font.Bold = True
Set xcelapp = Nothing
Set xcelwb = Nothing
Set Sheet = Nothing