Michael Vasilevsky
asked on
Clean up Exporting Report to Excel
Hi, I'm exporting an Access report to Excel using VBA and have a few issues I need to clean up.
I'm opening and exporting the report using the below Click subroutine and then I'm formatting the Excel report using the below function. I've got a couple problems:
1. I've hardcoded the location of the exported Excel file. How can I get that information programmically? I.e. I know "DoCmd.OutputTo acReport, stDocName" prompts the user to specify the location (file path) and file name for the new exported file. How can I capture that information once the user clicks "Ok" so I can use it later on?
2. In the function I have the line "Set WS = gobjExcel.Workbooks("rpt_O verviewfor Export.xls ").Sheets( 1)" which works fine unless there is any other instance of Excel open. How can I make this more robust so that it doesn't matter what other Excel files are open.
Any other tips/tricks/suggestions are welcome!
Thanks,
mv
Private Sub cmdOverviewRptExport_Click ()
On Error GoTo Err_cmdOverviewRptExport_C lick
Dim stDocName As String
stDocName = "rpt_OverviewforExport"
DoCmd.OpenReport stDocName, acPreview
DoCmd.OutputTo acReport, stDocName
Set xlApp = CreateObject("excel.applic ation")
xlApp.Visible = True
xlApp.Workbooks.Open ("C:\TVS\rpt_OverviewforEx port.xls")
xlApp.Application.ActiveWo rkbook.Run AutoMacros (xlAutoOpen)
Call FormatOverviewforExportRpt
DoCmd.Close acReport, stDocName, acSave
Exit_cmdOverviewRptExport_ Click:
Exit Sub
Err_cmdOverviewRptExport_C lick:
MsgBox Err.Description
Resume Exit_cmdOverviewRptExport_ Click
End Sub
Function FormatOverviewforExportRpt ()
Dim gobjExcel As Excel.Application
Dim WS As Excel.Worksheet
Dim RNG As Excel.Range
Set gobjExcel = GetObject(, "Excel.Application")
Set WS = gobjExcel.Workbooks("rpt_O verviewfor Export.xls ").Sheets( 1)
'Set the Zoom percentage to 80%
gobjExcel.ActiveWindow.Zoo m = 80
With WS
'Make the column headers bold
.Rows("1:1").Font.Bold = True
'Format the dates
.Columns("D:R").NumberForm at = "m/d/yyyy"
'Set column widths
.Columns("A:A").ColumnWidt h = 14.29
.Columns("B:B").ColumnWidt h = 8.86
.Columns("C:C").ColumnWidt h = 33.29
.Columns("D:R").ColumnWidt h = 8.86
'Set row height
.Rows(1).RowHeight = 25.5
'Add shading to row 1
.Range("A1:R1").Select
.Range("R1").Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
'Allow text wrapping
.Columns("A:R").WrapText = True
'Add conditional formatting
.Columns("D:R").FormatCond itions.Del ete
.Columns("D:R").FormatCond itions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=TODAY()", Formula2:="1"
.Columns("D:R").FormatCond itions(1). Font.Color Index = 3
.Columns("D:R").FormatCond itions(1). Interior.C olorIndex = 3
.Columns("D:R").FormatCond itions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=TODAY()", Formula2:="=TODAY()+14"
.Columns("D:R").FormatCond itions(2). Font.Color Index = 6
.Columns("D:R").FormatCond itions(2). Interior.C olorIndex = 6
End With
End Function
I'm opening and exporting the report using the below Click subroutine and then I'm formatting the Excel report using the below function. I've got a couple problems:
1. I've hardcoded the location of the exported Excel file. How can I get that information programmically? I.e. I know "DoCmd.OutputTo acReport, stDocName" prompts the user to specify the location (file path) and file name for the new exported file. How can I capture that information once the user clicks "Ok" so I can use it later on?
2. In the function I have the line "Set WS = gobjExcel.Workbooks("rpt_O
Any other tips/tricks/suggestions are welcome!
Thanks,
mv
Private Sub cmdOverviewRptExport_Click
On Error GoTo Err_cmdOverviewRptExport_C
Dim stDocName As String
stDocName = "rpt_OverviewforExport"
DoCmd.OpenReport stDocName, acPreview
DoCmd.OutputTo acReport, stDocName
Set xlApp = CreateObject("excel.applic
xlApp.Visible = True
xlApp.Workbooks.Open ("C:\TVS\rpt_OverviewforEx
xlApp.Application.ActiveWo
Call FormatOverviewforExportRpt
DoCmd.Close acReport, stDocName, acSave
Exit_cmdOverviewRptExport_
Exit Sub
Err_cmdOverviewRptExport_C
MsgBox Err.Description
Resume Exit_cmdOverviewRptExport_
End Sub
Function FormatOverviewforExportRpt
Dim gobjExcel As Excel.Application
Dim WS As Excel.Worksheet
Dim RNG As Excel.Range
Set gobjExcel = GetObject(, "Excel.Application")
Set WS = gobjExcel.Workbooks("rpt_O
'Set the Zoom percentage to 80%
gobjExcel.ActiveWindow.Zoo
With WS
'Make the column headers bold
.Rows("1:1").Font.Bold = True
'Format the dates
.Columns("D:R").NumberForm
'Set column widths
.Columns("A:A").ColumnWidt
.Columns("B:B").ColumnWidt
.Columns("C:C").ColumnWidt
.Columns("D:R").ColumnWidt
'Set row height
.Rows(1).RowHeight = 25.5
'Add shading to row 1
.Range("A1:R1").Select
.Range("R1").Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
'Allow text wrapping
.Columns("A:R").WrapText = True
'Add conditional formatting
.Columns("D:R").FormatCond
.Columns("D:R").FormatCond
Formula1:="=TODAY()", Formula2:="1"
.Columns("D:R").FormatCond
.Columns("D:R").FormatCond
.Columns("D:R").FormatCond
Formula1:="=TODAY()", Formula2:="=TODAY()+14"
.Columns("D:R").FormatCond
.Columns("D:R").FormatCond
End With
End Function
Does it happen anytime Excel is open or only when this specific file is open?
ASKER
? Does what happen?
The code opens this specific file. The problem is when there is another Excel file already open, I get an invalid subscript error. Even if there is no file open, just an instance of Excel, I get the error.
HTH,
mv
The code opens this specific file. The problem is when there is another Excel file already open, I get an invalid subscript error. Even if there is no file open, just an instance of Excel, I get the error.
HTH,
mv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.