Results 1 to 7 of 7

Thread: vba code to hide gridlines in excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2011
    Posts
    4

    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.

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: vba code to hide gridlines in excel

    welcome to the forum,
    Code:
    ActiveWindow.DisplayGridlines = False
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: vba code to hide gridlines in excel

    Welcome to the forums
    Code:
    Dim oWin As Excel.Window
    oWin.DisplayGridlines = False

  4. #4

    Thread Starter
    New Member
    Join Date
    Sep 2011
    Posts
    4

    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.

  5. #5

    Thread Starter
    New Member
    Join Date
    Sep 2011
    Posts
    4

    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?

  6. #6
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Sep 2011
    Posts
    4

    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
  •  



Click Here to Expand Forum to Full Width