ExportAsFixedFormat FileName problem

**ikslohap** · 09-15-2012, 09:44 PM

Trying to help somebody with a Word Macro, but I suspect the problem (and the answer) would be the same in Excel VBA.

Trying to use ExportAsFixedFormat to send a page of a document to a PDF file. One of the parameters is OutputFileName. If I type in OutputFileName:="c:\Trusted\TYL1234" it works fine, but if I type in OutputFileName:=strFileName, storing the exact same string in that variable, it complains that it's not a valid file name. What's up?

**Leith Ross** · 09-15-2012, 10:03 PM

Hello ikslohap ,

Welcome to the Forum!

It would help to see all the macro code rather than just a fragment.

**ikslohap** · 09-16-2012, 11:33 AM

Here's the entire macro; remember it's actually for Word, but the place where it's giving me grief is common to Excel and Access, where I do most of my coding.

Please Login or Register  to view this content.

If anyone can tell me why, I'd be very grateful. BTW, since I first posted I also tried to save to a default name, then rename it to what I want via FileSystemObject, same error, and then tried copying the default file to a new file with the desired discrete name, same error again. The discrete filename meets all the file naming conventions I know of, so I'm mystified.

**Leith Ross** · 09-16-2012, 01:23 PM

Hello ikslohap,

Thanks for posting the full macro. Try changing strFileName from a String to a Variant type. There are occasions where the argument value is always a string and the argument is typed as a Variant that the argument must also be typed as a Variant. A case in point is the Shell Application object. A folder path is always a string but the argument for a Folder object is variant. If you use a String variable for the path, it will fail. You must declare it as a Variant. Let me know if this helps.

**ikslohap** · 09-16-2012, 02:25 PM

Nope, no joy. Made it a Variant, still get the following:

Run-time error '-214767259 (80004005)';

This is not a valid filename.

HOWEVER, I just did what I probably should have done in the first place, and searched for that error number to discover the following, which looks promising. Will try it and post again here about the results after I've tried it. I'm optimistic, because when I checked the contents of my variable in the immediate window, it did indeed show the ".pdf" part on a second line....

Alright, I figured out the issue, mostly. I don't know why/where it is doing this, but it is adding a 'next-line' character (Chr(13)). So, right before telling it to save the file, I inserted the line:

pdfName = Replace(pdfName, Chr(13),"")
Now it is saving with no problem. My co-worker was helping me with this issue, and he found that if you took:

pdfNameLen = Len(pdfName)
it returns the length as 2 more than if you counted the visible characters (because of the 2 cell entries). He used:

Left(*cell text*, Len(*cell text*) - 2)
for where it collects both cell values, where the only difference is at the very end (-2 instead of -1, to get rid of the cell formatting and Char(13)). At the exact same time we said "got it!".

**ikslohap** · 09-16-2012, 02:43 PM

Eureka, that did it. Changed it back to a string variable, but applied the Replace() to it as described, and it ran like a rabbit. Thanks for your interest; hope this will be useful to you and others as well. I certainly wouldn't have come up with it on my own.

**Leith Ross** · 09-16-2012, 03:18 PM

Hello ikslohap ,

Thanks for sharing the solution. That is something I would not have thought of to ask about. That is why posting the workbook, when possible, is the best approach.

**Cutter** · 09-16-2012, 07:08 PM

@ ikslohap

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.