Solved

Run-time error '53':  File Not Found

Posted on 2003-12-01
20
1,664 Views
Last Modified: 2010-08-05
I get an error "File Not Found" when I try and run the Shell command (below).

The file does exist (I checked) so I am not sure what the problem is.

If I copy the first parameter and paste it into my immediate window, I get the following:

?"excel.exe " & strCommFilePath
excel.exe C:\Reports\Commission-12-01-03-HeidiThorn.xls


If I take the output and go START--RUN and paste the command in, it opens the document in Excel!!!!!!


Desperate for help here!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdEditCommissionReport_Click()
    Dim strCommFilePath As String
   
    Dim hInstance As Long
    Dim hProcess As Long
    Dim dwExitCode As Long
   
    Dim strExecName As String
   
    Dim lngCount As Long
         
    strExecName = Nz(Me.ListAENotPosted.Column(0), "")
     
    strCommFilePath = Replace("C:\Reports\Commission-" & Format(Date, "mm-dd-yy") & "-" & strExecName & ".xls", " ", "")
     
    If Len(Dir(strCommFilePath)) = 0 Then
           
        sbCreateCommissionReportPassThrough CStr(Me.Text0), CStr(Me.Text2), DLookup("directorID", "tblDirector", "fullname='" & strExecName & "'"), CDbl(Nz(Me.txtAECustomCommission, 1)), CDbl(Nz(Me.txtADCustomCommission, 1))
       
       
        lngCount = Nz(DCount("BuyerCode", "qryCommissionReportJanuaryPassThrough"), 0)
        If lngCount > 0 Then
            DoCmd.OutputTo acOutputQuery, "qryCommissionReportJanuary", acFormatXLS, strCommFilePath
            sbAddTotalsToExcelReport strCommFilePath
            DoEvents
           



  'ERROR:  File Not Found '53'
            hInstance = Shell("excel.exe " & strCommFilePath, vbNormalFocus)
  'ERROR:  File Not Found '53'
           
     
           
            hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hInstance)
            Do
                GetExitCodeProcess hProcess, dwExitCode
                DoEvents
            Loop While dwExitCode = STILL_ACTIVE
            sbGetAEandADCommissionsFromExcel
        Else
            MsgBox "No records to process for this person!"
        End If
    Else
        hInstance = Shell("excel.exe " & strCommFilePath, vbNormalFocus)
        hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hInstance)
        Do
          GetExitCodeProcess hProcess, dwExitCode
          DoEvents
        Loop While dwExitCode = STILL_ACTIVE
        sbGetAEandADCommissionsFromExcel
    End If
   
End Sub
0
Comment
Question by:knowlton
  • 11
  • 8
20 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9852164
Hey knowlton!

http://www.mvps.org/access/api/api0018.htm shows an API routine that can be used to open ALL KINDS of registerd file types easily

  fHandleFile strCommFilePath, win_normal

I have yet to have this fail on me.

regards
Jack
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9852167
I am only guessing here but I think you should wrap the filepath in single quotes as I think the command line is doing it for you but shell does not.

hInstance = Shell("excel.exe '" & strCommFilePath & "'", vbNormalFocus)

Steve
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9852188
I would also use YYMMDD as the format string parameter to eliminate the dashes from the file name.  Not an error cause, just extra characters
0
 
LVL 5

Author Comment

by:knowlton
ID: 9852361
UPDATE:


I had to supply the FULL path to the excel.exe.
0
 
LVL 5

Author Comment

by:knowlton
ID: 9852363
Now it works.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9852547
Hey knowlton!

  The last question you posted has a link to MVPS.org where in lies the answer to this and the last question

  The fHandle file API function will automatically determine the default application for a filetype, find that application via the registry and open the file on screen.

  Again that address is:
  http://www.mvps.org/access/api/api0018.htm


regards
Jack
0
 
LVL 5

Author Comment

by:knowlton
ID: 9852639
What about returning the file path (if I need that)?
0
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 9852688
the api0018.htm doesnt' return the file path but....

http://www.mvps.org/access/api/api0006.htm
might have some value for you also.  (the above is stated to be for Acc97, but I have discovered that many of the API calls have not significantly changed...)
0
 
LVL 5

Author Comment

by:knowlton
ID: 9852847
Jack:

The code provided at:

 http://www.mvps.org/access/api/api0018.htm

compiles and runs okay...but there is something strange about the HANDLE value that is being returned.

I keeps coming back 5 time after time.  I think this is an error code, one that is not trapped by the code.

 hInstance = Shell("excel.exe " & strCommFilePath, vbNormalFocus)

hInstance = 1444 (then 1664 the next time, a different number every time)


hInstance = fHandleFile("excel.exe" & strCommFilePath, 2)

hInstance = 5 (every time)

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9852921
your fHandle file call should look more like this...

  fHandleFile strCommFilePath, win_normal

What are you using the hInstance var for?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:knowlton
ID: 9852946
hInstance is used to open a process and then that process is checked for an exit code (so I can detect when the spreadsheet is not in use anymore)
0
 
LVL 5

Author Comment

by:knowlton
ID: 9853010
Even so, it should be opening the Excel spreadsheet in a window, which it is not.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9853057
I don't know if you will get a result code back from the fHandleFile...

