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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You don't seem to be using the FilePath variable in the filename argument.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top