Solved

macro to export call range to pdf

Posted on 2013-05-28
2
537 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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!

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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