CODEWITHSUNDEEP
vbaexcel
NorwegianLatte
NorwegianLatte

Reputation: 89

vba save as pdf into a shared folder with different computer

Hi I wrote a code where it saves the excel sheet as PDF file into a our company's sharefolder (dropbox). I realized when my coworker tried to use that Macro, it doesn't work because of the path the file is saved.

in the code, where it says "MyComputerName" is what my computer name and i am guessing it's because my co workers computer name is different so it can't find the path on her computer.

Is there a way to solve this? so we both can use this macro and save it into the shared folder ?

Help!!!

Sub SaveAsPDF()

' FormatName
ActiveSheet.Name = "#" & ActiveSheet.Range("F6").Value & " " & ActiveSheet.Range("F4").Value

' saveAsPDF Macro   
ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, Filename:= _
  "C:\Users\MyComputerName\Dropbox\Team Folder\PACKING LIST\201804\" & "PACKING LIST_" & ActiveSheet.Name _
    , quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
End Sub

Upvotes: 0

Views: 1506

Answers (1)

BigBen
BigBen

Reputation: 49998

Try adding the following lines to the beginning of your code:

Dim username As String
username = Environ$("username")

And then your path should be:

"C:\Users\" & username & "\Dropbox\...

To make the year/month dynamic (assuming based on today's date), your link can be:

...LIST\" & Format(Now(), "yyyymm") & "\PACKING LIST...

Upvotes: 1

Related Questions