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.

 

[Norie]

Why not use an actual template workbook in the first place?

 

[koolwaters]

I am using a template workbook to export the data from Access, however, the template contains macros and I do not want the end file to contain macros. That is the reason for copying the sheets to a new workbook.

 

[Norie]

What I meant was, why not export the data from Access to a template workbook?

So you would have a workbook with the code, and another, code-free, workbook based on the template.

 

[koolwaters]

That is exactly what I have.

The code exports the data into the template workbook.

It then creates a new workbook, copies the sheets from the template workbook and saves the new workbook.

 

[xenou]

You can use the template workbook directly, rather than copying sheets from it ... I think ... just SaveAs ... as long as the code is running from somewhere not inside the template workbook. Either way should work fine though.

I would actually prefer that though to anything involving GetObject() which I have never trusted to be reliable (it doesn't seem to work as advertised).

 

[koolwaters]

Thanks xenou. I have replaced

Set XlBook = GetObject(stPath)

with

XlBook.Activate

 

[james_lankford]

just don't delete the tables from the workbook

problem solved

next time you export, rebuild the tables and refresh the pivots

 

[koolwaters]

I have run into a snag with my automation. To recap, I am exporting data from Access into an Excel template. This data is exported to the Source Data sheet and then used to populate PivotTables and PivotCharts on 3 other sheets. On closing the template in Excel, I have code that deletes the records from the source data sheet. I needed the completed file to be a .xlsx file, so I copied the 4 sheets into a new workbook. This works fine the first time the export is done. The template is updated, the new workbook is created and saved and the sheets are copied. However, after the first export, unless I close Access, the workbook is created but does not open in Excel. I have to browse explorer to find and open the file. Additionally, Excel continues to run in the task manager until I close the database.

What I would like to happen is for the template to close after the new workbook is created, the new workbook remains opened and the instance of Excel created is closed. I have been trying different scenarios since yesterday to no avail.

Public Sub OtherIncomeReport()
Dim cnn As ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim MySQL As String, stPath As String, stPath2 As String, stTitle As String, stSaveName As String
Dim Xl As Object, XlBook As Object, XlNewBook As Object, XlSheet As Object, XLPivotTable As Object
Dim db As DAO.Database
Set cnn = CurrentProject.Connection
Dim myStartDate As Date, myEndDate As Date
On Error Resume Next

myStartDate = Me.StartDate
myEndDate = Me.EndDate

MyRecordset.ActiveConnection = cnn

    If Not IsNothing(Me.StartDate) Then
        If Not IsDate(Me.StartDate) Then
            MsgBox "You must enter a valid 'From' date.", vbExclamation, gstrAppTitle
            Me.StartDate.SetFocus
            Exit Sub
        End If
    End If
    If Not IsNothing(Me.EndDate) Then
        If Not IsDate(Me.EndDate) Then
            MsgBox "You must enter a valid 'To' date.", vbExclamation, gstrAppTitle
            Me.EndDate.SetFocus
            Exit Sub
        End If

        If Not IsNothing(Me.StartDate) Then
            If Me.EndDate < Me.StartDate Then
                MsgBox "'To' Date must not be earlier than 'From' Date.", _
                    vbExclamation, gstrAppTitle
                Me.EndDate.SetFocus
                Exit Sub
            End If
        End If
    End If

    DoCmd.SetWarnings False
        MySQL = "SELECT * FROM qrptAnalysisReport " & _
        "WHERE DateOfRev between #" & myStartDate & "# and #" & myEndDate & "#;"
    MyRecordset.Open MySQL

stPath = GetFEPath & "\Excel Reports\Templates\Analysis Report.xlsm"
stPath2 = GetFEPath & "Excel Reports"

Set Xl = CreateObject("Excel.Application")
Set XlBook = Xl.Workbooks.Open(stPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets("Source Data")
XlSheet.Range("A2").CopyFromRecordset MyRecordset

For Each XlSheet In Xl.Worksheets
    For Each XLPivotTable In XlSheet.PivotTables
        XLPivotTable.PivotCache.BackgroundQuery = True
        XLPivotTable.RefreshTable
    Next XLPivotTable
Next XlSheet
   
    Set XlNewBook = Workbooks.Add
        stTitle = "Analysis Report " & Format(Now, "mm-dd-yyyy")
        stSaveName = stPath2 & "\" & stTitle & ".xlsx"
        XlNewBook.SaveAs fileName:=stSaveName, FileFormat:=xlOpenXMLWorkbook
        
    XlBook.Activate

    For Each XlSheet In XlBook.Sheets
        XlSheet.Copy After:=XlNewBook.Sheets(XlNewBook.Sheets.count)
    Next
    
    Excel.Application.DisplayAlerts = False
        XlNewBook.Worksheets("Sheet1").Delete
    Excel.Application.DisplayAlerts = True
    
    XlNewBook.Worksheets("Source Data").Activate
    
    XlNewBook.Save
   ' XlBook.Save
    'XlBook.Close
    
MyRecordset.Close
MyRecordset.Close
Set cnn = Nothing
Set XlSheet = Nothing
Set XLPivotTable = Nothing
Set XlBook = Nothing
Set XlNewBook = Nothing
Set Xl = Nothing
Xl.Quit
DoCmd.SetWarnings True
End Sub

 

[xenou]

the new workbook remains opened and the instance of Excel created is closed.

This doesn't quite make sense because you have to have an instance of Excel if you want to have an open workbook.

Why do you have code in your template? I thought you weren't going to do that.

In my opinion just keep it simple - Open the workbook. Refresh the data. Save and close the workbook. Quit the Excel application. Done.

 

[koolwaters]

Thanks for the suggestions.

I deleted the code I had in the Excel template workbook, which was deleting the records from table and saved it as a .xlsx workbook.

In Access, I use save the template appending the month to the file name and added the code in Access to clear the tables. Everything works perfectly now. Thanks for your suggestions.

I have added the code below to clear the tables.

Set XlSheet = XlBook.Worksheets("Source Data")
    With XlSheet.ListObjects("PendingList")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With

Thanks for the assistance. Much appreciated.