Search code examples
excelvbastring-concatenation

String concatenation in Excel VBA failing with run-time error

Why does:

sDesktopPath = Environ("USERPROFILE") & "\Desktop\"
sTimeStamp = Format(Now(), "yyyymmdd-hh:nn")
sSuffix = "_1Sheet_" & sDateTimeNow

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    sDesktopPath & ActiveSheet.Range("E3").Value & "_1Sheet_" & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

work without problems, BUT:

sDesktopPath = Environ("USERPROFILE") & "\Desktop\"
sTimeStamp = Format(Now(), "yyyymmdd-hh:nn")
sSuffix = "_1Sheet_" & sDateTimeNow

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    sDesktopPath & ActiveSheet.Range("E3").Value & "_1Sheet_" & sTimeStamp & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

Fails with a runtime error?

enter image description here

I get the same problem if I use sSuffix in place of the "_1sheet_" & TimeStamp.

The VBA editor shows the error as being in the last line of the Export... statement, but nothing is changing in that line and I can't see anything that is changed before it that would break the syntax.

enter image description here

Solution

  • Whenever something like this happens, try to debug the whole string. Thus:

    Debug.Print sDesktopPath & ActiveSheet.Range("E3").Value & "_1Sheet_" & sTimeStamp & ".pdf"

    You will see that the sTimeStamp is "illegal", containg :. Thus, change the timestamp from:

    sTimeStamp = Format(Now(), "yyyymmdd-hh:nn")

    To:

    sTimeStamp = Format(Now(), "yyyymmdd-hhnn")