Excel PageSetup call causes crash

[calalli]

I have an application written in Office 97 that is being converted to Office 2003. The application exports the results of a large query to Excel for formatting.

Early in the code I set up the references

Set MyXLApp = GetObject(, "Excel.Application")
Set MyXL = GetObject(xlsBookName)

later I set a reference to the active page
Set ThisPage = MyXL.ActiveSheet

and format it for example:
ThisPage.Cells(2, 2).NumberFormat = "@"

But the code that uses PageSetup causes Excel to crash,
I get the Windows dialog about sending the error report to MS on the following statements.
Whether I use the With statement or reference it directly, PageSetup fails.

With ThisPage.PageSetup
.PrintTitleRows = "$1:$8"
.PrintTitleColumns = ""
.LeftMargin = MyXLApp.InchesToPoints(0.5)
.RightMargin = MyXLApp.InchesToPoints(0.5)
.TopMargin = MyXLApp.InchesToPoints(0.5)
.BottomMargin = MyXLApp.InchesToPoints(0.5)
.HeaderMargin = MyXLApp.InchesToPoints(0)
.FooterMargin = MyXLApp.InchesToPoints(0)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
End With

Has anyone seen this? I thought maybe it was a faulty install, but it occurs on several machines.

 

[SkipVought]

Hi,

There is nothing that relates these two objects

Set MyXLApp = GetObject(, "Excel.Application")
Set MyXL = GetObject(xlsBookName)

SO...

using MyXLApp in your code will have disastrous results.

rather

Set MyXLApp = GetObject(, "Excel.Application")
set MyXL = MyXLApp.Workbooks.Open xlsBookName
...

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[willir]

Also...
large query = Resource problem

Access and Excel both chew up resources. In your case, do you have enough memory? Do you have enough working space / room in the temp directory?

Try running
- a more simple job first to test your code
- the job on a more power computer with more memory and disk space (where the TEMP files are writen)

Richard

 

[calalli]

Richard and Skip, thank you for your replies.

I tried to simplify the question and omitted to much information perhaps. This application has worked fine in production for over four years, it is the conversion to Access 2003 that is problematic. I cut out much code attempting to get the point across.

Skip, I cannot find where I tie the two fields, MyXL and MyXLApp together, but I must, it has worked well and still does until I attempt to use PageSetup. I am looking at rewriting the entire procedure that sets the Excel references. I was very new to VBA when I wrote this (still am).

Richard, this app actually submits a job on a mainframe and then imports the output file. In attempting to resolve this I have cut that file to one record -- there is no problem with PC resources.

Thanks again. If someone is using PageSetup in Access to format an excel spreadsheet perhaps they could post an example of working code?

Chuck

 

[PHV]

You may try this:
Set MyXL = GetObject(xlsBookName)
Set MyXLApp = MyXL.Application

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244