locked
Excel - file not found VBA RRS feed

  • Question

  • I am going to simplify my somewhat lengthy code in here because I can't wrap my head around this error.

    I have a successful code that uses Outlook vba that exports all of my attachments to a specified folder. 

    Via vba code that I want to program to run, I want to then be able to go through each file, and based off of the file name either move it to a specified subfolder or print it first (adding some formatting) and then move it to the specified folder. The code works, but for some reason Excel keeps looking for the file after I've moved it, and I get an error from Excel (not access which I'm coding in) stating it cannot locate the file. I even disabled alerts in Excel, but the little bugger still shows. I'm pulling my hair out here. Any advice is greatly welcomed. 

    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sub WHYWONTYOUWORK Dim MyFile As String Dim MyPath As String Dim MyDone As String Dim MyName As String Dim ReportName As Integer Dim ReportName2 As Integer Dim xlApp As Excel.Application Dim wb As Excel.Workbook Dim BackSlash As Integer, Point As Integer Dim FilePath As String Dim i As Integer MyPath = "C:\Reports\" MyFile = Dir("C:\Reports\*.xlsx") MyDone = "C:\Reports\Done\" 'Kill any instances of Excel Kill_Excel 'code pasted below Sleep (30000) While Len(MyPath & MyFile) > 0 'Define the Report Names ReportName1 = InStr(MyFile, "ReportName1") ReportName2 = InStr(MyFile, "ReportName2") 'Report 1 If ReportName1 = 1 Then Name MyPath & MyFile As MyDone & MyFile 'Report 2 ElseIf ReportName2 = 1 Then PrintAtmt (MyPath & MyFile) 'Print Atmt coded below Sleep (30000) Name MyPath & MyFile As MyDone & MyFile

     End If

    Wend

    End Sub

    PrintAtmt Sub it calls
     Sub PrintAtmt(fFullPath As String)
            Dim xlApp As Excel.Application
            Dim wb As Excel.Workbook
            Dim BackSlash As Integer, Point As Integer
            Dim FilePath As String
            Dim i As Integer
            Dim MyName As String
            Dim MyKill As String
            Set xlApp = New Excel.Application
            Set wb = xlApp.Workbooks.Open(fFullPath)
    
            FilePath = xlApp.ActiveWorkbook.FullName
            
            For i = Len(FilePath) To 1 Step -1
                If Mid$(FilePath, i, 1) = "." Then
                    Point = i
                    Exit For
                End If
            Next i
            If Point = 0 Then Point = Len(FilePath) + 1
            For i = Point - 1 To 1 Step -1
                If Mid$(FilePath, i, 1) = "\" Then
                    BackSlash = i
                    Exit For
                End If
            Next i
            MyName = Mid$(FilePath, BackSlash + 1, Point - BackSlash - 1)
            
            
            wb.ActiveSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            wb.ActiveSheet.PageSetup.CenterHeader = "&B&""Arial Black""&14" & MyName
            wb.ActiveSheet.Columns.AutoFit
            wb.ActiveSheet.Rows.AutoFit
            wb.ActiveSheet.PageSetup.CenterFooter = "Page &P of &N"
            wb.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
            wb.PrintOut
            xlApp.DisplayAlerts = False
            xlApp.Quit
            Kill_Excel
            Sleep (30000)
            Set wb = Nothing
            Set xlApp = Nothing
            FilePath = ""
            MyName = ""

    Kill_Excel Sub

    Sub Kill_Excel()
    
    Dim sKillExcel As String
    
    sKillExcel = "TASKKILL /F /IM Excel.exe"
    Shell sKillExcel, vbHide
    
    End Sub
    'Read more at http://vbadud.blogspot.com/2009/05/kill-residual-excel-process-using-vba.html#GY2OluPpshBSGX6z.99


    Have a great day! Rachel


    Monday, July 7, 2014 7:42 PM

