Create Excel 2003 file from Acc2007/2010

[marlan]

Hi all you experts!

I am writing in Acc2003 an App I want to deploy and run in later versions aswell. this App creates Excel files, that should be read also by machines running Excel 2003.

this is the code for creating the file (Workbook):

Public Function GetExcelWorkBook(ByRef ApXL As Object, strFilePath As String) As Object
    'ApXL is of type of "Excel.Application"
    Dim xlWBk As Object
    'Get Excel file (WorkBook)
    If Not IsNull(ApXL) Then
        If FileExists(strFilePath) Then
            Set GetExcelWorkBook = ApXL.Workbooks.Open(strFilePath)
        Else
            Set GetExcelWorkBook = ApXL.Application.Workbooks.Add
            ApXL.Application.ActiveWorkbook.SaveAs strFilePath ', 56
        End If
    End If
    
End Function

The Comented out 56 in the line:

ApXL.Application.ActiveWorkbook.SaveAs strFilePath ', 56

should be a constant forcing file format to 97-2003, but it gives me a runtime eroor (in 2003. didn't test in 2007).
It runsd fine as long as I stik to 2003, but once I move to 2007, it won't give me 2003 format.

How do you do this?

I am running Office 2003 and testing Office 2007.

TIA

 

[vbaInet]

should be a constant forcing file format to 97-2003, but it gives me a runtime eroor (in 2003. didn't test in 2007).
It runsd fine as long as I stik to 2003, but once I move to 2007, it won't give me 2003 format.

These are two conflicting statements. Did you test it in Access 2007 or not?

Also, you're asking the question in the wrong section of the forum. This part of the forum is specifically for MS Access.

 

[marlan]

Hi vbaInet, and thanks for your reply!

The 56 is comented out, that's when it runs fine as long as bowth Acc and Excel stick to 2003.

As for the forum section, this code is writen in Access, I would like help from an Access developer point of view.

I found this 56 constant here, but it seems like it is known in 2007, not in 2003.

I have thought of an other method: using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SomeDummyTable",FileName & ".xls"

to just create the file, taking advantage of the acSpreadsheetTypeExcel9 constant for file format, and modifying it usin the above apxl.

but there must be a more eficient way?...

 

[vbaInet]

Why don't you follow the code example in the thread?

Here's your amended code:

Public Function GetExcelWorkBook(strFilePath As String) As Object
    'ApXL is of type of "Excel.Application"
    Dim xlWBk As Object
    'Get Excel file (WorkBook)
    If [COLOR=Red]Not [/COLOR]ApXL [COLOR=Red]Is Nothing[/COLOR] Then
        If FileExists(strFilePath) Then
            Set GetExcelWorkBook = ApXL.Workbooks.Open(strFilePath)
        Else
            Set GetExcelWorkBook = ApXL.Application.Workbooks.Add
            [COLOR=Red]GetExcelWorkBook[/COLOR].SaveAs strFilePath[COLOR=Red], 56[/COLOR]
        End If
    End If
    
End Function

The bits in red were changed.

 

[vbaInet]

I've just looked at the site and there's a sample code titled "Save one 2007-2010 worksheet as Excel 97-2003 workbook" which I would imagine does what you're attempting.

 

[marlan]

Hi, And thank you for the Code review, It does look better, and works... except for the 56 constant...

But this code section looks like fine, and runs in 2003, I just can't test it in 2007 right now...

 'Remember the users setting
    Dim SaveFormat as Long
    SaveFormat = Application.DefaultSaveFormat
    'Set it to the 97-2003 file format
    Application.DefaultSaveFormat = 56

If it works, I will have to return DefaultSaveFormat when done. A singleton should be a good way to manage that, can you please post singleton sample in VBA? I couldn't find one in this forum.

Edit: How do you return a ByRef variable in VBA?
Dose a Class in VBA have a distructor?

Thanks again!

 

[vbaInet]

I've just tested the code in an Access 2007 db and it saves the workbook as intended.

What do you mean by a singleton approach?

You can pass a variable by ref by using the ByRef keyword in the argument. You pass a variable via a function.

A classes destructor method is a function you create called Terminate.

Private Sub Class_Terminate()

End Sub

 

[marlan]

Can you have the return value of a function be a reference?

 

[vbaInet]

What do you mean? The actual memory location or a ByRef value?

 

[marlan]

Hi vbaInet, and Thank you!

Say this is my module:

Option Compare Database
Option Explicit

Static SingleExcelObect As Object

Public Static Property Get ExcelObject() As Object
    If SingleExcelObect Is Nothing Then SingleExcelObect = GetExcelObject
    set ExcelObject = SingleExcelObect
End Property

Does Property Get ExcelObject return a copy of the reference to SingleExcelObect, or a clone of the object with a reference to it, or otherwise...

An other question: What do I have to do so that this runs in a class, so that the destruter:

Private Sub Class_Terminate()
    If Not SingleExcelObect Is Nothing Then SingleExcelObect.Quit
    Set SingleExcelObect = Nothing
End Sub

runs, comming time?

Thanks a milion for repliying!

 

[vbaInet]

It's a pointer.

A lot of your code is incorrect. I've already mentioned that for objects you need Set. Let is implied if you don't use any keyword.

Your questions are starting to deviate from the original post. If you have anymore questions outside "Create Excel 2003 file from Acc2007/2010" please post a new thread.

 

[marlan]

Thank you, I will

 

[marlan]

This is what the class code should look like:

Option Compare Database
Option Explicit

Static SingleExcelObect As Object
Static SaveFormat As Long
    
Public Static Property Get ExcelObject() As Object
    If SingleExcelObect Is Nothing Then
        Set SingleExcelObect = GetExcelObject
        'Remember the users setting
        SaveFormat = SingleExcelObect.DefaultSaveFormat
        'Set it to the 97-2003 file format
        SingleExcelObect.DefaultSaveFormat = 56
    End If
    Set ExcelObject = SingleExcelObect
End Property


Private Sub Class_Terminate()
    If Not SingleExcelObect Is Nothing Then
        SingleExcelObect.DefaultSaveFormat = SaveFormat
        SingleExcelObect.Quit
    End If
    Set SingleExcelObect = Nothing
End Sub

What do I have to do so that this runs in a class, so that the destruter Class_Terminate() runs, comming time?

 

[vbaInet]

I did also mention that the destructor class is optional. Access handles the garbage collection. So all that clean you're doing in that class is irrelevant.

 

[marlan]

The Excel.exe process still runs after I finish using the Excel.Application object, and could prevent me from using it again for a while.

 

[vbaInet]

Alright, you can destroy the class object or make it go out of scope. Once that happens the Terminate sub will automatically be called.

But, where are you using ExcelObject?

 

[marlan]

I want to create up to 600-700 files in a loop, therefore must use one Excel.Application, and keep it alive so that I don't get Errors, and then release it, when done.

this is your modified code from the first post:

Public Function GetExcelWorkBook(ByRef ApXL As Object, strFilePath As String) As Object
    'ApXL is of type of "Excel.Application"
    Dim xlWBk As Object
    'Get Excel file (WorkBook)
    If Not ApXL Is Nothing Then
        If FileExists(strFilePath) Then
            Set GetExcelWorkBook = ApXL.Workbooks.Open(strFilePath)
        Else
            Set GetExcelWorkBook = ApXL.Application.Workbooks.Add
            GetExcelWorkBook.SaveAs strFilePath ', 56
        End If
    End If
End Function

I would like to avoid passing ApXl as a param, rather have a function with workbook (file name) and a workSheet name as params, and have the class take care of the ApXl. Would a Class be the right way to do it?

 

[marlan]

I've just tested the code in an Access 2007 db and it saves the workbook as intended.

I've Just tested it in 2007 and it doesn't work

This is My code:

Public Function GetExcelObject() As Object
    On Error GoTo GetExcelObject_Err
    
        Set GetExcelObject = GetObject(, "Excel.Application")
    
GetExcelObject_Exit:
    If Not GetExcelObject Is Nothing Then
         'Remember the users setting
        SaveFormat = GetExcelObject.DefaultSaveFormat
        'Set it to the 97-2003 file format
        GetExcelObject.DefaultSaveFormat = 56
    End If
    
    Exit Function
    
GetExcelObject_Err:
    On Error GoTo 0
    Set GetExcelObject = CreateObject("Excel.Application")
    Resume GetExcelObject_Exit
End Function

In Excel 2003 I get gibrish, and in Excel 2007 it offers to open the file, even though the file extention (.xls) differs from the file format, and opens correctly.

 

[marlan]

Teseted again, creating the file (Workbook) with this code:

Public Function GetExcelWorkBook(ByRef ApXL As Object, strFilePath As String) As Object
    'ApXL is of type of "Excel.Application"
    Dim xlWBk As Object
    'Get Excel file (WorkBook)
    If Not ApXL Is Nothing Then
        If FileExists(strFilePath) Then
            Set GetExcelWorkBook = ApXL.Workbooks.Open(strFilePath)
        Else
            Set GetExcelWorkBook = ApXL.Application.Workbooks.Add
            GetExcelWorkBook.SaveAs strFilePath[U][B], FileFormat:=56[/B][/U]
        End If
    End If    
End Function

It runs as expected in 2007, Errors in 2003... What can I do?

 

[vbaInet]

Well, I don't know why you were passing the Excel object in the first place. The name of your function is GetExcelWorkBook and that sounds to me you want it to return a reference to the workbook, existing or new.

I will give you four pointers:

1. Check the version of the Excel app and don't include the FileFormat if it's 2003. Include if it isn't.
2. The Workbook will not return a reference to the recently created workbook so you need to Open it after creating it.
3. strFilePath in the SaveAs code line should be a file path that excludes the ".xlsx" extension and includes the ".xls" extension.
4. Close the workbook after you finish working on it. xlWbk.Close If you do so the instance will not continue hanging.

Work on these and let me know if you need a hand.