CODEWITHSUNDEEP
excelvbastring
radeg
radeg

Reputation: 61

How can I pass a cell value into a string before exporting?

I am trying to screenshot a specific portion of a sheet & export the file with a certain name. I've figured out how to properly export the file the way I'd like it to be but I'm having trouble with the naming of said file. The file name is in a cell of the active workbook so I tried using the LEFT function to extract it from the cell. Below is my code:

Sub Test() 
'Dim tempSht As Worksheet'
'Dim jobNum As Range'
'Dim imgArea As Range'
'Dim buildID As String'
'Dim imgMap As String'
'Dim imgPDF As String'

    Set tempSht = ThisWorkbook.Sheets("Template")
    Set jobNum = tempSht.Range("$E$12")
    Set imgArea = tempSht.Range("$A$11:$R$71")

    buildID = Application.Evaluate( _
        Left(jobNum, InStr(jobNum, ".") - 1))

    'Setup print area
    With tempSht.PageSetup
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.2)
        .BottomMargin = Application.InchesToPoints(0.2)
        .HeaderMargin = Application.InchesToPoints(0.1)
        .FooterMargin = Application.InchesToPoints(0.1)
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

            tempSht.PageSetup.PrintArea = imgArea.Address

            imgMap = "C:\Users\Example\Desktop\"
            imgPDF = buildID & ".pdf"

            tempSht.ExportAsFixedFormat Type:=xlTypePDF, FileName:=imgMap & imgPDF, openafterpublish:=False
            Application.ScreenUpdating = True

End Sub

No error message in the code; but the output is wrong. For example, in the 'jobNum' cell the value is H2-500.pdf but the buildID will read -500 when exporting. Any ideas as to what's wrong? My assumption is that the InStr function is having trouble with the dash (but I could be wrong).

Upvotes: 0

Views: 53

Answers (1)

BigBen
BigBen

Reputation: 49998

Remove Application.Evaluate from the following line:

buildID = Application.Evaluate( _
Left(jobNum, InStr(jobNum, ".") - 1))

Upvotes: 1

Related Questions