Solved

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

Posted on 2013-06-07
2
602 Views
Last Modified: 2013-06-11
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
Comment
Question by:CFMI
2 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39231175
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
 
LVL 1

Author Closing Comment

by:CFMI
ID: 39238055
Thank you as the key was to close the application.  This allowed my additional steps to work well. Thanks again!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question