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

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
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

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" wrote in
message ...

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 coul
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 thin
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 workshee
as they change
occasionally. Also the path to the workbook is variable as that wil
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" wrot
in
message ...

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

If i am correct is there a way to, resume the next task, for th
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 fil
not
found error
and the code will not continue.

I need the code to continue even if it cannot find one of th
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


--
Piranh
-----------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043
View this thread: http://www.excelforum.com/showthread.php?threadid=46702

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" wrote in
message ...

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" wrote
in
message ...

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