File not found - error

Hi,

Have a macro that opens some files one at a time, and does stuff.

If Excel can't find one of the files, it gets i get the VBA file not found error
and the code will not continue.

I need the code to continue even if it cannot find one of the files, with no errpr messages.

I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck.

Sub start

get file do work

file missing

get file do work

get file do work

end sub

Ideas?

Try a variation of this:

Dim File1 As String
Dim FF1 As String

File1 = "C:/Temp/H73FJ.xls"

FF1 = Dir(File1)

If FF1 <> "" Then
Workbooks.Open Filename:=File1
MsgBox "Do something"
Windows(FF1).Close
Else
MsgBox "File doesn't exist"
End If

Hope this helps
Rowan

Piranha wrote:
> Hi,
>
> Have a macro that opens some files one at a time, and does stuff.
>
> If Excel can't find one of the files, it gets i get the VBA file not
> found error
> and the code will not continue.
>
> I need the code to continue even if it cannot find one of the files,
> with no errpr messages.
>
> I've tried variations of "On Error Resume Next" & "On Error Goto
> ErrorTrap" with no luck.
>
> Sub start
>
> get file do work
>
> file missing
>
> get file do work
>
> get file do work
>
> end sub
>
> Ideas?
>
>

Hi,

Yes it is correct that if the file doesnot exists then the code given by
Rowan should work good. but how to create the excel file at run time.
that is,

1. i want to create a file at execution time.

2. if file already exists then overwrite it.

3. Need to fill up the file with some contents.

4. Later to Save the file with a given name.

how this is possible..

thanking you...

"Rowan" wrote:

> Try a variation of this:
>
> Dim File1 As String
> Dim FF1 As String
>
> File1 = "C:/Temp/H73FJ.xls"
>
> FF1 = Dir(File1)
>
> If FF1 <> "" Then
> Workbooks.Open Filename:=File1
> MsgBox "Do something"
> Windows(FF1).Close
> Else
> MsgBox "File doesn't exist"
> End If
>
> Hope this helps
> Rowan
>
> Piranha wrote:
> > Hi,
> >
> > Have a macro that opens some files one at a time, and does stuff.
> >
> > If Excel can't find one of the files, it gets i get the VBA file not
> > found error
> > and the code will not continue.
> >
> > I need the code to continue even if it cannot find one of the files,
> > with no errpr messages.
> >
> > I've tried variations of "On Error Resume Next" & "On Error Goto
> > ErrorTrap" with no luck.
> >
> > Sub start
> >
> > get file do work
> >
> > file missing
> >
> > get file do work
> >
> > get file do work
> >
> > end sub
> >
> > Ideas?
> >
> >
>

Maybe something like this:

Sub AddFile()

Dim newBk As Workbook

On Error GoTo ErrorHandler
Application.DisplayAlerts = False

Set newBk = Workbooks.Add
newBk.Sheets(1).Cells(1) = "yourData"
newBk.SaveAs ("C:\Temp\Newbk.xls")
newBk.Close
Set newBk = Nothing
ErrorHandler:
Application.DisplayAlerts = True
End Sub

Regards
Rowan

Yogeshwar wrote:
> Hi,
>
> Yes it is correct that if the file doesnot exists then the code given by
> Rowan should work good. but how to create the excel file at run time.
> that is,
>
> 1. i want to create a file at execution time.
>
> 2. if file already exists then overwrite it.
>
> 3. Need to fill up the file with some contents.
>
> 4. Later to Save the file with a given name.
>
> how this is possible..
>
> thanking you...
>
> "Rowan" wrote:
>
>
>>Try a variation of this:
>>
>> Dim File1 As String
>> Dim FF1 As String
>>
>> File1 = "C:/Temp/H73FJ.xls"
>>
>> FF1 = Dir(File1)
>>
>> If FF1 <> "" Then
>> Workbooks.Open Filename:=File1
>> MsgBox "Do something"
>> Windows(FF1).Close
>> Else
>> MsgBox "File doesn't exist"
>> End If
>>
>>Hope this helps
>>Rowan
>>
>>Piranha wrote:
>>
>>>Hi,
>>>
>>>Have a macro that opens some files one at a time, and does stuff.
>>>
>>>If Excel can't find one of the files, it gets i get the VBA file not
>>>found error
>>>and the code will not continue.
>>>
>>>I need the code to continue even if it cannot find one of the files,
>>>with no errpr messages.
>>>
>>>I've tried variations of "On Error Resume Next" & "On Error Goto
>>>ErrorTrap" with no luck.
>>>
>>>Sub start
>>>
>>>get file do work
>>>
>>>file missing
>>>
>>>get file do work
>>>
>>>get file do work
>>>
>>>end sub
>>>
>>>Ideas?
>>>
>>>
>>

