CODEWITHSUNDEEP
vbaexcel
user3666237
user3666237

Reputation: 153

Pick Save Path from a Cell

I have the following code:

Const SAVE_PATH = "S:\Divisional Support\RVU Programs\Payroll 2015\2015-04 April\PDF's\Baptist Easley"

'paste file destination in the above location'

   Dim cell As Range
   Dim wsSummary As Worksheet
   Dim counter As Long

   Set wsSummary = Sheets("PERFORM. SUM. - EASLEY")

   For Each cell In Worksheets("NAME KEY").Range("$H:$H")
      If cell.Value <> "" Then

         'progress in status bar
         counter = counter + 1
         Application.StatusBar = "Processing file: " & counter & "/1042"

         With wsSummary
            .Range("$A$4").Value = cell.Value
            .ExportAsFixedFormat _
                  Type:=xlTypePDF, _
                  Filename:=SAVE_PATH & "\" & cell.Value & ".pdf", _
                  Quality:=xlQualityStandard, _
                  IncludeDocProperties:=True, _
                  IgnorePrintAreas:=False, _
                  OpenAfterPublish:=False
         End With
      End If
  Next cell


   Set wsSummary = Nothing
End Sub

My issue is simple instead of having to step into the code to change the location where it's saved: Const SAVE_PATH = "S:\Divisional Support\RVU Programs\Payroll 2015\2015-04 April\PDF's\Baptist Easley"

I want to be able to post that location in cell J2, how will I code that?

Upvotes: 1

Views: 577

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

I want to be able to post that location in cell J2, how will I code that?

It's very simple actually

Replace the line

FileName:=SAVE_PATH & "\" & cell.Value & ".pdf"

with

FileName:=Thisworkbook.Sheets("Sheet1").Range("J2").Value & _
          "\" & cell.Value & ".pdf"

Change the Thisworkbook and Sheet1 to the relevant workbook or sheet.

Upvotes: 4

Related Questions