?
Solved

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

Posted on 2013-06-07
2
Medium Priority
?
608 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

765 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