+ Reply to Thread
Results 1 to 4 of 4

Export sheet to PDF - fails unless user has manually exported a PDF

Hybrid View

  1. #1
    Registered User
    Join Date
    08-02-2018
    Location
    NI
    MS-Off Ver
    Office 365
    Posts
    2

    Export sheet to PDF - fails unless user has manually exported a PDF

    I have code in a workbook to export a hidden sheet to PDF by activating a macro. Users have reported the macro fails. I've pinpointed that the macro fails unless the user has previously manually "saved as" a sheet to PDF, which seems to wake up the macro.

    My code is

    Sub PRINTPDF()
    Sheets("Hardcopy").Visible = True
    Sheets("Hardcopy").Activate
    'Code from Contextures.com
            Dim wsA As Worksheet
            Dim wbA As Workbook
            Dim strTime As String
            Dim strName As String
            Dim strPath As String
            Dim strFile As String
            Dim strPathFile As String
            On Error GoTo 0
            Dim mainfilename As String
            
            mainfilename = Range("FILENAME")
            
            Set wbA = ActiveWorkbook
            Set wsA = ActiveSheet
            strTime = Format(Now(), "yyyymmdd\_hhmm")
            
            'get active workbook folder, if saved
            strPath = wbA.Path
            If strPath = "" Then
              strPath = Application.DefaultFilePath
            End If
            strPath = strPath & "\"
            
            'replace spaces and periods in sheet name
            strName = Replace(wsA.Name, " ", "")
            strName = Replace(strName, ".", "_")
            
            'create default name for savng file
            strFile = mainfilename & "_" & strName & "_" & strTime & ".pdf"
            strPathFile = strPath & strFile
            
              
            'export to PDF in current folder
                wsA.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    filename:=strFile, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False
                'confirmation message with file info
                MsgBox "PDF file has been created: " _
                  & vbCrLf _
                  & strPathFile
            
           
    Sheets("Hardcopy").Visible = False
    Sheets("DataEntry").Activate
    Range("B8").Select
    
    End Sub
    Is this an error in my code, or an Excel bug? Appreciate I have no error handling (any guidance on this would also be appreciated). Excel version is latest and greatest Office 365, 32 bit.

  2. #2
    Valued Forum Contributor meabrams's Avatar
    Join Date
    10-03-2014
    Location
    USA
    MS-Off Ver
    2007, 2010, 2013, 2016
    Posts
    451

    Re: Export sheet to PDF - fails unless user has manually exported a PDF

    The only thing that I see that might cause some issues is

    this line
    filename:=strFile,
    should read
    filename:=strPathFile,
    other then that it looks like the same basic setup I have in many of my excel projects
    Bramz

  3. #3
    Registered User
    Join Date
    08-02-2018
    Location
    NI
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Export sheet to PDF - fails unless user has manually exported a PDF

    Meabrams - This (strPathFile) fixed the save issue, thanks!

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,913

    Re: Export sheet to PDF - fails unless user has manually exported a PDF

    This will do nicely.
    Sub PRINTPDF()
        'Code from Contextures.com
    
        Dim strTime As String
        Dim strName As String
        Dim strPath As String
        Dim strFile As String
        Dim strPathFile As String
        Dim mainfilename As String
        
        On Error GoTo 0
        
        With Sheets("Hardcopy")
    'get mainfilename
            .Visible = True
            mainfilename = .Range("FILENAME")
            strTime = Format(Now(), "yyyymmdd\_hhmm")
    'get active workbook folder, if saved
            strPath = ThisWorkbook.Path
            If strPath = "" Then
              strPath = Application.DefaultFilePath
            End If
            strPath = strPath & Application.PathSeparator
    'replace spaces and periods in sheet name
            strName = Replace(.Name, " ", "")
            strName = Replace(strName, ".", "_")
    'create default name for saving file
            strFile = mainfilename & "_" & strName & "_" & strTime
            strPathFile = strPath & strFile
    'export to PDF in current folder
            .ExportAsFixedFormat 0, strPathFile
    'confirmation message with file info
            MsgBox "PDF file has been created: " _
                & vbCrLf _
                & strPathFile
            .Visible = False
        End With
        Application.Goto Sheets("DataEntry").Range("B8")
    End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] User Defined Function Fails when I run a Macro
    By scott altman in forum Excel General
    Replies: 11
    Last Post: 09-11-2017, 11:41 PM
  2. [SOLVED] How to create a static progress bar that is fixed and responds to user input manually
    By chin67326 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-29-2016, 04:19 AM
  3. [SOLVED] User Form with Empty row but user may enter data manually
    By Nole68 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2015, 11:35 AM
  4. How to prevent user to enter serial number in column B manually
    By mso3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-27-2015, 09:50 AM
  5. [SOLVED] VBA to export from Access to Excel and wright in field "Exported"
    By SGT in forum Access Tables & Databases
    Replies: 9
    Last Post: 02-05-2015, 09:41 AM
  6. Amendments to exisitng macro to manually input agent user names
    By DancingElvis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2013, 05:05 PM
  7. Want user to select macro enabled manually
    By vivekdevkar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2010, 07:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1