I have a MS Access VBA process that copies many worksheets from multiple XLSM workbooks into a displayed XLSX workbook but that workbook must be saved as XLS. There are additional steps to insert page numbers and to rename the tabs.
The first time, the workbook saves/overwrites quickly onto the network; however, when performing this a second time, errors appear when performing the additional steps. It appears that the workbook is still in the process of saving as a XLS workbook as the error message states, “The workbook you are trying to save has the same name as a current open workbook“.
I attempted to copy the xlsm workbooks to xlsx to capture the formatting and discovered it cannot be saved as xlsx so it is saved as xls.
What is the solution for this processing delay and/or can VBA code be written to save an open workbook?
Below display the copying VBA code:
Private Sub PDFWorkbook_Click()
Dim xl As New Excel.Application
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shtToCopy As Excel.Worksheet
Dim rs As Recordset
Dim SSName As String
Dim SSTab As String
Dim SSTabOut As String
Dim shtName As String
Set xl = CreateObject("Excel.Applic
xl.Visible = True
Set rs = CurrentDb.OpenRecordset("T
Set wkbDest = xl.Workbooks.Open("S:\Fina
Do While Not (rs.EOF)
shtName = rs!SSTab
Set wkbSource = xl.Workbooks.Open(rs("SSNa
Set shtToCopy = wkbSource.Sheets(shtName)
xl.DisplayAlerts = False
xl.DisplayAlerts = True
Set wkbSource = Nothing
Set wkbDest = Nothing
MsgBox "Worksheet was copied"
Private Sub Prepare_PDF_Click()
Dim MyFullName As String
Dim xlAppFTP As Object, xlWb As Object, xlWs As Object
Set xlAppFTP = CreateObject("Excel.Applic
Set xlWb = xlAppFTP.Workbooks.Open(
MyFullName = "H:\MonthlyReport.pdf"
xlWb.ExportAsFixedFormat Type:=0, FileName:=MyFullName, _
, IgnorePrintAreas:=False, OpenAfterPublish:=False
Set xlWs = Nothing
Set xlAppFTP = Nothing
Set xlApp = Nothing
MsgBox "The PDF file was created"