All replies

  • Re:  It came back

    Is there an instance(s) of Excel showing in Windows Task Manager after your code completes?
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:47 PM
    Monday, July 7, 2014 8:50 PM
  • Previously yes, which I thought was the issue. That's why I added the Kill_Excel coding, and called it in the PrintAtmt sub at the end. So in theory it should kill excel before trying to move the file. That's why I can't understand the error.

    Have a great day! Rachel

    Monday, July 7, 2014 8:53 PM
  • Re:  it came back and hangs around

    I am unfamiliar with the "TaskKill" function and how effective it is.
    I do know that if an automated instance of Excel is not setup/used and closed properly that
    "orphan" references can be created to Excel which prevent it from closing...

    The Excel instance that is created is not visible, so there is no "active" sheet.
    Create a reference to the appropriate sheet and use that...
      Dim WS as Excel.Worksheet
      Set WS = wb.Worksheets(1)
    Close the workbook before quitting.
    Set all object references to Nothing (except the xlApp) before quitting.
    After quitting the xlApp, set the xlApp reference to nothing.

    You should not need to use the TaskKill function.
    '---
    Jim Cone

    Monday, July 7, 2014 9:13 PM
  • Yeah I don't know what it's issue is... It's trying to find the files I don't print after moving them as well. I just ran it against some that are just plainly moved it is moving the file to the new folder, then telling me "file not found" in vba. I even tried removing all the "Else If" and putting each report in it's own IF statement and it's still producing the error. I'm tempted to make a separate sub for each report and see if that fixes it instead of having them all listed in the same sub.


    New Sub generating same error, vba is still looking for the file after it's moved.

    Sub MyReportName()
        Dim MyReport As String
        Dim MyFile As String
        Dim MyPath As String
        Dim MyDone As String
        
        MyDone = "C:\Reports\Report Name\"
        MyPath = "C:\Reports\"
        MyFile = Dir("C:\Reports\*.xlsx*")
    
        Do While Len(MyPath & MyFile) > 0
            
            MyReport = InStr(MyFile, "ReportName")
            
            If MyReport = 1 Then
                FileCopy MyPath & MyFile, MyDone & MyFile
                Kill MyPath & MyFile
            ElseIf MyReport = 0 Then
                Exit Sub
            End If
            
        Loop
    
    
    End Sub

    Run-Time Error '53' :

    File Not Found

    When I click Debug it brings me to this line:

    FileCopy MyPath & MyFile, MyDone & MyFile



    Tuesday, July 8, 2014 1:37 PM
  • You can use this function to check file before open:

    Public Function FileExists(filePath As String) As Boolean
    On Error GoTo blad
    If Len(filePath) = 0 Then Exit Function
    FileExists = Len(Dir(filePath, vbDirectory Or vbHidden Or vbSystem)) > 0
    Exit Function
    blad:
    End Function

    You can use FSO to look file for location. Base on objFSO.GetFolder(strFolderPAth).SubFolders


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Tuesday, July 8, 2014 2:34 PM
    Answerer
  • I was able to get my code to work by moving it over into an excel macro enabled workbook, calling it on workbook_open then opening the workbook via batch which I scheduled in Windows scheduler to run 3x a day. So far it seems to be working. I tested it a bunch of times before scheduling it and it hasn't choked.

    Sub AllFiles()
        Dim folderPath As String
        Dim filename As String
        Dim wb As Workbook
      
        folderPath = "C:\Reports\"
        
        
        
        filename = Dir(folderPath & "*.xlsx")
        Do While filename <> ""
          Application.ScreenUpdating = False
            Set wb = Workbooks.Open(folderPath & filename)
             
            'Calling my Sub Reports
            Reports
                
            filename = Dir
        Loop
      Application.ScreenUpdating = True
      Sleep (5000)
    End Sub
    
    Sub Reports()
    'The Excel Names
    Dim MyFile As String
    Dim MyName As String
    Dim MyWorkbook As String
    
    'The Path Names
    Dim Done As String
    Dim ReportA As String
    
    'The Report Names
    Dim SavedReportA As Integer
    Dim PrintedReportB As Integer
    
    
    'Define the active workbook info
    MyName = ActiveWorkbook.FullName
    MyFile = ActiveWorkbook.Name
    MyWorkbook = MyName
    
    'Define the path names
    Done = "C:\Reports\Done\"
    ReportA = "C:\Reports\Client Reports\ReportA\"
    
    'Define the reports
    SavedReportA = InStr(MyFile, "ReportNameA")
    PrintedReportB = InStr(MyFile, "ReportNameB")
    
    'Setup the formatting for the report
        ActiveWorkbook.ActiveSheet.PageSetup.CenterHeader = "&B&""Arial Black""&14" & MyFile
        ActiveWorkbook.ActiveSheet.Columns.AutoFit
        ActiveWorkbook.ActiveSheet.Rows.AutoFit
        ActiveWorkbook.ActiveSheet.PageSetup.CenterFooter = "Page &P of &N"
        ActiveWorkbook.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
    
    'Saved Report A
        If SavedReportA = 1 Then
            ActiveWorkbook.SaveAs ReportA & ActiveWorkbook.Name
            Kill MyWorkbook
            ActiveWorkbook.Close
    
     'Printed Report B
        ElseIf PrintedReportB = 1 Then
            ActiveWorkbook.ActiveSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape 'Is xlPortrait on other reports
            ActiveWorkbook.PrintOut
            ActiveWorkbook.SaveAs Done & ActiveWorkbook.Name
            Kill MyWorkbook
            ActiveWorkbook.Close
    
      End If
    End Sub


    Have a great day! Rachel


    Wednesday, July 9, 2014 4:27 PM