Excel Automation - Gridlines

I am trying to manipulate an Excel file from within Access. When the
file is printed out I want either gridlines or to turn gridlines off.
What I'm getting is some columns have gridlines off and most have them
on.

My code is below, does anyone have any ideas?

Here's the code having to do with formating the sheet which is created
one line ahead with an outputto statement:

Set oApp = CreateObject("Excel.Application")
Set oExcel = oApp.Workbooks.Open(Filename:=mPathAndFile)


oApp.Visible = False
oApp.DisplayAlerts = False
oExcel.Worksheets("compliance export qry").Activate


With oExcel.Worksheets("compliance export qry").Columns


..Columns("A:S").AutoFit


End With


'set cells white


Columns("A:V").Select


With Selection.Interior
..ColorIndex = 2
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic


End With


With oExcel.Worksheets("compliance export qry").PageSetup
..Zoom = False
..FitToPagesTall = 1000
..FitToPagesWide = 1
..Orientation = xlLandscape
..PrintGridlines = False
..PrintTitleRows = "A1:S1"
'.LeftHeader =
..CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
..LeftFooter = "Report Created &D &T"
'.CenterFooter =
..RightFooter = "Page &P of &N"


..LeftMargin = oApp.InchesToPoints(0.25)
..RightMargin = oApp.InchesToPoints(0.25)
..TopMargin = oApp.InchesToPoints(0.75)
..BottomMargin = oApp.InchesToPoints(0.5)
..HeaderMargin = oApp.InchesToPoints(0.5)
..FooterMargin = oApp.InchesToPoints(0.25)


End With


With oExcel.Worksheets("compliance export qry").Range("A1:S1")


..Font.ColorIndex = 1
..Font.Bold = True


End With


Set oSheet = Nothing 'disconnect from the Worksheet
oExcel.Close SaveChanges:=True 'Save (and disconnect from) the
Workbook


Set oExcel = Nothing
oApp.Quit 'Close (and disconnect from) Excel
Set oApp = Nothing

think you need to understand the difference between gridlines and borders.
Gridlines turns off gridlines (there are either on or off for the entire
sheet), but do not affect borders. Your code applies borders to A:V. So
modify the code to remove those borders.

--
Regards,
Tom Ogilvy


"tmort@ci.grand-rapids.mi.us" wrote:

> I am trying to manipulate an Excel file from within Access. When the
> file is printed out I want either gridlines or to turn gridlines off.
> What I'm getting is some columns have gridlines off and most have them
> on.
>
> My code is below, does anyone have any ideas?
>
> Here's the code having to do with formating the sheet which is created
> one line ahead with an outputto statement:
>
> Set oApp = CreateObject("Excel.Application")
> Set oExcel = oApp.Workbooks.Open(Filename:=mPathAndFile)
>
>
> oApp.Visible = False
> oApp.DisplayAlerts = False
> oExcel.Worksheets("compliance export qry").Activate
>
>
> With oExcel.Worksheets("compliance export qry").Columns
>
>
> ..Columns("A:S").AutoFit
>
>
> End With
>
>
> 'set cells white
>
>
> Columns("A:V").Select
>
>
> With Selection.Interior
> ..ColorIndex = 2
> ..Pattern = xlSolid
> ..PatternColorIndex = xlAutomatic
>
>
> End With
>
>
> With oExcel.Worksheets("compliance export qry").PageSetup
> ..Zoom = False
> ..FitToPagesTall = 1000
> ..FitToPagesWide = 1
> ..Orientation = xlLandscape
> ..PrintGridlines = False
> ..PrintTitleRows = "A1:S1"
> '.LeftHeader =
> ..CenterHeader = "&14" & pFilename & "&10"
> '.RightHeader =
> ..LeftFooter = "Report Created &D &T"
> '.CenterFooter =
> ..RightFooter = "Page &P of &N"
>
>
> ..LeftMargin = oApp.InchesToPoints(0.25)
> ..RightMargin = oApp.InchesToPoints(0.25)
> ..TopMargin = oApp.InchesToPoints(0.75)
> ..BottomMargin = oApp.InchesToPoints(0.5)
> ..HeaderMargin = oApp.InchesToPoints(0.5)
> ..FooterMargin = oApp.InchesToPoints(0.25)
>
>
> End With
>
>
> With oExcel.Worksheets("compliance export qry").Range("A1:S1")
>
>
> ..Font.ColorIndex = 1
> ..Font.Bold = True
>
>
> End With
>
>
> Set oSheet = Nothing 'disconnect from the Worksheet
> oExcel.Close SaveChanges:=True 'Save (and disconnect from) the
> Workbook
>
>
> Set oExcel = Nothing
> oApp.Quit 'Close (and disconnect from) Excel
> Set oApp = Nothing
>
>

