Is there a registry setting to force printing gridlines, column headers for Excel 2007 on Windows XP?

Question

I think I already know the answer to this ("NO!") but I thought I would reach out and hope that someone can prove me wrong.

I have a lot (tens of thousands) of Excel spreadsheets that I will be imaging (printing to TIFF) for a client. They have just added a new wrinkle to request the gridlines and the column headers on the resultant TIFFs.

Is there a way to force this from the registry? Or would it require opening each document and checking the correct boxes?

Answer 1

i do not know of any registry settings for this, but i imagine if all the documents to be printed were in a single folder it would not be too hard to handle with a macro. working example below.

Sub GridsHeaders()
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim FLS As Object, F As Object
    Dim fWB As Workbook, fSheet As Worksheet
    Dim Loc As String

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        Loc = .SelectedItems(1)
    End With

    Set FLS = FSO.GetFolder(Loc).Files
    For Each F In FLS
        Loc = UCase(Mid(F.Name, InStrRev(F.Name, ".") + 1))
        If Loc = "XLS" Or Loc = "XLSX" Then
            Set fWB = Workbooks.Open(F.Path)
            For Each fSheet In fWB.Sheets
                fSheet.PageSetup.PrintGridlines = True
                fSheet.PageSetup.PrintHeadings = True
            Next fSheet
            fWB.Close True
        End If
    Next F
End Sub