VBA code help (saving to active workbook file location

[kayne93]

Hi,
I am wanting to save my active sheet (with print set up) as a PDF with variable & set name which does work.
but i also want it to save the PDF in the active workbook file location. Currently it just saves to my documents. help would be greatly appreciated!

Sub CLEARANCETEMPLATE_Button1_Click()
File_name = ActiveWorkbook.Name
If InStr(Filename, ".") > 0 Then
Filename = Left(Filename, InStrRev(Filename, ".") - 1)
End If

File_name = Range("C7") & " - CLEARANCE "
Destination = ThisWorkbook.Path
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=File_name, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

 

[Herakles]

ActiveWorkbook.Path & "\"

returns the path in which the active workbook is saved.

ThisWorkbook.Path & "\"

returns the path in which the workbook in which the code resides is saved.

So ActiveWorkbook.Path & "\" & File_name
or
ThisWorkbook.Path & "\" & File_name

as appropriate can be used.

 

[kayne93]

i tried both and its still saves to document and not the file location?
(im completely new to VBA so apologies)

 

[Herakles]

You had File_name and File_name. Declare all variables before use them.

Have Option Explict at the top to enforce declaration.

Use Quick-wrap selection as VBA code. It is on the Mr Excel post editor menu.

Option Explicit

Sub CLEARANCETEMPLATE_Button1_Click()
Dim File_name  As String
Dim Destination As String

    File_name = ActiveWorkbook.Name
    
    If InStr(File_name, ".") > 0 Then
        File_name = Left(File_name, InStrRev(File_name, ".") - 1)
    End If

    File_name = Range("C7") & " - CLEARANCE "

    Destination = ThisWorkbook.Path
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\" & File_name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        
End Sub

 

[kayne93]

You had File_name and File_name. Declare all variables before use them.

Have Option Explict at the top to enforce declaration.

Use Quick-wrap selection as VBA code. It is on the Mr Excel post editor menu.

Option Explicit

Sub CLEARANCETEMPLATE_Button1_Click()
Dim File_name  As String
Dim Destination As String

    File_name = ActiveWorkbook.Name
  
    If InStr(File_name, ".") > 0 Then
        File_name = Left(File_name, InStrRev(File_name, ".") - 1)
    End If

    File_name = Range("C7") & " - CLEARANCE "

    Destination = ThisWorkbook.Path
  
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\" & File_name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
      
End Sub

You had File_name and File_name. Declare all variables before use them.

Have Option Explict at the top to enforce declaration.

Use Quick-wrap selection as VBA code. It is on the Mr Excel post editor menu.

Option Explicit

Sub CLEARANCETEMPLATE_Button1_Click()
Dim File_name  As String
Dim Destination As String

    File_name = ActiveWorkbook.Name
   
    If InStr(File_name, ".") > 0 Then
        File_name = Left(File_name, InStrRev(File_name, ".") - 1)
    End If

    File_name = Range("C7") & " - CLEARANCE "

    Destination = ThisWorkbook.Path
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\" & File_name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
       
End Sub

thank you, this works perfectly!