Variable file location

[ahovander1213]

I have a folder that is a template for multiple forms.
I wrote a code that open the forms and publishes to another location. Works fine.

My problem is that since this is a template that my users use to make a new file the location changes

The area in red font is my issue.

my simplistic example is that I have folders on Desktop as template. Working file is copied to My Documents

As always any help is greatly appreciated

Workbooks.Open Filename:= _
"C:\Users\alanh.CASNM\Desktop\CD - Al Test\05 Forms and progamming\QC Design.xlsm" _
, UpdateLinks:=3
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\A-ca-APP01\SmartSearchImport\Order\29\QCDESIGN.pdf" & ActiveSheet.Range("e2:e2"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWorkbook.Save
ActiveWorkbook.Close

 

[mrshl9898]

If they save the file in the same location as the file they are opening you could use ThisWorkbook.Fullname.

Just update "Your book name.xlsm"

Sub findabove()


Workbooks.Open Filename:= _
Left(ThisWorkbook.FullName, InStr(ThisWorkbook.FullName, "Your book name.xlsm") - 1) & "05 Forms and progamming\QC Design.xlsm" _
, UpdateLinks:=3
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\A-ca-APP01\SmartSearchImport\Order\29\QCDESIGN.pdf" & ActiveSheet.Range("e2:e2"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub