Save Excel Workbook from Access Without VBA

[koolwaters]

Hi!

I was not certain if I should use the Access or Excel forum for this post but I chose Access.

I have an Access database where data is exported on a weekly basis into three worksheets. The data is exported to tables in the Excel workbook. The tables are then used to generate a number of PivotTables. I opted to use Tables as the data source to make my PivotTables dynamic as new data is continuously being added to the database. Once the data is exported, I also have code in Access to refresh all of the PivotTables in the workbook. This process works perfectly.

On closing the Excel workbook,I have code which deletes the records from the tables on the three worksheets to ensure that when the data is next exported, no existing records are there. To this end, I have code in the Before Close event of the workbook to delete the table records.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo DataErr

    Worksheets("New Applications").Activate
    Range("Pending").Select
    Application.DisplayAlerts = False
    Selection.Delete
    Application.DisplayAlerts = True
    Range("A2").Select
    
    Worksheets("Disbursements").Activate
    Range("Disbursements").Select
    Application.DisplayAlerts = False
    Selection.Delete
    Application.DisplayAlerts = True
    Range("A2").Select
    
    Worksheets("Outstanding").Activate
    Range("Outstanding").Select
    Application.DisplayAlerts = False
    Selection.Delete
    Application.DisplayAlerts = True
    Worksheets("New Applications").Activate
    Range("A2").Select
    ThisWorkbook.Save
    
Exit Sub
DataErr_Exit:
    Exit Sub
    
DataErr:
        MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub

This is where the problem comes. I have tried to save the file as .xlsx on export, but despite the file being saved in the .xlsx format, upon closing the macro runs.

This is the code I was using in Access to save the file.

stTitle = Format(myEndDate, "mmmm") & " Analysis Report"
stSaveName = stPath2 & "\" & stTitle & ".xlsx"
XlBook.SaveAs fileName:=stSaveName, FileFormat:=xlOpenXMLWorkbook

I have done quite a bit of searching but have found nothing to assist with this issue.

I have tried copying the sheets to a new workbook after the template is opened and then saving the file but the file does not open. I get the message "Run-time error '1004': This extension cannot be used with the selected file type...

This is the code I am using in the Workbook Open event.

Private Sub Workbook_Open()
Dim NewName As String, NewPath As String
Dim ws As Worksheet

NewPath = Path

If Right$(NewPath, 1) = "\" Then
   NewPath = Left$(NewPath, Len(NewPath) - 1)
End If

NewPath = Left$(NewPath, InStrRev(NewPath, "\"))

    With Application
        .ScreenUpdating = False
 
 On Error GoTo DataErr
        Sheets(Array("New Applications", "Disbursements", "Outstanding", "Analysis")).Copy
        On Error GoTo 0
        
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
         
    Worksheets("New Applications").Activate
    Range("A4").Select
         
    NewName = "Analysis Report"
    ActiveWorkbook.SaveAs NewPath & NewName & ".xlsx"
         
        .ScreenUpdating = True
    End With
    Exit Sub
     
DataErr:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub

Any assistance is appreciated. Thanks in advance.

 

[xenou]

Hi,
You can't have macro code in an .xlsx workbook. Seems like you are trying to do something that just isn't allowed (and in general, your file extensions must match the file types to avoid confusion and possible bugs).

 

[koolwaters]

Hi xenou,

I understand that you cannot have a macro in a .xlsx workbook.

I am trying to find a way to create a new workbook based on the template without the macros. Am I understanding you to say that this cannot be done?

 

[xenou]

I'm not sure. You are saying your template has code in it, but you want to save it without code?

It might be possible (but beware that you are actively running code on the very workbook you want saved without code, which at very least implies an abrupt end to the running code).

I would prefer to use SaveCopyAs (that may not work if it doesn't allow you to pick a file type). Also you may need to disable alerts. You also cannot simply specific the extension if you are changing the file type. You must change the save as file format.

This would be a problem since presumably active workbook is the workbook with the code (just opened):

ActiveWorkbook.SaveAs NewPath & NewName & ".xlsx"

Although I would never use ActiveWorkbook in a case like this (and very rarely in any cases at all).

 

[xenou]

Note that all things about saving excel files in Excel vba are here:
Use VBA SaveAs in Excel 2007-2016

 

[koolwaters]

I think you misunderstood.

The data is exported from Access into a template. I then want to save the template as another workbook. I want to retain the code in the template. It is the new workbook that I want the code removed.

I had tired SaveCopyAs previously but that did not work either.

 

[xenou]

I'm looking at this code:

ActiveWorkbook.SaveAs NewPath & NewName & ".xlsx"

It must be the problem. The workbook contains code but you are saving it with an .xlsx extension.

 

[xenou]

Here's your problem actually:

Worksheets("New Applications").Activate

This is an unqualified reference. Guess what workbook/worksheet you are activating?

 

[xenou]

Some rewritten code for example purposes. The main thing is not to use ActiveWorkbook or any unqualified references!!!

Private Sub Workbook_Open()
Dim NewName As String, NewPath As String
Dim ws As Worksheet
Dim wbNew As Workbook

    On Error GoTo DataErr
    
    'Application.ScreenUpdating = False
    
    
    '//Copy four worksheets from template to new workbook
    Sheets(Array("New Applications", "Disbursements", "Outstanding", "Analysis")).Copy
    Set wbNew = ActiveWorkbook
    
    '//Make sure we have successfully created a copy
    If wbNew.Name = ThisWorkbook.Name Then
        MsgBox "Error copying template workbook."
        Exit Sub
    End If
    
    '//Remove all formulas and links
    For Each ws In wbNew.Worksheets
        With ws
            .Activate
            .Cells.Copy
            .Cells.PasteSpecial Paste:=xlValues
            .Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            .Cells(1, 1).Select
        End With
    Next ws
         
    '//A little housekeeping
    On Error Resume Next '//Never crash on select/activate actions
    wbNew.Worksheets("New Applications").Activate
    wbNew.Worksheets("New Applications").Range("A4").Select
    On Error GoTo DataErr
         
    '//Save new workbook
    NewName = "Analysis Report"
    NewPath = ThisWorkbook.Path & "\"
    wbNew.SaveAs NewPath & NewName & ".xlsx"
         
    'Application.ScreenUpdating = True
    
    Exit Sub
     
DataErr:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical

End Sub

I'm not sure how you are getting this workbook to open. You could run all this code from another workbook or from access so you wouldn't need code in your template (especially if the template only contains the four sheets - just save it with the new name).

 

[koolwaters]

Hi Xenou,

I was able to solve the problem by adding to the code I have in Access.

I have modified the code to add a new workbook and then copy all of the sheets from the template into the new workbook and then save the new workbook as a .xlsx file, which takes care of the problem.

    Set XlNewBook = Workbooks.Add
        stTitle = "Analysis Report " & Format(Now, "mm-dd-yyyy")
        stSaveName = stPath2 & "\" & stTitle & ".xlsx"
        XlNewBook.SaveAs fileName:=stSaveName, FileFormat:=xlOpenXMLWorkbook
        
    Set XlBook = GetObject(stPath)

    For Each XlSheet In XlBook.Sheets
        XlSheet.Copy After:=XlNewBook.Sheets(XlNewBook.Sheets.count)
    Next