Results 1 to 7 of 7

Do until file name is found in directory

Threaded View

  1. #1
    Forum Contributor
    Join Date
    10-07-2013
    Location
    Wilts, England
    MS-Off Ver
    Excel 2013
    Posts
    100

    Do until file name is found in directory

    Hi All,
    I am having some trouble implementing some changes to a script I have found which prints a work sheet to a pdf printer. I have implemented a change, which saves the pdf under a name generated from the content of 2 cells. This change I have implemented fine. However now the code keeps getting stuck in a loop, here;

         Do
            DoEvents
       Loop Until Dir(sPDFPath & sPDFName) = sPDFName
    This line also does not fire correctly;
        If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
    If there is a file already named the same it just skips over it and continues with the script regardless.

    I have stepped though both scripts adapted/non adapted and I just can't work out why its getting stuck in the loop.


    Option Explicit
    
    
    Sub PrintToPDF_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    '   (Download from http://sourceforge.net/projects/pdfcreator/)
    '   Designed for early bind, set reference to PDFCreator
    
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPath As String
        Dim bRestart As Boolean
        Dim OutApp As Object
        Dim OutMail As Object
        
        '//Save As Section//
        Dim SpecName As String
        Dim wb As Workbook
        Dim SerialName As String
        Dim WSName As String
        Dim CName As String
        Dim SName As String
        
      '//Set Names
     WSName = "Quotation"
       CName = "F65"
        SName = "I65"
       
       SpecName = Sheets(WSName).Range(CName).Text
       SerialName = Sheets(WSName).Range(SName).Text
        
     '/PDF Section/
     
        sPDFName = SpecName & " " & SerialName
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        'Check if worksheet is empty and exit if so
        If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
    
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
    
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
    
        'Assign settings for PDF job
        With pdfjob
            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = sPDFPath
            .cOption("AutosaveFilename") = sPDFName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
    
        'Delete the PDF if it already exists
        If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
    
        'Print the document to PDF
        ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    
        'Wait until the print job has entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = 1
            DoEvents
        Loop
        pdfjob.cPrinterStop = False
    
        'Wait until the file shows up before closing PDF Creator
         Do
            DoEvents
       Loop Until Dir(sPDFPath & sPDFName) = sPDFName
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    With OutMail
    .To = "test@test.com"
    .CC = ""
    .BCC = ""
    .Subject = "Test"
    .Body = "YYY"
    .Attachments.Add = sPDFName
    .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    Link to pdfcreator;
    http://sourceforge.net/projects/pdfcreator/

    Any help would be wonderful.

    Thanks!
    Attached Files Attached Files
    Last edited by beenbee; 07-28-2014 at 08:57 AM. Reason: add link to pdfcreator

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need VBA code to search file in directory and sub-directory and show result
    By johnchencanada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2012, 11:13 PM
  2. Found Code to List All Folders and Size in Certain Directory. Need Help Editing.
    By jcranst in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2012, 03:11 PM
  3. Saving file in current directory after accessing file in another directory
    By vuxsa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2011, 11:37 AM
  4. Replies: 3
    Last Post: 06-11-2009, 07:08 PM
  5. File Not Found in correct directory, but able to read file name
    By googlebot in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-29-2008, 06:50 PM

Tags for this Thread

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