• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1855
  • Last Modified:

Creating Excel.XlFileFormat as a proper Object MS Office Automation

Below are two code snippets. The first part works fine when including the Excel library in my VS project.
The problem is I have multiple versions of MS Office in use so I have to use the second method by GetObject or CreateObject to use whatever version the user has installed.
I'm wondering how i can properly create the object for the XlFileFormat.
The second code snippet fails when creating the object. Not sure how to work around this as the error states cannot create ActiveX control. Thanks in advance.


Snippet A - Works
 
Dim objExcelApp As New Excel.Application
        Dim objWorksheet As New Excel.Worksheet
        Dim objXFileFormat As New Excel.XlFileFormat
 
        With objExcelApp
            .Visible = True
            .Workbooks.Add(PATH)
            objWorksheet = .Worksheets(.ActiveSheet.Name)
            objWorksheet.SaveAs("c:\testxls", objXFileFormat.xlExcel9795)
        End With
 
Snippet B - Not Working
 
     Dim objExcelApp As Object
        Dim objWorksheet As Object
        Dim objXFileFormat As Object
 
        Try
            objExcelApp = GetObject("Excel.Application")
            objXFileFormat = GetObject("Excel.XlFileFormat")
        Catch e As Exception
            objExcelApp = CreateObject("Excel.Application")
''Fails Here with a cannot create ActiveX Error
            objXFileFormat = CreateObject("Excel.XlFileFormat")
        End Try
 
       With objExcelApp
            .Visible = True
            .Workbooks.Add(PATH)
            objWorksheet = .Worksheets(.ActiveSheet.Name)
            objWorksheet.SaveAs("c:\testxls", objXFileFormat.xlExcel9795)
        End With

Open in new window

0
cbish21577
Asked:
cbish21577
1 Solution
 
PaulHewsCommented:
You can look up constant definition in the online help, or using a source like: http://nerds-central.blogspot.com/2007/02/excel-constant-definitions-for-vbscript.html


Const xlExcel9795 As Integer = &H2B      
objWorksheet.SaveAs("c:\test.xls", xlExcel9795)
0
 
cbish21577Author Commented:
Thanks Paul, Worked Perfectly!
    Dim objExcelApp As Object
            objExcelApp = CreateObject("Excel.Application")
           Const xlExcel9795 As Integer = &H2B

        With objExcelApp
            .Visible = True
            .Workbooks.Add(PATH)
            objWorksheet = .Worksheets(.ActiveSheet.Name)
            objWorksheet.SaveAs("c:\testxls", xlExcel9795)
        End With
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now