• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

Copy many XLSM worksheets into a displayed XLSX workbook but save as xls

Hello Experts,

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.Application")
xl.Visible = True
Set rs = CurrentDb.OpenRecordset("TblReports")

rs.MoveFirst

Set wkbDest = xl.Workbooks.Open("S:\Finance\Finance Shared\CFIDIR\ANALYSIS\Close Reports\MasterReport.xlsx")

Do While Not (rs.EOF)
    shtName = rs!SSTab
    Set wkbSource = xl.Workbooks.Open(rs("SSName"))
        Set shtToCopy = wkbSource.Sheets(shtName)
    shtToCopy.Copy Before:=wkbDest.Sheets(wkbDest.Sheets.Count)
    wkbSource.Close xlDoNotSaveChanges
  rs.MoveNext
Loop
xl.Sheets("Sheet1").Select
xl.DisplayAlerts = False
xl.ActiveSheet.Delete
xl.DisplayAlerts = True
Set wkbSource = Nothing
Set wkbDest = Nothing

    Beep
    MsgBox "Worksheet was copied"
End Sub

Private Sub Prepare_PDF_Click()
Dim MyFullName As String
Dim xlAppFTP As Object, xlWb As Object, xlWs As Object
Set xlAppFTP = CreateObject("Excel.Application")
Set xlWb = xlAppFTP.Workbooks.Open("H:\Master.xls")
MyFullName = "H:\MonthlyReport.pdf"
DoCmd.Hourglass True
xlWb.ExportAsFixedFormat Type:=0, FileName:=MyFullName, _
Quality:=1, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

xlWb.Close
Set xlWs = Nothing
Set xlAppFTP = Nothing
Set xlApp = Nothing
DoCmd.Hourglass False
MsgBox "The PDF file was created"

End Sub
0
CFMI
Asked:
CFMI
1 Solution
 
Robberbaron (robr)Commented:
i dont see any code to save as XLS, nor to close the first XLapp.

have reformatted to ensure items get closed and disposed.
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 rs = CurrentDb.OpenRecordset("TblReports")
        rs.MoveFirst
        
        Set xl = CreateObject("Excel.Application")
            xl.Visible = True
    
            Set wkbDest = xl.Workbooks.Open("S:\Finance\Finance Shared\CFIDIR\ANALYSIS\Close Reports\MasterReport.xlsx")
            
                Do While Not (rs.EOF)
                    shtName = rs!SSTab
                    Set wkbSource = xl.Workbooks.Open(rs("SSName"))
                    Set shtToCopy = wkbSource.Sheets(shtName)
                    shtToCopy.Copy Before:=wkbDest.Sheets(wkbDest.Sheets.Count)
                    wkbSource.Close xlDoNotSaveChanges
                    
                    rs.MoveNext
                Loop
                wkbDest.Sheets("Sheet1").Select
                xl.DisplayAlerts = False
                wkbDest.ActiveSheet.Delete
                xl.DisplayAlerts = True
            
            wkbDest.Close (True) ''<<<close with save
            Set wkbSource = Nothing
            Set wkbDest = Nothing
    
        xl.Quit   '<<<close the xlapp
        Set xl = Nothing  '<<< relase ref
    Set rs = Nothing '?????
    

    Beep
    MsgBox "Worksheet was copied"
    
End Sub

Private Sub Prepare_PDF_Click()
    Dim MyFullName As String
    Dim xlAppFTP As Object, xlWb As Object, xlWs As Object
    Set xlAppFTP = CreateObject("Excel.Application")
        Set xlWb = xlAppFTP.Workbooks.Open("H:\Master.xls")
            MyFullName = "H:\MonthlyReport.pdf"
            DoCmd.Hourglass True
            xlWb.ExportAsFixedFormat Type:=0, Filename:=MyFullName, _
                    Quality:=1, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            
            xlWb.Close
        Set xlWb = Nothing
        xlAppFTP.Quit '<<<<
    Set xlAppFTP = Nothing

    DoCmd.Hourglass False
    MsgBox "The PDF file was created"

End Sub

Open in new window

0
 
CFMIFinancial Systems AnalystAuthor Commented:
Thank you as the key was to close the application.  This allowed my additional steps to work well. Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now