I have tried it without:

Columns("A:V").Select


With Selection.Interior
..ColorIndex = 2
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic


End With


with gridlines set to either true or false and get the same result when
I try to print or do a page preview.


Tom Ogilvy wrote:
> think you need to understand the difference between gridlines and borders.
> Gridlines turns off gridlines (there are either on or off for the entire
> sheet), but do not affect borders. Your code applies borders to A:V. So
> modify the code to remove those borders.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "tmort@ci.grand-rapids.mi.us" wrote:
>
> > I am trying to manipulate an Excel file from within Access. When the
> > file is printed out I want either gridlines or to turn gridlines off.
> > What I'm getting is some columns have gridlines off and most have them
> > on.
> >
> > My code is below, does anyone have any ideas?
> >
> > Here's the code having to do with formating the sheet which is created
> > one line ahead with an outputto statement:
> >
> > Set oApp = CreateObject("Excel.Application")
> > Set oExcel = oApp.Workbooks.Open(Filename:=mPathAndFile)
> >
> >
> > oApp.Visible = False
> > oApp.DisplayAlerts = False
> > oExcel.Worksheets("compliance export qry").Activate
> >
> >
> > With oExcel.Worksheets("compliance export qry").Columns
> >
> >
> > ..Columns("A:S").AutoFit
> >
> >
> > End With
> >
> >
> > 'set cells white
> >
> >
> > Columns("A:V").Select
> >
> >
> > With Selection.Interior
> > ..ColorIndex = 2
> > ..Pattern = xlSolid
> > ..PatternColorIndex = xlAutomatic
> >
> >
> > End With
> >
> >
> > With oExcel.Worksheets("compliance export qry").PageSetup
> > ..Zoom = False
> > ..FitToPagesTall = 1000
> > ..FitToPagesWide = 1
> > ..Orientation = xlLandscape
> > ..PrintGridlines = False
> > ..PrintTitleRows = "A1:S1"
> > '.LeftHeader =
> > ..CenterHeader = "&14" & pFilename & "&10"
> > '.RightHeader =
> > ..LeftFooter = "Report Created &D &T"
> > '.CenterFooter =
> > ..RightFooter = "Page &P of &N"
> >
> >
> > ..LeftMargin = oApp.InchesToPoints(0.25)
> > ..RightMargin = oApp.InchesToPoints(0.25)
> > ..TopMargin = oApp.InchesToPoints(0.75)
> > ..BottomMargin = oApp.InchesToPoints(0.5)
> > ..HeaderMargin = oApp.InchesToPoints(0.5)
> > ..FooterMargin = oApp.InchesToPoints(0.25)
> >
> >
> > End With
> >
> >
> > With oExcel.Worksheets("compliance export qry").Range("A1:S1")
> >
> >
> > ..Font.ColorIndex = 1
> > ..Font.Bold = True
> >
> >
> > End With
> >
> >
> > Set oSheet = Nothing 'disconnect from the Worksheet
> > oExcel.Close SaveChanges:=True 'Save (and disconnect from) the
> > Workbook
> >
> >
> > Set oExcel = Nothing
> > oApp.Quit 'Close (and disconnect from) Excel
> > Set oApp = Nothing
> >
> >

