Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

Excel: VBA to Save Worksheets as a Named File

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
dabug80
Asked:
dabug80
  • 7
  • 7
1 Solution
 
MacroShadowCommented:
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
 
dabug80Author Commented:
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
 
Rgonzo1971Commented:
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MacroShadowCommented:
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
 
dabug80Author Commented:
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
 
MacroShadowCommented:
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
 
dabug80Author Commented:
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
 
MacroShadowCommented:
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
 
dabug80Author Commented:
Thanks.

This gives me a runtime error 1004. I click debug and line 9 & 10 of your code are highlighted in yellow.
0
 
MacroShadowCommented:
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
 
dabug80Author Commented:
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
 
MacroShadowCommented:
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
 
dabug80Author Commented:
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
 
MacroShadowCommented:
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
 
dabug80Author Commented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now