try this:
  fHandleFile "C:\Reports\Commission-12-01-03-HeidiThorn.xls", win_normal
in the debug window after a compile and save.


0
 
LVL 5

Author Comment

by:knowlton
ID: 9853512
fHandleFile "C:\Reports\Commission-12-01-03-HeidiThorn.xls", win_normal


works just fine.
0
 
LVL 5

Author Comment

by:knowlton
ID: 9853518
The whole problem looks like I need to have that file handle passed back from fHandleFile is all.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9853701
I think I see what you are doing.... You are holding up the next report until the current report window is closed.

Can I offer a suggestion:
  Run all the report xls files to the common directory.
  Use a listbox to show all the reports run with the same date in the file name

**The following function fills a table that is the rowsource for a listbox showing the reports recently created by a Excel workbook generator that creates approximately 40 to 60 workbook reports.  The table is located in the user interface and has only one field.
 
Public Function FillRosterList()

  Dim x
  Dim strList
  Dim edb As Database
  Dim ers As Recordset
  Dim FilePattern
  Dim RosterFolder
  Dim YM
  On Error GoTo FillRosterList_Err
 
  RosterFolder = EligFolder & "Reports\"
 
  Set edb = CurrentDb()
  edb.Execute "DELETE * FROM tRefPRFileNames WITH OWNERACCESS OPTION;"
  YM = Forms.fRosterBuilder("txtEligYrMon")
 
  Set ers = edb.OpenRecordset("tRefPRFileNames", dbOpenDynaset)
  If Forms("fRosterBuilder")("optgrpRosterType") = 1 Then
    FilePattern = "MR_" & YM & "_????*.xls"
  Else
    FilePattern = "PR_" & YM & "_????.xls"
  End If
   
  x = Dir$(RosterFolder & FilePattern)
 
  If Len(x) = 0 Then
    GoTo FillRosterList_Exit
  Else
    ers.FindFirst "[PRFileName]='" & x & "'"
    If ers.NoMatch Then
      ers.AddNew
      ers("PRFileName") = x
      ers("PRFileSize") = FileLen(RosterFolder & x)
      ers.Update
    End If
    'strList = Chr(34) & x & Chr(34) & ";"
  End If
 
  Do While Len(x) > 0
    x = Dir
    If Len(x) > 0 Then
      'strList = strList & Chr(34) & x & Chr(34) & ";"
      ers.FindFirst "[PRFileName]='" & x & "'"
      If ers.NoMatch Then
        ers.AddNew
        ers("PRFileName") = x
        ers("PRFileSize") = FileLen(RosterFolder & x)
        ers.Update
      End If
    End If
  Loop

FillRosterList_Exit:
  Exit Function
 
FillRosterList_Err:
  Select Case Err
  Case Else
    ErrMsg "bas_App_EligFileMgmt.FillRosterList", Error$, Err, True
    Resume FillRosterList_Exit
    Resume
  End Select
 
End Function

**The following code open the workbooks selected in the listbox to the screen:

Private Sub cmdOpenRosters_Click()
 
  Dim MR
  Dim selectcount
  Dim vMRFolder
 
  On Error GoTo cmdOpenRosters_Click_Err
 
  vMRFolder = EligFolder & "Reports\"
 
  selectcount = Me("lstRosters").ItemsSelected.Count
  If selectcount = 0 Then
    Exit Sub
  Else
    If MsgBox("You have selected " & selectcount & " roster(s) for viewing." & vbCrLf & "Do you wish to continue?", vbYesNo + vbCritical, "View Rosters") = vbNo Then
      Exit Sub
    End If
  End If
 
  For Each MR In Me("lstRosters").ItemsSelected
    fHandleFile vMRFolder & Me("lstRosters").ItemData(MR), WIN_NORMAL
  Next MR

cmdOpenRosters_Click_Exit:
  Exit Sub
 
cmdOpenRosters_Click_Err:
  Select Case Err
  Case Else
    ErrMsg Me.Name & ".cmdOpenRosters_Click_Click", Error$, Err, True
    Resume cmdOpenRosters_Click_Exit
    Resume
  End Select
End Sub
0
 
LVL 5

Author Comment

by:knowlton
ID: 9854357
I think I see what you are doing.... You are holding up the next report until the current report window is closed.

Correct, that is my intent.

Okay...now back to reading your post.

Tom
0
 
LVL 5

Author Comment

by:knowlton
ID: 9854378
Jack:

The nature of the reports requires that each one be run in turn.  I cannot run them all at once.

Here is the flow:

1)  Pick person to run report for
2)  Change commission percentages for that person
3)  Generate query
4)  Build XLS from that query
5)  Open the XLS
6)  Make custom changes (not knowable until runtime)
7)  Changes impact the Totals at the bottom of XLS.
8)  Close XLS.
9)  Open XLS grab new totals.
10)  Display new totals in main form

The reason for step 8 is I fear that the last change made may not be reflected in the total if the XLS is closed while the changed field still has focus.

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9854385
just so you know,... the code in the my previous post was cut directly and pasted here for example purposes.  It is not intended to be pasted into your application directly...  There are several function dependencies that you will NOT have in your app (see ErrMsg function call)
0
 
LVL 5

Author Comment

by:knowlton
ID: 9854391
Understood, thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now