-
Sep 7th, 2011, 11:16 AM
#1
Thread Starter
New Member
vba code to hide gridlines in excel
I'm creating excel reports while in access and am having a problem hiding the excel reports' gridlines from the vba code.
-
Sep 7th, 2011, 11:21 AM
#2
Re: vba code to hide gridlines in excel
welcome to the forum,
Code:
ActiveWindow.DisplayGridlines = False
-
Sep 7th, 2011, 11:23 AM
#3
Re: vba code to hide gridlines in excel
Welcome to the forums
Code:
Dim oWin As Excel.Window
oWin.DisplayGridlines = False
-
Sep 7th, 2011, 11:40 AM
#4
Thread Starter
New Member
Re: vba code to hide gridlines in excel
I've been using that exact line of code and it hides the gridlines on the first report but the subsequent reports gives me "object variable with block variable not set" message.
-
Sep 9th, 2011, 09:07 AM
#5
Thread Starter
New Member
Re: vba code to hide gridlines in excel
I've been using that exact line of code and it hides the gridlines on the first report but the subsequent reports gives me "object variable with block variable not set" message.... anybody?
-
Sep 11th, 2011, 06:12 AM
#6
Re: vba code to hide gridlines in excel
Welcome to the forum im_Isabel
Please show us the code that you are using.
Sid
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 12th, 2011, 09:18 AM
#7
Thread Starter
New Member
Re: vba code to hide gridlines in excel
Here's some of my code:1st report works great hiding the gridlines and adding auto filters to 2 of the sheets after that the Active window doesn't seem to be recognized. I've also tried coding it within the With xlsheet and that didn't make a difference.
Option Compare Database
Option Explicit
Public Sub CreateReport(ByVal strFund As String)
Dim cnt As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strPath, strFileName As String
Dim showFile As String
Dim i As Integer
Dim Count As Long
Dim lkuName As String
Dim rsB As New ADODB.Recordset
Dim rsC As New ADODB.Recordset
Dim strFilexls As String
Dim strTable As String
Dim xlappObj As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
showFile = ""
strPath = CurrentProject.Path
strFileName = "\" & strFund & " FAS161 Report as of " & Form_frmMain.cboMonthTo.Value & " " & Form_frmMain.cboDayTo.Value & ", " & Form_frmMain.cboYr_To.Value & ".xls"
strFileName = strPath & strFileName
showFile = showFile & strFileName & vbCrLf
'check if file already exits if yes delete the file
IfExists (strFileName)
'****************************************************************
'* Export tblDtlDerivatives to xlsheet and rename tab
'****************************************************************
Set cnt = CurrentProject.Connection
strPath = CurrentProject.Path
rsB.Open "tblDtlDerivatives", cnt, adOpenKeyset, adLockOptimistic
If rsB.RecordCount < 1 Then
MsgBox "*** No Derivatives for Fund: " & strFund & " ***", vbInformation
End If
strTable = "tblDtlDerivatives"
'export
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTable, strFileName, True
'****************************************************************
'* Export tblDtlForwards to xlsheet and rename tab
'****************************************************************
rsC.Open "tblDtlForwards", cnt, adOpenKeyset, adLockOptimistic
If rsC.RecordCount < 1 Then
MsgBox "*** No Forwards for Fund: " & strFund & " ***", vbInformation
End If
strTable = "tblDtlForwards"
'export
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTable, strFileName, True
'********************************************************************************************
Set xlappObj = New Excel.Application
xlappObj.Visible = True
xlappObj.Workbooks.Open (strFileName)
' Set xlbook = xlappObj.ActiveWorkbook
' Set xlsheet = xlappObj.ActiveSheet
'turn off excel alerts
'''' Excel.Application.DisplayAlerts = False
' xlappObj.Sheets("tblDtlForwards").Activate
' With xlsheet
' .Range("A1").AutoFilter
' End With
' xlappObj.Sheets("tblDtlDerivatives").Activate
' With xlsheet
' .Range("A1").AutoFilter
' End With
'rename DtlDerivatives imported sheet
xlappObj.Sheets("tblDtlDerivatives").Name = "Detail - Derivatives"
ActiveSheet.Range("A1").AutoFilter
'rename DtlForwards imported sheet
xlappObj.Sheets("tblDtlForwards").Name = "Detail - Forwards"
xlappObj.Sheets("Detail - Forwards").Activate
ActiveSheet.Range("A1").AutoFilter
'*********************************************************************************************
'Add the 'Summary' sheet
'*********************************************************************************************
xlappObj.Sheets("Detail - Derivatives").Activate
xlappObj.Sheets.Add.Name = "Summary"
Set xlbook = xlappObj.ActiveWorkbook
Set xlsheet = xlappObj.ActiveSheet
With xlsheet
ActiveWindow.Zoom = 85
ActiveWindow.DisplayGridlines = False
End With
'*********************************************************************************************
'Add up each column in the report and calculate averages
'*********************************************************************************************
Call CreateSummaryHdr(xlbook, xlsheet, strFund)
lkuName = "Futures - Buy"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Futures - Sell"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Written Options"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Purchased Options"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Forward Contracts - Buy"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Forward Contracts - Sell"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Credit Default SWAP - Buy"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Credit Default SWAP - Sell"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Interest Rate SWAP"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
lkuName = "Total Return Bond SWAP"
Call GetTblData(xlbook, xlsheet, strFund, lkuName)
xlappObj.ActiveWorkbook.Save
' xlappObj.ActiveWorkbook.Close
' xlappObj.Application.Quit
Set xlsheet = Nothing
Set xlbook = Nothing
' Set xlappObj = Nothing
MsgBox "Report Complete.." & vbCrLf & showFile
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|