Solved

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

Posted on 2013-06-07
2
598 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now