Hi rowan,
I am working on this. The way i understand is, i have to put your code on every file i am opening.
Is that correct?

If i am correct is there a way to, resume the next task, for the whole macro,
instead of each task seperatly?

Thx for your input.
Dave

Originally Posted by Rowan

Try a variation of this:

Dim File1 As String
Dim FF1 As String

File1 = "C:/Temp/H73FJ.xls"

FF1 = Dir(File1)

If FF1 <> "" Then
Workbooks.Open Filename:=File1
MsgBox "Do something"
Windows(FF1).Close
Else
MsgBox "File doesn't exist"
End If

Hope this helps
Rowan

Piranha wrote:
> Hi,
>
> Have a macro that opens some files one at a time, and does stuff.
>
> If Excel can't find one of the files, it gets i get the VBA file not
> found error
> and the code will not continue.
>
> I need the code to continue even if it cannot find one of the files,
> with no errpr messages.
>
> I've tried variations of "On Error Resume Next" & "On Error Goto
> ErrorTrap" with no luck.
>
> Sub start
>
> get file do work
>
> file missing
>
> get file do work
>
> get file do work
>
> end sub
>
> Ideas?
>
>

Hi Dave

You would need to have this for each book you are opening. If you are
going to perform the same tasks on each book opened you could have these
in a seperate macro which you call from the main routine eg:

Sub GetFiles()
Dim File1 As String
Dim FF1 As String

File1 = "C:/Temp/FirstFile.xls"
FF1 = Dir(File1)
If FF1 <> "" Then
Call DoStuff(File1)
FF1 = ""
End If

File1 = "C:/Temp/AnotherFile.xls"
FF1 = Dir(File1)
If FF1 <> "" Then
Call DoStuff(File1)
FF1 = ""
End If

'etc

End Sub

Sub DoStuff(File1 As String)
Workbooks.Open Filename:=File1
'Perform other tasks on file
'save and close if required
End Sub

Note the main macro "GetFiles" passes the variable File1 to "DoStuff"
which is then used to open the appropriate file.

This bit I am afraid I did not understand:
> If i am correct is there a way to, resume the next task, for the whole
> macro,
> instead of each task seperatly?
The code above will check each file and perform whatever tasks you put
in DoStuff on each file found.

Hope this helps
Rowan

Piranha wrote:
> Hi rowan,
> I am working on this. The way i understand is, i have to put your code
> on every file i am opening.
> Is that correct?
>
> If i am correct is there a way to, resume the next task, for the whole
> macro,
> instead of each task seperatly?
>
> Thx for your input.
> Dave

Hi Dave,

If the processing steps are the same for each workbook opened, then perhaps
try something like:

Sub Tester03A()
Dim arr As Variant
Dim WB As Workbook
Dim i As Long

'Workbooks to open
arr = Array("C:\Book1.xls", "C:\BookB.xls", _
"C:\Book100.xls", "C:\Book200.xls")

'Open, process and close each workbook sequentially
For i = LBound(arr) To UBound(arr)
Set WB = Nothing
On Error Resume Next
Set WB = Workbooks.Open(arr(i))
On Error GoTo 0

If Not WB Is Nothing Then
'Do something, e.g.:
MsgBox WB.Name
WB.Close SaveChanges:=True
Else
'Workbook not found
'Do something else, e.g.:
MsgBox arr(i) & " not found!"
End If
Next i

