Set Excel Document Properties Programmatically
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: Set Excel Document Properties Programmatically

  1. #1
    Join Date
    Sep 2008
    Posts
    68

    Exclamation Set Excel Document Properties Programmatically

    I was reading this article on how to Set Word Document Properties Programmatically. Here is the link:
    http://msdn.microsoft.com/en-us/maga...163637.aspx#S4

    Does anyone have any idea how i can do the same thing but for excel documents. I am new to Visual Basic so any help is greatly appreciated.

  2. #2
    Join Date
    Dec 2007
    Posts
    234

    Re: Set Excel Document Properties Programmatically

    Pretty much the same way (note: I didn't check out the link...) ... once you have an instance of the object, you can manipulate it. If you find that you can't find a particular property, try recording a macro, go through the menu, or the action of setting the property using Excel...then view the macro... Macros get recorded as VBA code... you can't usually just copy paste it, but it does give some insight on the objects and where some properties are kept (some aren't always obvious).

    -tg
    * I don't respond to private requests for help. It's not conducive to the general learning of others.-I also subscribe to all threads I participate, so there's no need to pm when there's an update.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help - how to remove eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to???
    * On Error Resume Next is error ignoring, not error handling(tm). * Use Offensive Programming, not Defensive Programming.
    "There is a major problem with your code, and VB wants to tell you what it is.. but you have decided to put your fingers in your ears and shout 'I'm not listening!'" - si_the_geek on using OERN
    MVP '06-'10

  3. #3
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,853

    Re: Set Excel Document Properties Programmatically

    Here are a few values which I set when using Excel as a Reporting Mechanism

    You will see here how to set headings and footers on each page plus page numbering, Fonts, etc

    The commented out code should also work if you need to apply it to your application output

    The only downside I have found is that while it works perfectly, it is quite slow in creating the formatting in the first place


    Code:
    Dim xlCol As Long
    Dim ColRange As String
    
    Dim XLRow As Long
    'Dim appExcel 'As Excel.Application
    Dim wbkNew 'As Excel.Workbook
    Dim wksNew 'As Excel.Worksheet
    'Dim wksNew 'As Excel.Worksheet
    
    On Error GoTo XLErr
    If frmReport.GridRpt.TextMatrix(0, 1) = vbNullString Then
        MsgBox "Zero records to export", vbCritical
    Else
        On Error GoTo myErr
        RunExport = True
        TotalRegs = frmReport.GridRpt.Rows
        Load frmExport
        frmExport.prBar.Min = 0
        frmExport.prBar.Max = TotalRegs
        frmExport.Show , frmReport
        
        Dim appExcel As Variant
        Dim MyStr As String
        
        Set appExcel = CreateObject("Excel.application")
        
        ExcelVersion = appExcel.Version
        frmExport.Label1.Caption = "Excel Version " & ExcelVersion
        
        appExcel.Visible = False
        appExcel.Workbooks.Add
        
        
        'Set wksNew = appExcel.Worksheets("Sheet1")
     
        appExcel.Visible = False
     
    ' The first thing I do to the worksheet is to set the font.
    ' Not all are required, but I included them as examples.
     With appExcel
        .Cells.Font.Name = "Arial"
        .Cells.Font.Size = 8
        '.Cells.Font.Strikethrough = False
        '.Cells.Font.Superscript = False
        '.Cells.Font.Subscript = False
        '.Cells.Font.OutlineFont = False
        '.Cells.Font.Shadow = False
        '.Cells.Font.Underline = 2   'xlUnderlineStyleNone
        '.Cells.Font.ColorIndex = xlAutomatic
        .Cells.NumberFormat = "@"                                   'all set to Text Fields
        ' My first row will contain column names, so I want to freeze it
        .Rows("2:2").Select
        .ActiveWindow.FreezePanes = True
     
        ' ... and I want the header row to be bold
        .Rows("1:1").Font.Bold = True
        .Rows("1:1").Font.ColorIndex = 1
        .Rows("1:1").Interior.ColorIndex = 15
        
    '    For C = 1 To 20
    '        'appExcel.Cells(1, C).Font.Bold = True
    '        appExcel.Cells(1, C).Font.ColorIndex = 1
    '        appExcel.Cells(1, C).Interior.ColorIndex = 15
    '    Next C
        ' ... and I want it to print on every page when in Print Preview
        .ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
        .ActiveSheet.PageSetup.PrintTitleColumns = ""
    
        ' Here, we set more Print Setup properties
        '.ActiveSheet.PageSetup.LeftHeader.Font.Size = 12
        .ActiveSheet.PageSetup.LeftHeader = CompanyName  'sHeader ' Custom header (name of SQL Query)
        '.ActiveSheet.PageSetup.CentreHeader.Font.Size = 20
        .ActiveSheet.PageSetup.CenterHeader = ReportName
        .ActiveSheet.PageSetup.RightHeader = "Date: " & "&D" & "  " & Format(Time, "hh:mm") & "   Page: " & "&P of &N" & "   OPID:" & OperatorID '"RRRRR"
        
        
        
        
        
        
    '    .ActiveSheet.PageSetup.LeftFooter = "Global Auto Parts"  '"&F" & vbCr  ' File name and a hard return
    '    .ActiveSheet.PageSetup.CenterFooter = "&D"  ' Today's Date
    '    .ActiveSheet.PageSetup.RightFooter = "&P of &N"  ' Page x of y
        
        
        
        
     
    '        .ActiveSheet.PageSetup.LeftHeader = ""
    '        .ActiveSheet.PageSetup.CenterHeader = ""
    '        .ActiveSheet.PageSetup.RightHeader = ""
    '        .ActiveSheet.PageSetup.LeftFooter = ""
    '        .ActiveSheet.PageSetup.CenterFooter = ""
    '        .ActiveSheet.PageSetup.RightFooter = ""
    
        .ActiveSheet.PageSetup.LeftMargin = appExcel.Application.InchesToPoints(0.75)
        .ActiveSheet.PageSetup.RightMargin = appExcel.Application.InchesToPoints(0.75)
        .ActiveSheet.PageSetup.TopMargin = appExcel.Application.InchesToPoints(1)
        .ActiveSheet.PageSetup.BottomMargin = appExcel.Application.InchesToPoints(0.5)
        .ActiveSheet.PageSetup.HeaderMargin = appExcel.Application.InchesToPoints(0.5)
        .ActiveSheet.PageSetup.FooterMargin = appExcel.Application.InchesToPoints(0)
        
    '    .ActiveSheet.PageSetup.LeftMargin = appExcel.Application.InchesToPoints(0.75)
    '    .ActiveSheet.PageSetup.RightMargin = appExcel.Application.InchesToPoints(0.75)
    '    .ActiveSheet.PageSetup.TopMargin = appExcel.Application.InchesToPoints(1)
    '    .ActiveSheet.PageSetup.BottomMargin = appExcel.Application.InchesToPoints(1)
    '    .ActiveSheet.PageSetup.HeaderMargin = appExcel.Application.InchesToPoints(0.5)
    '    .ActiveSheet.PageSetup.FooterMargin = appExcel.Application.InchesToPoints(0.5)
    
        
        
        
        
        .ActiveSheet.PageSetup.PrintHeadings = False
        .ActiveSheet.PageSetup.PrintGridlines = True  'False
        
        
        
        '.ActiveSheet.PageSetup.PrintComments = xlPrintNoComments
        '.ActiveSheet.PageSetup.PrintQuality = 600
        .ActiveSheet.PageSetup.CenterHorizontally = False
        .ActiveSheet.PageSetup.CenterVertically = False
        .ActiveSheet.PageSetup.Orientation = 2         'LANDSCAPE  xlPortrait
        .ActiveSheet.PageSetup.Draft = False
        .ActiveSheet.PageSetup.FirstPageNumber = 1       'xlAutomatic
        '.ActiveSheet.PageSetup.Order = xlDownThenOver
        .ActiveSheet.PageSetup.BlackAndWhite = False
        .ActiveSheet.PageSetup.Zoom = 80  ' Reduce to 80% when printing
     
    End With
    Now output the rows to excel

    Code:
    appExcel.Cells(XLRow, C) = Trim(MyStr)   ' where XLROW = the Excel Row and C = the Excel Column
    FINALLY - Autofit the data to the column widths required

    ' Autofit column headers (NOT COLUMN 1 - IT CONTAINS REPORT HEADING)

    Code:
    For C = 1 To frmReport.GridRpt.Cols - 1
            appExcel.Columns(C).AutoFit
        Next C
        
        
        For xlCol = 1 To 100
            If FTotal(xlCol) = "Yes" Then
                ColRange = Trim(Chr$(xlCol + 65)) & ":" & Trim(Chr$(xlCol + 65))
                appExcel.ActiveSheet.Range(ColRange).HorizontalAlignment = "4"
            End If
            If FTotal(xlCol) = "" Then GoTo EndHA
        Next xlCol


    That should get you started !


    There are a number of dedicated websites to Excel

    eg, http://www.codeforexcelandoutlook.co...nt-properties/

    Also try searching Google - quite a lot of references

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,961

    Re: Set Excel Document Properties Programmatically

    This works in a few seconds, with a big worksheet, that beeps when it's done.

    The form blinks, and that's it!

    Code:
    Option Explicit
    
    Public xlAppTemp As Excel.Application
    Public xlWorkBook As Excel.Workbook
    Public xlSheet As Excel.Worksheet
    Dim strDate$
    
    Public Sub GenerateReport()
      On Error GoTo ErrHandler
      
      ' Creating Object for Excel File.....
      Set xlAppTemp = New Excel.Application
      
      ' Making it Invisible and non-Interactive.....
      xlAppTemp.Visible = False
      xlAppTemp.DisplayAlerts = False
        ' Opening Template Excel File.....
      Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
      Set xlSheet = xlWorkBook.Sheets(1)
    
      ' Making Active to Worksheet 1.....
      xlSheet.Activate
    
      ' I am doing lot of things in it, but to provide you with example
      xlSheet.Cells(15, 1) = "This is my report 1"
    
      ' Formating Date to attach with new file name.....
      strDate = Format(Date, "yyyy-mm-dd")
      
      ' Saving excel file with new name on different folder.....
      xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"
    
    Cleanup:
      ' Destroying Objects.....
      Set xlSheet = Nothing
      xlWorkBook.Close SaveChanges:=False
      Set xlWorkBook = Nothing
    'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
      xlAppTemp.Visible = True
      xlAppTemp.DisplayAlerts = True
      xlAppTemp.Quit
      Set xlAppTemp = Nothing
      Exit Sub
    ErrHandler:
    'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
    '(save happens just above if no error occurs)
      xlWorkBook.Close SaveChanges:=False
      Set xlWorkBook = Nothing
    
    'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
      xlAppTemp.Visible = True
      xlAppTemp.DisplayAlerts = True
    
      xlAppTemp.Quit
      Set xlAppTemp = Nothing
    End Sub
    
    Private Sub Command1_Click()
      Call GenerateReport
      Beep
    End Sub
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Sep 2008
    Posts
    68

    Re: Set Excel Document Properties Programmatically

    I was trying to change the properties when you right click on the file and go to summary and the Subject, author fields come up. I know for MS word they have builtindocumentproperties and enumerated constants for each field. I wanted to do the same for excel.

  6. #6
    Join Date
    Sep 2008
    Posts
    68

    Re: Set Excel Document Properties Programmatically

    Right now I have

    If useTitleCheckbox.Checked Then
    If useDocNameCheckBox.Checked Then
    Dim title As String = xls.BuiltinDocumentProperties("Title").Value = Path.GetFileNameWithoutExtension(xls.Name)
    Else
    Dim title As String = xls.BuiltinDocumentProperties("Title").Value = titleTextBox.Text
    End If
    End If

    But when I look at the properties for the excel file the feild is blank.

  7. #7
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,583

    Wink Re: Set Excel Document Properties Programmatically

    Code:
     
    Dim title As String = xls.BuiltinDocumentProperties("Title").Value = titleTextBox.Text
    It is not clear from the code snippet you posted, but I assume your variable xls is an Excel.Workbook object.

    What you do there is certainly not what you intended to do: You compare the value of the document property "Title" to the contents of your titleTextBox (for equality). The boolean result of the comparison is then somehow converted to a string (don't really know how VB does that, but it doesn't matter here either) and finally assigned to your variable text.

    It is not like the way C/C++ does it, where the operator = is always an assignment whose result is the assigned value.

    That way you don't assign anything to the document property at all, so it's no surprise that it isn't changed. I only tried it from within Excel, but there assigning to the document property works like a charm.

    Ah, and... what about these code tags?

    HTH
    Last edited by Eri523; July 9th, 2010 at 07:37 PM.

  8. #8
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,961

    Re: Set Excel Document Properties Programmatically

    Code:
    Dim title As String 
    
      IF xls.BuiltinDocumentProperties("Title").Value = titleTextBox.Text THEN
    ' DO SOMETHING
      END IF

    Better to put in a FUNCTION that returns True/False
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  9. #9
    Join Date
    Sep 2008
    Posts
    68

    Re: Set Excel Document Properties Programmatically

    Thanks I got it to work yesterday. Thank you all for your help

  10. #10
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,090

    Re: Set Excel Document Properties Programmatically

    Please mark your thread if you feel your question has been answered to your satisfaction. You could do this by clicking Thread tools above your first post, then clicking Mark Thread Resolved.

    Thanx

    Hannes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center