Solved

macro to export call range to pdf

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 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