UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> If Statement File Not Found Error, Access 2013    
 
   
g0049978
post Sep 18 2019, 10:02 AM
Post#1



Posts: 26
Joined: 13-September 19



This VBA is working when the file is there but producing an error when it's not. I want it to produce the msgbox when the file is not found but it errors on line Set wb = ...

Public Sub Savenewclosedreport12()

Dim xcelFile As Excel.Application
Dim wb As Excel.Workbook
Dim strBasePath As String
Dim strFilter As String
Dim sfound As String

Set xcelFile = New Excel.Application

strBasePath = "\\CCX.abc.com\Shares\Public\TATMonitoring\RoutingPointClosedReport\"
strFilter = "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
Format(Now(), "dd") & "*" & "12" & "*" & "AM" & "*" & ".xlsx"

sfound = strBasePath & strFilter
If sfound <> "" Then
Set wb = xcelFile.Workbooks.Open(sfound)
wb.SaveAs FileName:="C:\Users\Public\Sources\Closed12pm.xlsx"
wb.Close
Set xcelFile = Nothing

Else
MsgBox ("No file/wildcard matches")
End If


End Sub
This post has been edited by g0049978: Sep 18 2019, 10:02 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2019, 10:12 AM
Post#2


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


One solution would be to add error handling.

Identify the specific err number raised here (we got the description, but not the number).

In your error handler, add code something like this, using a general err number you need to replace with that specific one raised by this error in your code:

CODE
If Err = 99999 Then
     MsgBox Prompt:="Requested file doesn't exist. Please try a different file.", Buttons:= vbOkOnly, Title:="File Not Found"
     Exit Sub
End if


There are several other ways to write the error handler, and other approaches. Hopefully one will be offered that suits your needs.
This post has been edited by GroverParkGeorge: Sep 18 2019, 10:13 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
g0049978
post Sep 18 2019, 10:14 AM
Post#3



Posts: 26
Joined: 13-September 19



Thank you! This worked:

Public Sub Savenewclosedreport12()

Dim xcelFile As Excel.Application
Dim wb As Excel.Workbook
Dim strBasePath As String
Dim strFilter As String
Dim sfound As String

Set xcelFile = New Excel.Application

strBasePath = "\\CCX.abc.com\Shares\Public\TATMonitoring\RoutingPointClosedReport\"
strFilter = "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
Format(Now(), "dd") & "*" & "10" & "*" & "AM" & "*" & ".xlsx"

sfound = strBasePath & strFilter
If sfound <> "" Then
xcelFile.DisplayAlerts = False
If Err = 1004 Then
MsgBox Prompt:="Requested file doesn't exist. Please try a different file.", Buttons:=vbOKOnly, Title:="File Not Found"
Exit Sub
Set wb = xcelFile.Workbooks.Open(sfound)
End If
Else
wb.SaveAs FileName:="C:\Users\Public\Sources\Closed12pm.xlsx"
wb.Close
Set xcelFile = Nothing


End If

End Sub

This post has been edited by g0049978: Sep 18 2019, 10:18 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2019, 10:32 AM
Post#4


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem.

I prefer to use Error Handlers, rather than inline error checking, partly because the code tends to be cleaner, but this does work.

Continued Success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
g0049978
post Sep 18 2019, 12:41 PM
Post#5



Posts: 26
Joined: 13-September 19



Uggh - spoke too soon - If the file cannot be found, it just exits the sub and doesn't give me the msgbox prompt
Go to the top of the page
 
g0049978
post Sep 18 2019, 01:01 PM
Post#6



Posts: 26
Joined: 13-September 19



I keep getting a Microsoft Excel popup that says Sorry, we couldn't open '\\CCX.abc.com\Shares..... when it can't find the file but I need to suppress this popup!
Go to the top of the page
 
g0049978
post Sep 19 2019, 11:07 AM
Post#7



Posts: 26
Joined: 13-September 19



The following code is still giving me error 1004 - cannot find Excel file - I want to ignore this error... Here is my code:

Public Sub Savenewclosedreport()

DoCmd.SetWarnings (False)
Dim xcelFile As Excel.Application
Dim wb As Excel.Workbook
Dim strBasePath As String
Dim strFilter As String
Dim sfound As String
Excel.Application.DisplayAlerts = False
Set xcelFile = New Excel.Application

strBasePath = "\\CCX.abc.com\Shares\Public\TATMonitoring\RoutingPointClosedReport\"
strFilter = "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
Format(Now(), "dd") & "*" & "12" & "*" & "PM.xlsx"
On Error GoTo Error_Handler
sfound = strBasePath & strFilter
If sfound <> "" Then
On Error GoTo 0
Set wb = xcelFile.Workbooks.Open(sfound)
wb.SaveAs FileName:="C:\Users\Public\Sources\Closed12pm.xlsx"
wb.Close
Set xcelFile = Nothing


End If

Error_Handler: If Not Err.Number = 0 Then Exit Sub
End Sub
Go to the top of the page
 
GroverParkGeorge
post Sep 19 2019, 11:23 AM
Post#8


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


Your error handler has to check the specific error raised and handle it appropriately.

Right now, nothing happens on any error in that Sub.

Error_Handler: If Not Err.Number = 0 Then Exit Sub

