CODEWITHSUNDEEP
vbams-word
Daevin
Daevin

Reputation: 901

Issue with saving Word Doc as PDF "This is not a valid file name"

I'm trying to save a Word Document as a PDF, but I'm getting an error box

"Run-time error '-214747259 (80004005) This is not a valid filename."

This is the code I have for it:

Option Explicit

Private Sub cmdSave_Click()

Dim equipName As String, equipError As String, fileDate As String, pdfName As String, filePath As String

filePath = "C:\"
equipName = Replace(Left(ActiveDocument.Tables(1).Cell(1, 2).Range.Text, _
    Len(ActiveDocument.Tables(1).Cell(1, 2).Range.Text) - 1), "/", "-")
equipError = Left(ActiveDocument.Tables(1).Cell(2, 2).Range.Text, Len(ActiveDocument.Tables(1).Cell(2, 2).Range.Text) - 1)
fileDate = Replace(Date, "/", "")
pdfName = equipName & "_" & equipError & "_" & fileDate

ActiveDocument.ExportAsFixedFormat OutputFileName:=filePath & pdfName & ".pdf", _
    ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
    wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
    Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
    CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
    BitmapMissingFonts:=True, UseISO19005_1:=False

End Sub

The code to save the file as a PDF is what I took from a Macro recorded of me SaveAs-ing. The actual file path is much longer, but for simplicity's sake, I have it as "C:\" (which doesn't work either).

Upvotes: 2

Views: 5406

Answers (3)

Daevin
Daevin

Reputation: 901

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!".

Thanks, all, for helping! Final code:

Option Explicit

Private Sub cmdSave_Click()

Dim equipName As String, equipError As String, fileDate As String, pdfName As String, filePath As String, pdfLen As Integer

filePath = "C:\"
equipName = Replace(Left(ActiveDocument.Tables(1).Cell(1, 2).Range.Text, _
    Len(ActiveDocument.Tables(1).Cell(1, 2).Range.Text) - 1), "/", "-")
equipError = Left(ActiveDocument.Tables(1).Cell(2, 2).Range.Text, Len(ActiveDocument.Tables(1).Cell(2, 2).Range.Text) - 1)
fileDate = Replace(Date, "/", "")
pdfName = equipName & "_" & equipError & "_" & fileDate

pdfName = Replace(pdfName, Chr(13), "")

ActiveDocument.ExportAsFixedFormat OutputFileName:=filePath & pdfName, _
    ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
    wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
    Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
    CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
    BitmapMissingFonts:=True, UseISO19005_1:=False

End Sub

Upvotes: 2

SeanC
SeanC

Reputation: 15923

this will strip out unwanted chars:

Function CleanFilename(CurrentFilename As String) As String
Dim MyArray()
Dim x As Integer

    MyArray = Array("<", ">", "|", "/", "*", "\", "?", """", ":")
    For x = LBound(MyArray) To UBound(MyArray)
        CurrentFilename = Replace(CurrentFilename, MyArray(x), "_", 1)
    Next x
    CleanFilename = CurrentFilename
End Function

I wouldn't recommend running it on the fully qualified pathname, but on the pdfName, else C:\test.pdf would become C__test.pdf

Upvotes: 3

macycron
macycron

Reputation: 91

The text you have in the ActiveDocument in Cell1,2 and 2,2 may have special characters. If you don't see any windows reserved characters like \ / : * ? " < > | then you may have a strange hidden or white character.

Also if DATE has : in it, then it won't save. You'll have to strip all of the reserved chars because a windows filename cannot contain any of the following characters: \ / : * ? " < > |

Upvotes: 3

Related Questions