Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

macro to export call range to pdf

Without specifying a specific folder, my macro should by default save the pdf to “My Documents” on C drive.  Macro below:

Sub exportpdf()
ActiveSheet.PageSetup.PrintArea = "a6:u459"
With ActiveSheet.PageSetup
   
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlLandscape
   
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
   
End With
Sheets("SKU-Category").Range("a6:u459").ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("I8").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

End Sub

For some reason, the macro is causing all my pdf to be saved to a folder on the Network drive.  I use this folder a lot, but not sure why it’s defaulting to that folder.

If I modify the macro as below it saves it to Desktop but this wouldn’t work for other users I want to send to as I have to specify my user name in the macro.  In other words, anyone who is not JohnDoe wouldn’t be able to run the macro.  

Sub exportpdf()
ActiveSheet.PageSetup.PrintArea = "a6:u459"
With ActiveSheet.PageSetup
   
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlLandscape
   
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
   
End With
Sheets("SKU-Category").Range("a6:u459").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\jsroy\Desktop\" & Range("I8").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

End Sub
0
Goraps
Asked:
Goraps
  • 2
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

I have added the following code to the attached workbook:

Option Explicit

Private Declare Sub CoTaskMemFree _
                Lib "ole32.dll" _
             (ByVal pvoid As Long)

Private Declare Function SHGetPathFromIDList _
                     Lib "shell32.dll" _
                   Alias "SHGetPathFromIDListA" _
                   (ByVal Pidl As Long, _
                    ByVal pszPath As String) As Long

Private Declare Function SHGetSpecialFolderLocation _
                     Lib "shell32.dll" _
                  (ByVal hWnd As Long, _
                   ByVal nFolder As Long, _
                   ByRef ppidl As Long) As Long
    
Public Const lngCSIDL_PERSONAL                          As Long = &H5
Public Const lngCSIDL_DESKTOPDIRECTORY                  As Long = &H10
Public Function strSpecial_Folder(ByVal lngFolder As Long) As String
  
' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28140660.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28140660
' Question Title:   macro to export call range to pdf
' Question Asker:   Goraps                                    [ http://www.experts-exchange.com/M_4270685.html ]
' Question Dated:   2013-05-28 at 16:10:36
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------
  
  Dim lngFolderFound                                    As Long
  Dim lngPidl                                           As Long
  Dim lngPidlFound                                      As Long
  Dim strPath                                           As String
  Dim strReturn                                         As String
  
  On Error GoTo Err_strSpecial_Folder
  
  Const MAX_PATH                                        As Long = 260&
  
  strReturn = ""
  strPath = Space(MAX_PATH)
  
  lngPidlFound = SHGetSpecialFolderLocation(0, lngFolder, lngPidl)

  If lngPidlFound = 0& Then
     If (SHGetPathFromIDList(lngPidl, strPath)) Then
        strReturn = Left$(strPath, InStr(1&, strPath, vbNullChar) - 1&)
    End If ' If (SHGetPathFromIDList(lngPidl, strPath)) Then
  End If ' If lngPidlFound = 0& Then
  
Exit_strSpecial_Folder:

  On Error Resume Next
  
  If lngPidl <> 0& Then
     Call CoTaskMemFree(lngPidl)
  End If ' If lngPidl <> 0& Then
  
  strSpecial_Folder = strReturn
  
  Exit Function
  
Err_strSpecial_Folder:

  On Error Resume Next
  
  strReturn = ""
  
  Resume Exit_strSpecial_Folder
  
End Function
Public Sub Test()

  Dim strDesktop_Folder                                 As String
  Dim strPersonal_Folder                                As String
  
  strDesktop_Folder = strSpecial_Folder(lngCSIDL_DESKTOPDIRECTORY)
  strPersonal_Folder = strSpecial_Folder(lngCSIDL_PERSONAL)
  
  MsgBox "Desktop Folder:" & _
         vbCrLf & _
         strDesktop_Folder & _
         vbCrLf & vbLf & _
         "Personal Folder:" & _
         vbCrLf & _
         strPersonal_Folder, _
         vbInformation Or vbOKOnly, _
         ThisWorkbook.Name
         
End Sub
Sub exportpdf()

  ActiveSheet.PageSetup.PrintArea = "a6:u459"
  
  With ActiveSheet.PageSetup
      .CenterHorizontally = True
      .CenterVertically = True
      .Orientation = xlLandscape
      .Zoom = False
      .FitToPagesWide = 1
      .FitToPagesTall = 1
  End With

' Sheets("SKU-Category").Range("a6:u459").ExportAsFixedFormat Type:=xlTypePDF, _
                                                              Filename:="C:\Users\jsroy\Desktop\" & Range("I8").Value, _
                                                              Quality:=xlQualityStandard, _
                                                              IncludeDocProperties:=True, _
                                                              IgnorePrintAreas:=False, _
                                                              OpenAfterPublish:=True

  Sheets("SKU-Category").Range("a6:u459").ExportAsFixedFormat Type:=xlTypePDF, _
                                                              Filename:=strSpecial_Folder(lngCSIDL_PERSONAL) & Range("I8").Value, _
                                                              Quality:=xlQualityStandard, _
                                                              IncludeDocProperties:=True, _
                                                              IgnorePrintAreas:=False, _
                                                              OpenAfterPublish:=True

End Sub

Open in new window


Hopefully you can see I have added a (Public) function, "strSpecial_Folder", that allows you to gain the location of the user's Personal folder (at run-time).

I have also included an example of how to gain the location of the "Desktop" folder, for your reference.

BFN,

fp.
Q-28140660.xls
0
 
[ fanpages ]IT Services ConsultantCommented:
For background reading:

"Identify the Location of Special Folders with API Calls"
[ http://msdn.microsoft.com/en-us/library/office/aa140088%28v=office.10%29.aspx ]
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now