Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel: VBA to Save Worksheets as a Named File

Posted on 2014-04-27
15
Medium Priority
?
569 Views
Last Modified: 2014-05-01
Hello,

I'm seeking some help on the below Excel VBA code. This is what I would like it to do:

1. User clicks a button, triggering a message box
2. If the user clicks 'Yes - Save' on the message box, Excel selects the four noted worksheets and saves them as a PDF, using the filename noted in cell 'savename'.
3. If the user selects 'No - Wait', nothing happens.

Below is my try at coding this. Can you help.


---

Sub mac_PDF4Pages()

mac_PDF4Pages Macro

MSG1 = MsgBox("Are you sure you want to save this PDF as "&(savename)&".PDF?,"Yes - Save","No - Wait")

If MSG1 = "Yes - Save" then

    Sheets(Array("PDFpg1", "Quote Page-Full", "PDFpg3", "PDFpg4")).Select
    Sheets("Quote Page-Full").Activate
    ThisFile = Range("savename").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile

Else
Sheets("Quote Page-Full").Activate

Open in new window


End Sub
0
Comment
Question by:dabug80
[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
  • 7
  • 7
15 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40026588
I'm not sure why you want to select the sheets.

Sub mac_PDF4Pages()

    If MsgBox("Are you sure you want to save this PDF as " & (savename) & ".PDF?", vbYesNo) = vbYes Then
        ThisFile = Range("savename").Value
        ActiveWorkbook.SaveAs Filename:=ThisFile
    Else
        Sheets("Quote Page-Full").Activate
    End If

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:dabug80
ID: 40026594
Hi MacroShadow.

The workbook contains other sheets too. I only want it to select and print the four sheets I listed ("PDFpg1", "Quote Page-Full", "PDFpg3", "PDFpg4")
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40026615
Hi,

pls try

Sub mac_PDF4Pages()

mac_PDF4Pages Macro

MSG1 = MsgBox("Are you sure you want to save this PDF as "&(savename)&".PDF?,"Yes - Save","No - Wait")

If MSG1 = "Yes - Save" then

    Sheets(Array("PDFpg1", "Quote Page-Full", "PDFpg3", "PDFpg4")).Copy ' Create a new workbook with this sheets
    Sheets("Quote Page-Full").Activate
    ThisFile = Range("savename").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    ActiveWorkbook.Close savechanges:=False

Else
Sheets("Quote Page-Full").Activate

Open in new window

Regards
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 27

Expert Comment

by:MacroShadow
ID: 40026622
Sub mac_PDF4Pages()

    Dim ThisFile As String
    
    ThisFile = Sheets("Quote Page-Full").Range("savename").Value

    If MsgBox("Are you sure you want to save this PDF as " & (ThisFile) & ".PDF?", vbYesNo) = vbYes Then
        Sheets(Array("PDFpg1", "Quote Page-Full", "PDFpg3", "PDFpg4")).Copy ' Create a new workbook with this sheets
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile, Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        ActiveWorkbook.Close SaveChanges:=False
    Else
        Sheets("Quote Page-Full").Activate
    End If

End Sub

Open in new window


Note: Range("savename") must be a full file name including path (without the extension), i.e. C:\Test\test
0
 
LVL 1

Author Comment

by:dabug80
ID: 40033799
Thanks MacroShadow. Works well. Sorry for the silly question but can you incorporate the C:\Test\test range into the code so I can see exactly how it's expressed.

Cheers
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40033857
Sub mac_PDF4Pages()

    Dim ThisFile As String
    
    'ThisFile = Sheets("Quote Page-Full").Range("savename").Value
    ThisFile = "C:\Test\test"

    If MsgBox("Are you sure you want to save this PDF as " & (ThisFile) & ".PDF?", vbYesNo) = vbYes Then
        Sheets(Array("PDFpg1", "Quote Page-Full", "PDFpg3", "PDFpg4")).Copy ' Create a new workbook with this sheets
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile, Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        ActiveWorkbook.Close SaveChanges:=False
    Else
        Sheets("Quote Page-Full").Activate
    End If

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:dabug80
ID: 40033871
Further to the above comment,

I have named a cell 'savepath'. This gives the file path reference (e.g c:\test). How would I therefore incorporate this cell reference as the file path in the MacroShadow code?

Cheers again
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40033895
Sub mac_PDF4Pages()

    Dim ThisFile As String
    
    ThisFile = Sheets("Quote Page-Full").Range("savepath").Value

    If MsgBox("Are you sure you want to save this PDF as " & (ThisFile) & ".PDF?", vbYesNo) = vbYes Then
        Sheets(Array("PDFpg1", "Quote Page-Full", "PDFpg3", "PDFpg4")).Copy ' Create a new workbook with this sheets
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile, Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        ActiveWorkbook.Close SaveChanges:=False
    Else
        Sheets("Quote Page-Full").Activate
    End If

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:dabug80
ID: 40033927
Thanks.

This gives me a runtime error 1004. I click debug and line 9 & 10 of your code are highlighted in yellow.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40033981
What is the value of ThisFile at the time of error?

To get the value, in the immediate window (ctrl+G to open it) type ?ThisFile then press Enter.
0
 
LVL 1

Author Comment

by:dabug80
ID: 40036028
The code seems to create a new workbook and duplicate the sheets for PDF publishing. I tried to press (ctrl+G) on this duplicate workbook but it did not trigger anything. I could not select the original workbook to press ctrl+ G.

I therefore clicked 'debug' again, but I got the message 'out of memory'. It's worth nothing that three of the Worksheets have large images on them. The total file size of the xlsx file is 3.5MB.

I also don't have a 'ThisFile' naming reference setup. I figure that's a particular hard code reference?

Cheers.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40036381
I could not select the original workbook to press ctrl+ G.
Why not?

I also don't have a 'ThisFile' naming reference setup. I figure that's a particular hard code reference?
ThisFile is a variable that holds the value of your savepath cell. It is populated in this line:
ThisFile = Sheets("Quote Page-Full").Range("savepath").Value

Open in new window


What path do you have in the savepath cell?

It is possible you're encountering an error if you are using the root of the drive (i.e c:\) to save the file in.
0
 
LVL 1

Author Comment

by:dabug80
ID: 40036435
I could not select the original workbook to press ctrl+ G.

Why not?

I'm met with the attached runtime error. Plus my cursor is stuck in 'thinking hour glass' mode. This is all happening within the mirror workbook 'book 1'. I can't return to the core workbook without hitting end or debug on the attached error message.

What path do you have in the savepath cell?

It is possible you're encountering an error if you are using the root of the drive (i.e c:\) to save the file in.

Originally I had C:\. I've since changed this to S:\ (another viable location).

No PDFs were saved in the above process due to the error message

:(
VBerror.gif
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 40036462
I can't return to the core workbook without hitting end or debug on the attached error message.
Correct. Hit Debug then type ?ThisFile in the immediate window and press Enter. What is the value?

Originally I had C:\. I've since changed this to S:\ (another viable location).
Don't use a root, try a deeper path such as S:\Test\test. As I wrote, Windows at times won't let you save to the root do to permission issues.
0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 40036468
Thanks for your excellent solution. Very much appreciated.

Once I set the path to a folder deeper on S:\ it worked perfectly.

You are a champion.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

704 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