End Sub
'==================>>

---
Regards,
Norman



"Piranha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi rowan,
> I am working on this. The way i understand is, i have to put your code
> on every file i am opening.
> Is that correct?
>
> If i am correct is there a way to, resume the next task, for the whole
> macro,
> instead of each task seperatly?
>
> Thx for your input.
> Dave
> Rowan Wrote:
>> Try a variation of this:
>>
>> Dim File1 As String
>> Dim FF1 As String
>>
>> File1 = "C:/Temp/H73FJ.xls"
>>
>> FF1 = Dir(File1)
>>
>> If FF1 <> "" Then
>> Workbooks.Open Filename:=File1
>> MsgBox "Do something"
>> Windows(FF1).Close
>> Else
>> MsgBox "File doesn't exist"
>> End If
>>
>> Hope this helps
>> Rowan
>>
>> Piranha wrote:
>> > Hi,
>> >
>> > Have a macro that opens some files one at a time, and does stuff.
>> >
>> > If Excel can't find one of the files, it gets i get the VBA file not
>> > found error
>> > and the code will not continue.
>> >
>> > I need the code to continue even if it cannot find one of the files,
>> > with no errpr messages.
>> >
>> > I've tried variations of "On Error Resume Next" & "On Error Goto
>> > ErrorTrap" with no luck.
>> >
>> > Sub start
>> >
>> > get file do work
>> >
>> > file missing
>> >
>> > get file do work
>> >
>> > get file do work
>> >
>> > end sub
>> >
>> > Ideas?
>> >
>> >
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=467024
>

Rowan,
This sounds good. I am trying to make this work in a workbook.

If you are going to perform the same tasks on each book opened you could have
these in a seperate macro which you call from the main routine eg:

Hi Norman,
Yours sounds good as well. I am also trying to make it work. One thing i don't understand
how to do is, where you have the workbooks to open "hard coded".

I'm calling my workbooks from a name, on a list, on a hidden worksheet as they change
occasionally. Also the path to the workbook is variable as that will change occasionally.

Originally Posted by Norman Jones

Hi Dave,
'Workbooks to open
arr = Array("C:\Book1.xls", "C:\BookB.xls", _
"C:\Book100.xls", "C:\Book200.xls")

'Open, process and close each workbook sequentially
For i = LBound(arr) To UBound(arr)
Set WB = Nothing
On Error Resume Next
Set WB = Workbooks.Open(arr(i))
On Error GoTo 0
---
Regards,
Norman



"Piranha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi rowan,
> I am working on this. The way i understand is, i have to put your code
> on every file i am opening.
> Is that correct?
>
> If i am correct is there a way to, resume the next task, for the whole
> macro,
> instead of each task seperatly?
>
> Thx for your input.
> Dave
> Rowan Wrote:
>> Try a variation of this:
>>
>> Dim File1 As String
>> Dim FF1 As String
>>
>> File1 = "C:/Temp/H73FJ.xls"
>>
>> FF1 = Dir(File1)
>>
>> If FF1 <> "" Then
>> Workbooks.Open Filename:=File1
>> MsgBox "Do something"
>> Windows(FF1).Close
>> Else
>> MsgBox "File doesn't exist"
>> End If
>>
>> Hope this helps
>> Rowan
>>
>> Piranha wrote:
>> > Hi,
>> >
>> > Have a macro that opens some files one at a time, and does stuff.
>> >
>> > If Excel can't find one of the files, it gets i get the VBA file not
>> > found error
>> > and the code will not continue.
>> >
>> > I need the code to continue even if it cannot find one of the files,
>> > with no errpr messages.
>> >
>> > I've tried variations of "On Error Resume Next" & "On Error Goto
>> > ErrorTrap" with no luck.
>> >
>> > Sub start
>> >
>> > get file do work
>> >
>> > file missing
>> >
>> > get file do work
>> >
>> > get file do work
>> >
>> > end sub
>> >
>> > Ideas?
>> >
>> >
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=467024
>

Hi Dave,

Assume that the file names (including the path) list starts in A1 on the
hidden sheet.

