none
Save a .xlsm file from excel 2003

    Question

  • Hi

    I'm trying to save an excel 2007 file (.xlsm) from within an add-in using excel 2003 as a excel 2007 file (.xlsm). So first of all i'm opening a excel 2007 file with excel 2003 (using service pack 3), via a macro, an add-in is loaded that changes some data. Finally I want to save these changes back to the original excel 2007 file.

    When opening the file, a conversion is done so excel 2003 can read the excel 2007 file format. This happens correctly. But then, when the add-in tries to save it back as a 2007-file format, again, a conversion should happen, but it doesn't.

    When saving the workbook manually, the conversion is strangely done (you get the progress bar indicating the conversion). When recording this action (the manually saving of the file), I get a macro that sais that the only thing that I've done is: "ActiveWorkbook.Save". But when using this code in my add-in, it's not correctly saved (and no conversion is done, no progress bar).
    In fact, after a long search I found that it is saved, but in the temporary directory as a file named 'CVT390.tmp'.

    Can anyone help me, or am I missing something?

    Thanks in advance

    version: Microsoft Office Excel 2003 (11.8307.8221) SP3
    Friday, October 23, 2009 12:28 PM

Answers

  • I stripped down the files to the basic actions where the problems occur.

    in my first excel 2007 document I have a macro that calls an add-in when the file is opened (I have a hidden sheet named "ApplicationData" with some data like the path to the add-in):

    Private Sub Workbook_Activate()
    Dim FilePath As String
    Dim strRefName As String
    Dim str_rRefName As String
    Dim bRefAlreadyIN As Boolean
    Dim arrRefName
    Dim arr_rRefName
    Dim rRef
    Dim Fs
    Dim vbRet
    
    Set owrkbPROCESS_REPORT = ThisWorkbook
    
        '''''''''''''''''''''''''''''''''''
        'add the referenced add-in (*.XLA)'
        '''''''''''''''''''''''''''''''''''

    'get the path and name of the add-in from the applicationdata

    arrRefName = Split(VBA.Trim(ThisWorkbook.Sheets("ApplicationData").Range("AddInFullPath").Text), "\") strRefName = arrRefName(UBound(arrRefName))




    'check all references and remove all but the one to the add-in
    If ThisWorkbook.VBProject.references.Count > 0 Then For Each rRef In ThisWorkbook.VBProject.references arr_rRefName = Split(VBA.Trim(rRef.fullpath), "\") str_rRefName = arr_rRefName(UBound(arr_rRefName)) If str_rRefName = strRefName Then ThisWorkbook.VBProject.references.Remove rRef bRefAlreadyIN = False Exit For End If bRefAlreadyIN = False Next Else bRefAlreadyIN = False End If
    'if there was no reference to the add-in, add it
    If Not bRefAlreadyIN Then Set Fs = CreateObject("Scripting.FileSystemObject") FilePath = VBA.Trim(ThisWorkbook.Sheets("ApplicationData").Range("AddInFullPath").Text) If Fs.FileExists(FilePath) Then Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.VBProject.references.addfromfile FilePath On Error GoTo 0 Application.DisplayAlerts = True Else FilePath = VBA.Trim(ThisWorkbook.Sheets("ApplicationData").Range("AddInLocally").Text) If Fs.FileExists(FilePath) Then Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.VBProject.references.addfromfile FilePath On Error GoTo 0 Application.DisplayAlerts = True Else vbRet = MsgBox("Error loading the add-in") ThisWorkbook.Close Exit Sub End If End If End If End Sub



    The add-in (also a excel 2007 add-in) then consist of some code, that edits the previous file and tries to save it, for example:

    Private Sub Workbook_Open()
    
    MsgBox "Before editing"
    
    Call Edit
    
    MsgBox "After editing, before saving"
    
    Call Save
    
    MsgBox "After saving"
    
    End Sub
    
    
    
    Private Function Edit()
    
    ActiveWorkbook.Sheets("ApplicationData").Cells(1,1).Value = "Test"
    
    End Function
    
    
    
    Private Function Save()
    
    ActiveWorkbook.Save
    
    End Function



    • Marked as answer by Tim Li Friday, October 30, 2009 6:49 AM
    Tuesday, October 27, 2009 12:34 PM

All replies

  • Hi,

    I get code like this in xl2003 when saving as xlsx format.

        ActiveWorkbook.SaveAs Filename:="C:\Book2.xlsx", FileFormat:=51, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
            CreateBackup:=False
    Once saved the Activeworkbook.Save method performs the conversion as expected.
    Cheers www.andypope.info
    Friday, October 23, 2009 1:09 PM
  • First of all, I am using a macro enabled format, '.xlsm', could this make any difference?

    And second of all, I have to save it as the same original document, so I think I can't use the SaveAs because then I would have to override my active workbook which is still in use...
    Friday, October 23, 2009 2:38 PM
  •     ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.fullname, FileFormat:=52, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    The activeworkbook should not be your addin, that is Thisworkbook.

    Cheers www.andypope.info
    Friday, October 23, 2009 3:15 PM
  • I've tried that also before, but it seems (I've checked it now) that after the conversion from the office 2007 file (.xlsm) when opening the file with excel 2003, the activeworkbook is some temporary workbook which then has the name CVT390.tmp in my case. And when you try to save it as the original file (with the original filename and -path) with the SaveAs, he complains that there are access problems (I guess because the file is actually in use).
    Friday, October 23, 2009 4:04 PM
  • I don't think the problem is the syntax of the VBA, which for me works, but the temp files that are being created for some reason.
    Have you tried clearing out your pc's temporary folders.

    This code works as expected using either Save or SaveAs methods.

    Sub Macro1()
    '
        Dim wbkTest As Workbook
        
        Set wbkTest = Workbooks.Open("C:\Temp\test.xlsm")
        wbkTest.Worksheets(1).Range("A1").Value = "Test"
        wbkTest.Save
        
        Application.DisplayAlerts = False
        wbkTest.SaveAs Filename:=wbkTest.FullName, FileFormat:=52, _
                       Password:="", WriteResPassword:="", _
                       ReadOnlyRecommended:=False, CreateBackup:=False
        
        Application.DisplayAlerts = True
        
    End Sub
    

    Cheers www.andypope.info
    Saturday, October 24, 2009 10:38 AM
  • Yes, but I'm starting from an excel 2007-file (.xlsm), this one is converted when opening and a temporary file is created. Afterwards, when the Workbook_Activate() module of this excel-file is executed, an add-in is added to the references (ThisWorkbook.VBProject.references.addfromfile FilePathOfAddIn). This add-in is executed when it is loaded and from this add-in, the save action needs to be triggered on the original document.

    So the problem is that after opening the original excel 2007-file, I am working actually with the temporary file and ThisWorkbook will also reference to the temporary file. In your code, the saving of the file is triggered from the original file, while in my code, the saving is triggered from within the add-in.
    Monday, October 26, 2009 3:09 PM
  • Actually my code example was in a xl2003 file. The code opens the .xlsm file, changes a value in A1 on the first sheet and then save and saveas.
    At no time did I get a temporary version of the xl2007 document.

    Are you able to test your code on another pc and if so do you get the same problem?

    If you want to send your add-in I will take a look.
    andy AT andypope DOT info


    Cheers www.andypope.info
    Monday, October 26, 2009 3:33 PM
  • I stripped down the files to the basic actions where the problems occur.

    in my first excel 2007 document I have a macro that calls an add-in when the file is opened (I have a hidden sheet named "ApplicationData" with some data like the path to the add-in):

    Private Sub Workbook_Activate()
    Dim FilePath As String
    Dim strRefName As String
    Dim str_rRefName As String
    Dim bRefAlreadyIN As Boolean
    Dim arrRefName
    Dim arr_rRefName
    Dim rRef
    Dim Fs
    Dim vbRet
    
    Set owrkbPROCESS_REPORT = ThisWorkbook
    
        '''''''''''''''''''''''''''''''''''
        'add the referenced add-in (*.XLA)'
        '''''''''''''''''''''''''''''''''''

    'get the path and name of the add-in from the applicationdata

    arrRefName = Split(VBA.Trim(ThisWorkbook.Sheets("ApplicationData").Range("AddInFullPath").Text), "\") strRefName = arrRefName(UBound(arrRefName))




    'check all references and remove all but the one to the add-in
    If ThisWorkbook.VBProject.references.Count > 0 Then For Each rRef In ThisWorkbook.VBProject.references arr_rRefName = Split(VBA.Trim(rRef.fullpath), "\") str_rRefName = arr_rRefName(UBound(arr_rRefName)) If str_rRefName = strRefName Then ThisWorkbook.VBProject.references.Remove rRef bRefAlreadyIN = False Exit For End If bRefAlreadyIN = False Next Else bRefAlreadyIN = False End If
    'if there was no reference to the add-in, add it
    If Not bRefAlreadyIN Then Set Fs = CreateObject("Scripting.FileSystemObject") FilePath = VBA.Trim(ThisWorkbook.Sheets("ApplicationData").Range("AddInFullPath").Text) If Fs.FileExists(FilePath) Then Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.VBProject.references.addfromfile FilePath On Error GoTo 0 Application.DisplayAlerts = True Else FilePath = VBA.Trim(ThisWorkbook.Sheets("ApplicationData").Range("AddInLocally").Text) If Fs.FileExists(FilePath) Then Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.VBProject.references.addfromfile FilePath On Error GoTo 0 Application.DisplayAlerts = True Else vbRet = MsgBox("Error loading the add-in") ThisWorkbook.Close Exit Sub End If End If End If End Sub



    The add-in (also a excel 2007 add-in) then consist of some code, that edits the previous file and tries to save it, for example:

    Private Sub Workbook_Open()
    
    MsgBox "Before editing"
    
    Call Edit
    
    MsgBox "After editing, before saving"
    
    Call Save
    
    MsgBox "After saving"
    
    End Sub
    
    
    
    Private Function Edit()
    
    ActiveWorkbook.Sheets("ApplicationData").Cells(1,1).Value = "Test"
    
    End Function
    
    
    
    Private Function Save()
    
    ActiveWorkbook.Save
    
    End Function



    • Marked as answer by Tim Li Friday, October 30, 2009 6:49 AM
    Tuesday, October 27, 2009 12:34 PM