Advanced Office Automation Question

[Shake412]

I am writing reports from Access to Excel using automation. I want the reports to be saved with no scroll bars and with a zoom setting, plus other settings, using the following code…

With wksReport
.DisplayPageBreaks = False
.Activate
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.Zoom = 88
End With
End With

With appExcel
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
End With


The problem is that when I’ve finished running the code I am still left with an Excel Image running in Task Manager. This causes problems when running again without manually deleting the image using “End Process” and/or closing the Access application.

If run without taking this action I get a RPC Server Unavailable error. This error I’ve seen before with Automation and can usually sever all links by setting the object references to nothing, but I don’t know what to do in this instance.

Any help is greatly appreciated and I’ll make sure Santa gives you whatever you want this Christmas

Thanks

 

[RobBroekhuis]

So

set appExcel = nothing

doesn't do the trick in this case?
Rob

 

[Shake412]

I set all everthing I can think of to Nothing as follows

Set rngRegions = Nothing
Set wksReport = Nothing
Set wkbReport = Nothing
Set appExcel = Nothing

And Have tried the following but then just cause errors.

Set appExcel.ActiveWindow = Nothing
Set ActiveWindow = Nothing

Any Ideas?

Thanks for looking.

 

[RobBroekhuis]

I don't do Access, but I tried the following from Word:

Dim xl As Excel.Application
Set xl = New Excel.Application
With xl
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
End With
xl.Visible = True
MsgBox ("it's excel&quot
Set xl = Nothing

I shut Excel down before running the code, made sure I had no Excel-related processes going; when the msgbox came up, I had an excel process - when I allowed the sub to complete, it went away without a trace. So it seems to work fine. How do you create the Excel object in the first place? Perhaps my understanding of Windows terminology is lacking, but what do you mean by an "Excel image"?
Rob

 

[Shake412]

Image and process are the same thing I believe.

I Use the following to create an instance in Excel…

Private appExcel As Excel.Application
Set appExcel = CreateObject("Excel.Application&quot

I tried using ‘Set appExcel = New Excel.Application’ instead but the result is the same.

I think the problem is with the “ActiveWindow” part of the code.

Thanks again for trying.

 

[RobBroekhuis]

I reproduced your problem. The Excel app stayed when I put in the window code. It went away when I closed the workbook before setting the application object to nothing. Probably, using xl.quit would have worked fine as well.

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Add
With xl.ActiveSheet
.DisplayPageBreaks = False
.Activate
With xl.ActiveWindow
.DisplayGridLines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.Zoom = 88
End With
End With

With xl
.DisplayFormulaBar = False
.DisplayStatusBar = False
.DisplayScrollBars = False
End With
xl.Visible = True
MsgBox ("it's excel!&quot
xl.ActiveWorkbook.Close

Rob

 

[Shake412]

Your version does indeed work perfectly. Under further screwtany I've managed to locate the problem which happenes to be the line of code:-

With xl.ActiveWindow

where I left off the xl. or appExcel. in my case.

Problem solved.

Thanks once again for your time and effort, and for teaching me something new.

Have a great Christmas