formating in Excel

[RSusanto]

Hi,

I tried to format the excel sheet before I print it from Access
the code are :

Set oExcel = CreateObject("excel.application")
Set oInvbook = oExcel.Workbooks.Open(sheetLocation, , , , "xxxxxx", , ,
, , True)
Set oSheet = oExcel.Workbooks("requirements.xls").Sheets("Schedule
Data")

oExcel.Range("A2", "AK3").Select
oExcel.Rows("2:3").Select
oExcel.Selection.Copy
oExcel.ActiveWindow.SmallScroll Down:=30
oExcel.Rows("82:82").Select
oExcel.Selection.Insert shift:=xlDown
oExcel.Range("B82").Select
oExcel.CutCopyMode = False
oExcel.ActiveSheet.HPageBreaks(1).Location = oExcel.Range("A81")
oExcel.Range("B87").Select
oExcel.ActiveSheet.HPageBreaks(2).DragOff Direction:=xlDown,
RegionIndex:=1 <-----error
oExcel.ActiveWindow.SelectedSheets.PrintPreview
----------------------------------------------------------------------------------

But it gave me error for this line :

oExcel.ActiveSheet.HPageBreaks(2).DragOff Direction:=xlDown, RegionIndex:=1

the msg : Object Error 1004
Object define error.

Can Anybody help pls ?

 

[Ken Snell [MVP]]

ACCESS does not know what the value of EXCEL's intrinsic constants (e.g.,
xlDown) are unless you use early binding (which you're not using).

Change xlDown to -4121 in that code step.

 

[John Nurick]

This is an Excel question more than an Access one, but I think you are
using the HPageBreak.DragOff method in a context for which it was not
intended. Use the page break's Delete method instead and things should
work better.

 

[RSusanto]

From the previous line it did recognize.
but then it became error after that.

Even I changed it to the -4121, still give me some error.

I have no idea about this one. Any other clue ?

thanks

 

[Ken Snell [MVP]]

Did you see John Nurick's reply in this thread?