Try:
'=====================>>
Sub Tester03B()
Dim arr As Variant
Dim WB As Workbook
Dim rng As Range
Dim rCell As Range
Dim i As Long

Set rng = ThisWorkbook.Sheets("MyHiddenSheet"). _
Range("A1").CurrentRegion.Columns(1) '<<==== CHANGE

'Open, process and close each workbook sequentially
For Each rCell In rng.Cells
Set WB = Nothing
On Error Resume Next
Set WB = Workbooks.Open(rCell.Value)
On Error GoTo 0

If Not WB Is Nothing Then
'Do something, e.g.:
MsgBox WB.Name
WB.Close SaveChanges:=True
Else
'Workbook not found
'Do something else, e.g.:
MsgBox rCell.Value & " not found!"
End If
Next rCell

End Sub
'==================>>

Change "MyHiddenSheet" to accord with the name of your hidden sheet.

---
Regards,
Norman



"Piranha" <[email protected]> wrote in
message news:[email protected]...
>
> Rowan,
> This sounds good. I am trying to make this work in a workbook.
>> If you are going to perform the same tasks on each book opened you could
>> have
>> these in a seperate macro which you call from the main routine eg:
> Hi Norman,
> Yours sounds good as well. I am also trying to make it work. One thing
> i don't understand
> how to do is, where you have the workbooks to open "hard coded".
>
> I'm calling my workbooks from a name, on a list, on a hidden worksheet
> as they change
> occasionally. Also the path to the workbook is variable as that will
> change occasionally.
> Norman Jones Wrote:
>> Hi Dave,
>> 'Workbooks to open
>> arr = Array("C:\Book1.xls", "C:\BookB.xls", _
>> "C:\Book100.xls", "C:\Book200.xls")
>>
>> 'Open, process and close each workbook sequentially
>> For i = LBound(arr) To UBound(arr)
>> Set WB = Nothing
>> On Error Resume Next
>> Set WB = Workbooks.Open(arr(i))
>> On Error GoTo 0
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Piranha" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > Hi rowan,
>> > I am working on this. The way i understand is, i have to put your
>> code
>> > on every file i am opening.
>> > Is that correct?
>> >
>> > If i am correct is there a way to, resume the next task, for the
>> whole
>> > macro,
>> > instead of each task seperatly?
>> >
>> > Thx for your input.
>> > Dave
>> > Rowan Wrote:
>> >> Try a variation of this:
>> >>
>> >> Dim File1 As String
>> >> Dim FF1 As String
>> >>
>> >> File1 = "C:/Temp/H73FJ.xls"
>> >>
>> >> FF1 = Dir(File1)
>> >>
>> >> If FF1 <> "" Then
>> >> Workbooks.Open Filename:=File1
>> >> MsgBox "Do something"
>> >> Windows(FF1).Close
>> >> Else
>> >> MsgBox "File doesn't exist"
>> >> End If
>> >>
>> >> Hope this helps
>> >> Rowan
>> >>
>> >> Piranha wrote:
>> >> > Hi,
>> >> >
>> >> > Have a macro that opens some files one at a time, and does stuff.
>> >> >
>> >> > If Excel can't find one of the files, it gets i get the VBA file
>> not
>> >> > found error
>> >> > and the code will not continue.
>> >> >
>> >> > I need the code to continue even if it cannot find one of the
>> files,
>> >> > with no errpr messages.
>> >> >
>> >> > I've tried variations of "On Error Resume Next" & "On Error Goto
>> >> > ErrorTrap" with no luck.
>> >> >
>> >> > Sub start
>> >> >
>> >> > get file do work
>> >> >
>> >> > file missing
>> >> >
>> >> > get file do work
>> >> >
>> >> > get file do work
>> >> >
>> >> > end sub
>> >> >
>> >> > Ideas?
>> >> >
>> >> >
>> >
>> >
>> > --
>> > Piranha
>> >
>> ------------------------------------------------------------------------
>> > Piranha's Profile:
>> > http://www.excelforum.com/member.php...o&userid=20435
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=467024
>> >
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=467024
>