Solved

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

24 Experts available now in Live!

Get 1:1 Help Now