Solved

macro to export call range to pdf

Posted on 2013-05-28
2
489 Views
Last Modified: 2013-06-12
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
Comment
Question by:Goraps
  • 2
2 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39201752
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39201767
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now