vba code to hide gridlines in excel-VBForums
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,158

    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
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: vba code to hide gridlines in excel

    Welcome to the forums
    Code:
    Dim oWin As Excel.Window
    oWin.DisplayGridlines = False
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  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
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: vba code to hide gridlines in excel

    Welcome to the forum im_Isabel

    Please show us the code that you are using.

    Sid
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.