KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
I tried to create a procedure whose objective is to convert the excel spreadsheet to pdf and save it in the folder "D:\Users\(UserName)\Documents\Project\(MachineName)", this program will be used by several users on different computers , how can I make the PDF be saved in the documents regardless of the user who is accessing it?



Available folders to save the file depending on the MachineName variable:

print_1.JPG




My Sub for convert file to PDF:

print_2.JPG



The file format must remain the same (MachineName, "Date of Today")

print_3.JPG
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You don't seem to be using the FilePath variable in the filename argument.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
You don't seem to be using the FilePath variable in the filename argument.
Sorry, the "FilePath" in the argument doesn’t appear in the photo, but when I tried it didn’t work either


print_1.JPG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
1. Please don't post code as pictures. It just means anyone trying to help has to retype the whole thing (which means they are less likely to bother...)

2. You appear to be missing a backslash on the end of FilePath, and that code could be tidied up (but see point 1).
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

1. Please don't post code as pictures. It just means anyone trying to help has to retype the whole thing (which means they are less likely to bother...)

2. You appear to be missing a backslash on the end of FilePath, and that code could be tidied up (but see point 1).
Sorry, there is the code:

Can u check for me the cause of the error? I already tried to put the backslash but doesn´t result

VBA Code:
Sub SaveAsPDF()

Dim FilePath As String

    Select Case MachineName
    Case "A4"
        FilePath = Environ("userprofile") & "\Documents\Projeto\A4"
    Case "A8.1"
        FilePath = Environ("userprofile") & "\Documents\Projeto\A8_1"
    Case "A8.2"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A8_2"
    Case "A8.3"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A8_3"
    Case "A12.1"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A12_1"
    Case "A12.2"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A12_2"
    Case "A12.3"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A12_3"
    Case "A20.1"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A20_1"
    Case "A20.2"
         FilePath = Environ("userprofile") & "\Documents\Projeto\A20_2"
    End Select

    Sheets("Final").Range("A1:AO69").ExportAsFixedFormat Type:=xlTypePDF, filename:= FilePath &
    MachineName & " _ " & Format(Now(), "dd.mm.yyyy") & ".pdf" _
     , Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
       
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
VBA Code:
Sub SaveAsPDF()

Dim FilePath As String

    Select Case MachineName
    Case "A4","A8.1","A8.2","A8.3","A12.1","A12.2","A12.3","A20.1","A20.2"
         FilePath = Environ("userprofile") & "\Documents\Projeto\" & MachineName & "\"

    Sheets("Final").Range("A1:AO69").ExportAsFixedFormat Type:=xlTypePDF, filename:= FilePath & MachineName & " _ " & Format(Now(), "dd.mm.yyyy") & ".pdf" _
     , Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
     End Select
End Sub

If that doesn't work, we need to know exactly what is happening - are you getting an error (if so, what and on which line), or is it saving to the wrong place, or not saving at all, or... ?
 
Last edited:
Solution

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
Sub SaveAsPDF()

Dim FilePath As String

    Select Case MachineName
    Case "A4","A8.1","A8.2","A8.3","A12.1","A12.2","A12.3","A20.1","A20.2"
         FilePath = Environ("userprofile") & "\Documents\Projeto\" & MachineName & "\"

    Sheets("Final").Range("A1:AO69").ExportAsFixedFormat Type:=xlTypePDF, filename:= FilePath &
    MachineName & " _ " & Format(Now(), "dd.mm.yyyy") & ".pdf" _
     , Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
     End Select
End Sub

If that doesn't work, we need to know exactly what is happening - are you getting an error (if so, what and on which line), or is it saving to the wrong place, or not saving at all, or... ?

is saving to the project folder but not saving to the folder corresponding to the machine

And your code dont work....


print_1.JPG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That's actually your code as you posted it. I didn't notice that you broke it onto two lines but didn't put a line continuation character in. I've amended my version above.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
That's actually your code as you posted it. I didn't notice that you broke it onto two lines but didn't put a line continuation character in. I've amended my version above.
Now results, thank u soo muchhh!!
 

Forum statistics

Threads
1,148,157
Messages
5,745,105
Members
423,923
Latest member
yuvaraj859

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top