Excel automation from Access

[scclem]

I am using the following code to make formatting changes to an Excel workbook that is created by an Access export process. The resulting file will then be emailed as an attachment.

The problem I am having is that when the resulting file is opened in Excel, the file is hidden. I then need to go through the Window/Unhide menu options to view the file.

Any suggestions?

Code:

Public Sub ChangePageSetup(FileName As String)

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

On Error GoTo Error_ChangePageSetup

Set objXLBook = GetObject(FileName)
Set objXLApp = objXLBook.Parent
Set objXLSheet = objXLBook.Worksheets("Pastdueorders&quot

With objXLSheet
.PageSetup.LeftMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.RightMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.TopMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.BottomMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.Zoom = 75
.PageSetup.Orientation = xlLandscape
.Columns.AutoFit
End With

objXLBook.Save

objXLApp.Application.Quit

Set objXLSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

Exit_ChangePageSetup:
Exit Sub

Error_ChangePageSetup:

MsgBox Err.Number & " " & Err.Description
Resume Exit_ChangePageSetup

End Sub

Scott

 

[ide]

Public Sub ChangePageSetup(FileName As String)

Dim objXLApp As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

On Error GoTo Error_ChangePageSetup


Set objXLBook = objXLApp.Workbooks.Open(FileName)
Set objXLSheet = objXLBook.Worksheets("Pastdueorders&quot

With objXLSheet
.PageSetup.LeftMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.RightMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.TopMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.BottomMargin = objXLApp.InchesToPoints(0.5)
.PageSetup.Zoom = 75
.PageSetup.Orientation = xlLandscape
.Columns.AutoFit
End With

objXLApp.Application.Visible = True
objXLBook.Save

Exit_ChangePageSetup:

On Error Resume Next
objXLApp.Application.Quit

Set objXLSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

Exit Sub

Error_ChangePageSetup:

MsgBox Err.Number & " " & Err.Description
Resume Exit_ChangePageSetup

End Sub

 

[scclem]

Thank you for your suggestion. Even though it didn't quite work, it got me pointed in the right direction. I ended up recording a macro to see how Excel would write the code. From there I was able to adapt it to my code.

In the end:

objXLApp.Application.Visible = True

was replaced by

objXLApp.Application.Windows(<filename>).Visible = True

and everything works as planned.

Again, thanks for your help.

Scott