removing the code doesn't discount the possibility that the borders were
already present on the worksheet.

Before printing try adding lines like
With Activesheet.Cells
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End with


--
Regards,
Tom Ogilvy



"tmort@ci.grand-rapids.mi.us" wrote:

> I have tried it without:
>
> Columns("A:V").Select
>
>
> With Selection.Interior
> ..ColorIndex = 2
> ..Pattern = xlSolid
> ..PatternColorIndex = xlAutomatic
>
>
> End With
>
>
> with gridlines set to either true or false and get the same result when
> I try to print or do a page preview.
>
>
> Tom Ogilvy wrote:
> > think you need to understand the difference between gridlines and borders.
> > Gridlines turns off gridlines (there are either on or off for the entire
> > sheet), but do not affect borders. Your code applies borders to A:V. So
> > modify the code to remove those borders.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "tmort@ci.grand-rapids.mi.us" wrote:
> >
> > > I am trying to manipulate an Excel file from within Access. When the
> > > file is printed out I want either gridlines or to turn gridlines off.
> > > What I'm getting is some columns have gridlines off and most have them
> > > on.
> > >
> > > My code is below, does anyone have any ideas?
> > >
> > > Here's the code having to do with formating the sheet which is created
> > > one line ahead with an outputto statement:
> > >
> > > Set oApp = CreateObject("Excel.Application")
> > > Set oExcel = oApp.Workbooks.Open(Filename:=mPathAndFile)
> > >
> > >
> > > oApp.Visible = False
> > > oApp.DisplayAlerts = False
> > > oExcel.Worksheets("compliance export qry").Activate
> > >
> > >
> > > With oExcel.Worksheets("compliance export qry").Columns
> > >
> > >
> > > ..Columns("A:S").AutoFit
> > >
> > >
> > > End With
> > >
> > >
> > > 'set cells white
> > >
> > >
> > > Columns("A:V").Select
> > >
> > >
> > > With Selection.Interior
> > > ..ColorIndex = 2
> > > ..Pattern = xlSolid
> > > ..PatternColorIndex = xlAutomatic
> > >
> > >
> > > End With
> > >
> > >
> > > With oExcel.Worksheets("compliance export qry").PageSetup
> > > ..Zoom = False
> > > ..FitToPagesTall = 1000
> > > ..FitToPagesWide = 1
> > > ..Orientation = xlLandscape
> > > ..PrintGridlines = False
> > > ..PrintTitleRows = "A1:S1"
> > > '.LeftHeader =
> > > ..CenterHeader = "&14" & pFilename & "&10"
> > > '.RightHeader =
> > > ..LeftFooter = "Report Created &D &T"
> > > '.CenterFooter =
> > > ..RightFooter = "Page &P of &N"
> > >
> > >
> > > ..LeftMargin = oApp.InchesToPoints(0.25)
> > > ..RightMargin = oApp.InchesToPoints(0.25)
> > > ..TopMargin = oApp.InchesToPoints(0.75)
> > > ..BottomMargin = oApp.InchesToPoints(0.5)
> > > ..HeaderMargin = oApp.InchesToPoints(0.5)
> > > ..FooterMargin = oApp.InchesToPoints(0.25)
> > >
> > >
> > > End With
> > >
> > >
> > > With oExcel.Worksheets("compliance export qry").Range("A1:S1")
> > >
> > >
> > > ..Font.ColorIndex = 1
> > > ..Font.Bold = True
> > >
> > >
> > > End With
> > >
> > >
> > > Set oSheet = Nothing 'disconnect from the Worksheet
> > > oExcel.Close SaveChanges:=True 'Save (and disconnect from) the
> > > Workbook
> > >
> > >
> > > Set oExcel = Nothing
> > > oApp.Quit 'Close (and disconnect from) Excel
> > > Set oApp = Nothing
> > >
> > >
>
>