Macro that saves worksheet as a PDF with specified cell value

[connor12]

Hi All,

I am new to VBA, and was hoping someone could help me with one of my macros. I am trying to make a macro that will save a worksheet as a PDF with a specific name and date (which will change). In addition, this file will be saved as a pdf often with new information each time, so the macro needs to capture the name and date changes to distinguish the different pdf copies. Here is what I have so far:

Please Login or Register  to view this content.

In Cell A6 I have the following:

="C:\Users\Connor\Documents\For Work\"&TEXT($E$8,"Text")&"_"&TEXT($G$3,"mmm-dd-YYYY")&".pdf"

Where E8 is the cell containing the name (that changes) and G3 is a cell with the date on the excel form.
The line shows up like I expected:

C:\Users\Connor\Documents\For Work\Name_Sep-01-2013.pdf

However, when I go to run the macro, I keep getting an error type 13 mismatch, and I can't figure out where I went wrong.

Does anyone know what I'm doing wrong? Thanks in advance!!

[Alf]

Test

Please Login or Register  to view this content.

instead

Alf

[connor12]

Thank you Alf, that got rid of that error. However, now I am getting a "run-time error 1004" saying that the doc cant be saved. Any ideas?

Thanks for your help!

[Alf]

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If that does not help perhaps you could upload a mockup file I could test.

Alf

[jaslake]

Hi connor12

Try changing this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

As Alf noted, you also need to Dim s as a String.

Please Login or Register  to view this content.

[connor12]

Hi John,

Unfortunately changing value to text results in the same error. Could it be something to do with the default macro settings in excel? I can't seem to figure out what it is that I am doing wrong.

Thanks for your help,

Connor

[jaslake]

Hi connor12

I don't know Connor, I simulated your stuff and using this Code

Please Login or Register  to view this content.

Attached you'll see my Output.

Is there some Code you've not shared?

[jaslake]

Hi connor12

A thought just struck me...is THIS on the ActiveSheet...of not you'll need to fully qualify it.

Please Login or Register  to view this content.

[connor12]

What I have posted is the only code that I used, that's why I'm wondering if it is something to do with my settings somewhere in excel?

Everything I am doing with this macro (button, cell reference, worksheet I want copied), is all on the same worksheet, so it's not a problem of activating the worksheet. Also, if I set 's' to a blank cell on the same worksheet (for mine I used E5 as an example), then the macro works, and saves it as "0.pdf" in the same folder location as where my excel form is found (which is the same location I am trying to get the macro to save the PDF version of the sheet to)

I realize this reply is a bit wordy, so please let me know if I am being unclear anywhere.

Thank you,

Connor

[connor12]

Sorry, my mistake, if I set 's' to a blank cell "E5", it saves it as the same name as the excel workbook, just in a PDF format.

[jaslake]

Hi Conner

As I've said, I can simulate your environment and the Code does as desired. If you'd like me to try to troubleshoot it more I'll need to see your File.

[connor12]

Hi Jaslake,

sorry for the late reply, I have been caught up at work. Here is the file. I made some changes as to the name of the folder where the PDF macro should save it, and I changed the code accordingly. Still not sure why it isn't working.

Thank you,

Connor

[jaslake]

Hi Conner

This Code in the attached works for me in Excel 2007 and 2010...don't honestly know what the issue could be.

There's no settings I'm aware of that would affect this...what Version of Excel are you running...

Please Login or Register  to view this content.

[connor12]

I'm using excel 2010, still doesn't work. Thank you for your help though

[jaslake]

Hi Conner

I'll try to get some Experts to look at this...perhaps they can shed some light on the issue...

[shg]

Does the directory exist?

Is there a .log file in the directory where the pdf was supposed to appear?

[jaslake]

@shg

I'll wait...an obvious question...built the OP's Tree...worked for me...

[connor12]

The PDF file was supposed to appear in the folder titled "For Jag"

[jaslake]

Hi Conner

Are you getting an Error Message?