Saving file to PDF using Macros on different computer's common cloud database

[Cherlyn Tan]

Hi,

I am very new to this excel VBA thing and am trying to make a more efficient excel workbook so for a start I created a macros linked to a "button" to output workbook to PDF immediately.

Here's the code:
[/CODE]

I tried to change the naming of the filename so that other users of the same dropbox access will be able to print to PDF to the correct folder however, it doesn't seems to work.

Here are the folder paths that I have used and tried:
Folder path that worked with macros code:
(i) E:\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV

Folder paths that does not work with macros code:
(i) C:\Users\Sze Nee\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV
(ii) C:\Users\L AUNG NAN\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV

Is it possible for all the users of a different device to run the macros successfully?

Appreciate your help!

 

[Cherlyn Tan]

Sub FiletoPDF()
'
' FiletoPDF Macro
'

'
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Environ("%HOMEDRIVE%") & Environ("%HOMEPATH%") & "\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV\Org Chart PDF\YYMMDD_OrgChart.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    
    
End Sub

Here's the code that I have used.. Thanks in advance! Really appreciate it!

 

[Cherlyn Tan]

Hi Everyone! Thank you for your time! Managed to get it solved on temporarily with the code below:

Sub FiletoPDF()

On Error Resume Next
'For Office Desktop Users
    Path = Environ("USERPROFILE") & "\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV\Org Chart PDF"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Path & "\YYMMDD_OrgChart" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
        
  'For YY Computer
    Path2 = "D:\Users\Dropbox\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV\Org Chart PDF"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Path2 & "\YYMMDD_OrgChart" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    
    'For Lyn Computer
    Path3 = "E:\Dropbox\03_AKK_New_Projects\00 Company Profile & Project Library\04_Org Chart & CV\Org Chart PDF"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Path3 & "\YYMMDD_OrgChart" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
        
         MsgBox "PDF has been Printed"

End Sub

Although there's definitely some limitation, if new users with different dropbox folder path come in may have error in ouput.

But hope that this can help people who wants their file to be saved in dropbox user of different devices!