Note that if there is no error (i.e. Err.Number = 0) then the sub ends via Exit Sub. If there IS an error (i.e. Err.Number <> 0) then the sub exits in the very next line via End Sub.

So errors are always shown in their raw form, followed by an exit from the sub.

Try a more complete error handler
CODE
Public Sub  Savenewclosedreport()

On Error Goto ErrHandler

    --  all of your other code goes here

ExitProc:

    Exit Sub

ErrHandler:
    If err = 1004 Then
        Resume ExitProc
    Else
        MsgBox "Something Really Bad Happened", vbOkOnly, "Oh No. An Error"
    End if
Exit Sub

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 19 2019, 11:26 AM
Post#9


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


Also, please remove that line: On Error GoTo 0

I'm not sure why it gets used. I guess I need to revisit that because it sort of seems pointless most of the time. Maybe i'm missing something.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
g0049978
post Sep 19 2019, 01:25 PM
Post#10



Posts: 26
Joined: 13-September 19



I placed that in the code and am still getting a Microsoft Excel popup that says: Sorry, we couldn't find "\\CCX.abc.com\Shares\.... - Looks like it's failing at the Set wb line. I need the code to call the next sub if it's not found rather than show me this popup.

Public Sub Savenewclosedreport()

On Error GoTo Error_Handler

DoCmd.SetWarnings (False)
Dim xcelFile As Excel.Application
Dim wb As Excel.Workbook
Dim strBasePath As String
Dim strFilter As String
Dim sfound As String
Excel.Application.DisplayAlerts = False
Set xcelFile = New Excel.Application

strBasePath = "\\CCX.abc.com\Shares\Public\TATMonitoring\RoutingPointClosedReport\"
strFilter = "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
Format(Now(), "dd") & "*" & "12" & "*" & "PM.xlsx"

sfound = strBasePath & strFilter
If sfound <> "" Then

Set wb = xcelFile.Workbooks.Open(sfound)
wb.SaveAs FileName:="C:\Users\Public\Sources\Closed12pm.xlsx"
wb.Close
Set xcelFile = Nothing

ExitProc:

Exit Sub
End If
Error_Handler:
If Err = 1004 Then
Resume ExitProc
Else
MsgBox "Something Really Bad Happened", vbOKOnly, "Oh No. An Error"
End If
Exit Sub
End Sub
This post has been edited by g0049978: Sep 19 2019, 01:27 PM
Go to the top of the page
 
g0049978
post Sep 20 2019, 09:43 AM
Post#11



Posts: 26
Joined: 13-September 19



HELP ME?? iconfused.gif
Go to the top of the page
 
GroverParkGeorge
post Sep 20 2019, 09:54 AM
Post#12


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


Are you sure the file exists and that it is in the correct location?

And it seems I overlooked a different problem in your code, BTW. I'm sorry.

Your code as written will ALWAYS return True at this line.

sfound = strBasePath & strFilter
If sfound <> "" Then

That's because both strBasePath and strFiler will ALWAYS both have at least some characters in them.

strBasePath = "\\CCX.abc.com\Shares\Public\TATMonitoring\RoutingPointClosedReport\"
strBasePath is now a string longer than ""

and the same is true for

strFilter = "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
Format(Now(), "dd") & "*" & "12" & "*" & "PM.xlsx"


That means your code always tries to open "something" whether it exists or not.

And that means you open Excel, then try to load an Excel file whether it exists or doesn't exist. At that point, EXCEL is in control and the error is occurring in Excel. Hence the Excel error message.

So I would FIRST try to locate the Excel file in the designated directory BEFORE trying to open Excel to load it.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
g0049978
post Sep 20 2019, 10:01 AM
Post#13



Posts: 26
Joined: 13-September 19



If the file is there, it opens, no problem but if the file isn't there, I want to exit this sub and call the next one but instead the Excel error pops up.

How do I write the code to first figure out if the Excel file is there or not?
This post has been edited by g0049978: Sep 20 2019, 10:05 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 20 2019, 10:42 AM
Post#14


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


The problem is, as I noted above, that your code currently TRIES to open the Excel file whether it exists or doesn't exist. That conditional will ALWAYS return true because the length of the two string variables will ALWAYS be greater than "", or 0. It's beside the point, therefore, to check it. Excel is launched and tries to open the designated file, failing when it's not found. But that error comes from Excel, not Access.

There are multiple ways to go about checking for the existence of a file. (That's usually the case in Access, btw. We have a plenitude of options for many tasks.)

I would use the basic Dir function to check for the existence of the file before launching Excel and trying to open the file.

Sorry again for missing this key point and delaying your solution. Better luck going forward.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
g0049978
post Sep 20 2019, 11:46 AM
Post#15



Posts: 26
Joined: 13-September 19



I think this might work:

Public Sub Fileexisttest()

Dim FilePath As String
Dim FileExists As String

FilePath = "\\CCX.abc.com\Shares\Public\TATMonitoring\RoutingPointClosedReport\" & "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
Format(Now(), "dd") & "*" & "02" & "*" & "PM.xlsx"


FileExists = Dir(FilePath)

If FileExists = "" Then
MsgBox "Requested closed file doesn't exist. Please check if file is on share-drive and try again."
Else
Call Savenewclosedreport2

End If


End Sub

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 09:29 PM