Solved

macro to export call range to pdf

Posted on 2013-05-28
2
473 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

21 Experts available now in Live!

Get 1:1 Help Now