Trying to Save-As a pdf by pulling file name from a cell

[DivingDoug]

I pulled some good info from some other posts to get me to a certain level but have hit a road block.

I am pasting data into a template, formating all the data w/ a macro (got that one running good)
Then I am pulling one pc of the pasted data to generate a file name by extracting part of a cell [ LEFT(xx) ] and contencating it with some other fixted strings then copying teh final result and pasting it as a value so that I end up with just a text entry of my desired file name. Extension and all. I found that I can even include the path into that and the Save-AS command seems to recognize it.

Problem is, while the file name and path end up as desired, it won't actually save the file. The macro stops after opening the Save-As dialog and selectes the folder and inserts the file name but when I click Save, it just closes and saves nothing. I did notice the the file name was enclosed in quotes in the dialog box. Not sure if this matters or not.

Current code is using the ChDir funtion to select my Save-As folder. Seems to work ok.

Code is as follows:

Please Login or Register  to view this content.

Contecated string for file name is "GTR_CAS-1004_V02-31.pdf"


If anyone can point out what I may be doing wrong (probably multiple things), I would surely appreciate it.


Doug

[VBA FTW]

1st, rather than selecting AA8, just use vFileName= Range("AA8").Text. There's really no need to select, it's just an extra step.

2nd,

Please Login or Register  to view this content.

this code will export Sheet1 as a PDF, and concatenate the data through VBA to use as the file name. You can easily change that to your vFilename variable. If you don't want to view the PDF after it's created, change the last word above to False, and you'll need to change "C:\" to your chDir path

[DivingDoug]

What timing. I had actually discoverd the ExportAsFixedFormat function and was experimenting with it when you posted. I saw where you used Sheets instead of Workbook which makes sense since it is really kinda printing the sheet to pdf and not saving the whole workbook file.

I still built my file name outside the macro and the ChDir function still worked to place me in the correct location. And I actually want it to open after writing the pdf so that works out as well.

The only thing I did notice was that when I repeated the command, it did not warn me about overwriting the existing file. Any thoughts on that ?

[DivingDoug]

Actually, I will look later tonite or in morning and I expect there is a method to see if a given file already exists and if so, sequence the file name or prompt the operator.

Will work on that

[VBA FTW]

Yup. You can check if a file exists or not, and then show a message box. I'm doing similar on several projects I'm working on. Let me know if you need any help. Maybe something like

Please Login or Register  to view this content.

EDIT: I don't think this will take into account the pdf extension of the file you're looking for

[DivingDoug]

Got it all working except for whrn I DO want to overwrite the existing file. Creates pdf if it does not exist ok. Finds the file if it exists and pops dilaog box ok. If I click NO it ends the macro. If I hit YES it ends the macro but fails to save the file.

Any thoughts?

Please Login or Register  to view this content.

Still working on it but any help is appreciated. Did have to get rid of the ChDir line as using the Dir function seemed to disrupt it and even if I reapplied it right above the export function, it didn't seem to take. Just built the path in the export function to get around that though. I know there is probably a way to point to the export function without having to have it there twice but I'll diddle with that later.

Thanks... Doug

[DivingDoug]

Ok, figured out that issue and the entire save-as routine works as needed.

One last problem. When I execute a function to close a text file I copied a bunch of info from, excel pops up with the "Large amount of data on the cliboard yada yada yada". Can I eliminate this dialog from popping up or close it with the macro?

Thanks.... Doug

[jaslake]

"Large amount of data on the cliboard yada yada yada".

Please Login or Register  to view this content.

[DivingDoug]

Wow. I really should have figured that one out. But bear in mind, I only started working with this VBA stuff a couple weeks ago.

Thanks

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.