VBA saving worksheet as PDF with added numerical increments

**BettyMay** · 09-20-2021, 07:44 AM

Hello lovely community

I have come across an issue that is seriously holding me back on VBA.

I have created a sub below to convert a worksheet into a PDF file in a specific location, with the current date and specific name of the person the file is about that will be displayed in cell B3 of the worksheet. Additionally, i have attempted to add an increment on the end, (1, 2, 3) should there be multiple files saved during a day. However, i keep encountering 2 errors.

Issue 1:
The increment is working but the file is not saving as a PDF.

Issue 2:
Strangely, only the first part of the persons name is saved as the file name in the folder. It needs to be saved as the full B3 cell value, even if there is a space between words in cell B3.

I have included the code below. Your help would be much appreciated.
Thank you
Betty

Please Login or Register  to view this content.

**bakerman2** · 09-20-2021, 07:52 AM

To save a worksheet as PDF take a look at ExportAsFixedFormat-method.

Be sure there are no invalid characters in your filename. For instance change dots in your date to underscores.

**davsth** · 09-20-2021, 07:57 AM

Your end code marker should be [/CODE]

Issue 1 you are not asking to save as PDF, usually this is an export see
https://powerspreadsheets.com/save-excel-file-pdf-vba/ for exactly what you want to do

we can not see the sheet or the values of B3 so it is hard to advise

**BettyMay** · 09-20-2021, 08:01 AM

Thanks davsth,

B3 is a FirstName SecondName value, with the space in between.

I've tried to input Export As PDF instead of the 'SaveAs, Filename' at the bottom of the code but it then ignores the rest of the naming instructions?

Thanks for your help

**BettyMay** · 09-20-2021, 08:41 AM

I have tried to use the Export As Fixed Format, but i'm struggling to get it right.

Please Login or Register  to view this content.

**davsth** · 09-20-2021, 08:45 AM

Well post a sample as per the yellow banner and you might get a better answer

try and find step through the code with f8 and find the values of
Len(Sheets("TempKIT").Range("B3").Value)

eg check1=Len(Sheets("TempKIT").Range("B3").Value)
and check2 =Sheets("TempKIT").Range("B3").Value

You can see these in the locals window

**BettyMay** · 09-20-2021, 09:02 AM

Hi there,

Please see attached sanitised workbook.

Thank you so much for your help

Betty

**davsth** · 09-20-2021, 09:31 AM

try the below it works for me

Please Login or Register  to view this content.

although the path needs to be changed to H:\HR for Managers\1. Absence and Welfare\Active NOAs\ from u:\ this is where I tested it, any closer?

but it works for me.

Also on your profile the office version is 365 or 2019, 2016, 2013 etc 10 is your windows version. At some point you will be asked to change it by an admin!

**BettyMay** · 09-20-2021, 09:45 AM

Sorry, still new to the forum.

Thank you it works! I just had to Dim nametosave as a String and its working perfectly.

I can see where i was going wrong as well, so i really appreciate your efforts. I'm just looking into why it cutes off the second half of B3 value. Ws it doing that for you?

Have some reputation

**davsth** · 09-20-2021, 09:57 AM

it was cutting off the last four characters as this is what you wanted to do? If you didn't
nametosave = "u:\" & "KIT Form" & " " & Sheets("TempKIT").Range("B3").Value & " " & Format(Date, "dd.mm.yyyy") & " " & IIf(n = 0, "", n) & ".pdf"

would have worked. but you never said what you wanted the value to be, this could be a naming convention in your company for all we knew!

Glad you got there!

**bakerman2** · 09-20-2021, 10:23 AM

As already mentioned, what are you trying to achieve with this

Please Login or Register  to view this content.

Also this & ".pdf" is not needed in your savaename because when saving as PDF, the extension is automatically added.

VBA saving worksheet as PDF with added numerical increments

LinkBack

Thread Tools

Rate This Thread

Display

VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Re: VBA saving worksheet as PDF with added numerical increments

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Duplication with numerical increments

A macro that automatically increments the first cell when a new row is added

Staffing Rota Trouble - converting letters into a numerical value to be added for a total

cannot copy formula to below cell with numerical increase in increments of 1

Saving newly added workbooks

Saving a new added workbook with existing name on HD, but without being prompted.

Help saving user inputted data from a worksheet to another worksheet and saving it.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions