CODE for EXCEL output not executing in access - PrintTitleRows

[BlueWolverine]

Hello,
MS Access/Excel 2003 on XP PRO.

I have a subroutine that outputs a query to an excel file and then formats
it automatically, and everything works, I get no compile errors, except for
one little failure.

when I execute the line with the > in front of it is the problem.


With obj_excel 'Excel application
'whole bunch of stuff in here

With .activesheet.PageSetup

.PrintTitleRows = "$1:$1"

'plus other
end with

'whole bunch of stuff in here
end with

this line does not happen. It executes, moves to the next line of code, but
it does not happen. the print title rows in the output excel file are not
set. the whole point of writing code like this is to prevent manually
formatting a document so this irritates the hell out of me, since I may
output 15 documents like this in a setting.

Any idea why this line of code is not executing?
Thanks.

 

[Douglas J. Steele]

I find that when Automating Excel, it's usually a good idea to avoid using
..ActiveSheet, and instead explicitly refer to the sheet you mean.

Take a look at my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html or
read it online at http://www.vb123.com/kb/200507_ds_excel.htm

 

[BlueWolverine]

With obj_worksheet.PageSetup
.PrintTitleRows = "$1:$1"
'.PrintTitleColumns = ""
End With
With .activesheet.PageSetup
.PrintTitleRows = "$1:$1"

end with
I did this, with obj_worksheet=obj_Workbook.sheets("NAME OF SHEET")

IT STILL DOES NOT WORK. HELP!

 

[James A. Fortune]

Hello,
MS Access/Excel 2003 on XP PRO.

I have a subroutine that outputs a query to an excel file and then formats
it automatically, and everything works, I get no compile errors, except for
one little failure.

when I execute the line with the > in front of it is the problem.

With obj_excel 'Excel application
'whole bunch of stuff in here

With .activesheet.PageSetup> .PrintTitleRows = "$1:$1"

'plus other
end with

'whole bunch of stuff in here
end with

this line does not happen. It executes, moves to the next line of code, but
it does not happen. the print title rows in the output excel file are not
set. the whole point of writing code like this is to prevent manually
formatting a document so this irritates the hell out of me, since I may
output 15 documents like this in a setting.

Any idea why this line of code is not executing?
Thanks.

Maybe I can help you follow Douglas' advice. When formatting
worksheets from scratch, I reference each worksheet directly from the
Excel.Application object. For example:

Set wsheet = obj_excel.Worksheets("Sheet1")
wsheet.Name = Format(Date, "mmm") & Format(Date, "yy")
With obj_excel
.DisplayAlerts = False
.Worksheets("Sheet2").Delete
.Worksheets("Sheet3").Delete
.DisplayAlerts = True
End With

James A. Fortune
(e-mail address removed)

 

[sduis]

Hello,
MS Access/Excel 2003 on XP PRO.

I have a subroutine that outputs a query to an excel file and then formats
it automatically, and everything works, I get no compile errors, except
for
one little failure.

when I execute the line with the > in front of it is the problem.


With obj_excel 'Excel application
'whole bunch of stuff in here

With .activesheet.PageSetup
'plus other
end with

'whole bunch of stuff in here
end with

this line does not happen. It executes, moves to the next line of code,
but
it does not happen. the print title rows in the output excel file are not
set. the whole point of writing code like this is to prevent manually
formatting a document so this irritates the hell out of me, since I may
output 15 documents like this in a setting.

Any idea why this line of code is not executing?
Thanks.