Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Gridlines
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Gridlines
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 " 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Gridlines
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 " 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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Gridlines are there, then Custom View, gridlines disappear in xl 2 | Excel Discussion (Misc queries) | |||
excel 3 gridlines. | Excel Worksheet Functions | |||
gridlines in excel | Excel Discussion (Misc queries) | |||
Row colors overlay the gridlines...how do I show gridlines? | Excel Discussion (Misc queries) | |||
In Excel, gridlines won't print--File,PageSetup,Sheet,Gridlines-- | New Users to Excel |