-
July 7th, 2010, 01:38 PM
#1
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.
-
July 7th, 2010, 01:59 PM
#2
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
-
July 8th, 2010, 08:40 PM
#3
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
-
July 8th, 2010, 11:07 PM
#4
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
-
July 9th, 2010, 06:15 AM
#5
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.
-
July 9th, 2010, 09:11 AM
#6
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.
-
July 9th, 2010, 07:34 PM
#7
Re: Set Excel Document Properties Programmatically
Last edited by Eri523; July 9th, 2010 at 07:37 PM.
-
July 9th, 2010, 09:27 PM
#8
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
-
July 10th, 2010, 08:31 AM
#9
Re: Set Excel Document Properties Programmatically
Thanks I got it to work yesterday. Thank you all for your help
-
July 10th, 2010, 06:48 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|