File not found - error

[Piranha]

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?

 

[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

 

[Yogeshwar]

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]

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

 

[Piranha]

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.

 

[Rowan]

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

 

[Norman Jones]

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

 

[Piranha]

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]

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.

 

[Piranha]

Hi Norman,
I stuck in my "calling the workbook" code, below, in red, There are 1
source workbooks
and it copies the bottom lines to my master workbook.

I don't have a hard coded path cause it will change.

Thx
Dave

 

[Piranha]

Hi Rowan & Norman,

When i look at your codes here on the screen, i can almost make sense
of them. However i have spent many hours trying to get them to work
with my spread sheet, to no avail.

You guys have any other tricks up your sleeves?

Just to resummarize.
1- Sheet2 has the filenames B1:B19 (IE: thisfile.xls)
2- The files are called from the filenames on sheet2 as that is inputed
by user. The names will change by user, so this must be variable..
3- 19 workbooks are the source of the data
4- They are in the same folder, (which will change name, so this must
be variable)
5- The last used row of the 19 worksheeets is copied to specific rows
in the master workbook
NOTE: _ALL_this_works_great._ I just need to fix it so if a line on
sheet2 OR a file is missing from the folder. the code will continue to
run and gather all other data. The way it is now in either of those
happens, a popup for FILE MISSING displays and code stops.

Thank you, both of you guys, for your input you have contributed so
far, Sorry im so dense, on this.

Dave

 

[Norman Jones]

Hi Dave,

Try this minor modification:

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

'Change range and / or sheet details to suit
Set rng = ThisWorkbook.Sheets("Sheet2").Range("B1:B9")

'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
'Your Copy Code
WB.Close SaveChanges:=False
Else
'Workbook not found - Do nothing!
End If
Next rCell

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

 

[Piranha]

Hi Norman,

I just don't understand. See below in red. If i take the "set" part out
and put it on top that don't work. i've tried changing the rngFileName
stuff to rcell, i'v tried changing the ranges, taking out the range
stuff for sheet2 in my code, and a bunch more.
I have 19 of the codes like the red one below, one after the other.

Hi Dave,

Try this minor modification:

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

'Change range and / or sheet details to suit
Set rng = ThisWorkbook.Sheets("Sheet2").Range("B1:B9")

'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
'Your Copy Code
''##########
'This is copying workbook # 1
Dim rngFileNames As Range, rngfilename As Range, wb As Workbook
'Selecting filename list of filenames on sheet 2.
With Worksheets("sheet2")
Set rngFileNames = .Range("B1")
'The Range above advances one row for each workbook name
"IE the next block of code will say ("B2")
For Each rngfilename In rngFileNames
'Open file listed in B1.
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" &
rngfilename)
'Copy data from file
LastRow = Range("C65536").End(xlUp).Row
Range("A" & LastRow).EntireRow.Copy
'Select file, range, to paste to.
Windows("WeeklyNumbers_19_ForMM.xls").Activate
Range("A6").Select
'The range above advances two lines for each workbook.
'IE workbook # 2 will be ("A8") etc
ActiveSheet.Paste
'Finalize paste.
Application.CutCopyMode = False
Range("F1").Select
'Close file copied from.
wb.Close
Next
End With
''##########
WB.Close SaveChanges:=False
Else
'Workbook not found - Do nothing!
End If
Next rCell

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

[/QUOTE]

 

[Norman Jones]

Hi Dave,

I just don't understand. See below in red.

Reading plain text NG posts, I (and most contributors to the NG) am unable
to see your 'red' data.

In your previous post you said:

NOTE: _ALL_this_works_great._ I just need to fix it so if a line on
sheet2 OR a file is missing from the folder. the code will continue to
run and gather all other data. The way it is now in either of those
happens, a popup for FILE MISSING displays and code stops

If you wish, send me a copy of the master file with the
'ALL_this_works_great code' and a typical example of one of the 19
subsidiary workbooks. By all means change // remove any sensitive data.

---
Regards,
Norman

Hi Norman,

I just don't understand. See below in red. If i take the "set" part out
and put it on top that don't work. i've tried changing the rngFileName
stuff to rcell, i'v tried changing the ranges, taking out the range
stuff for sheet2 in my code, and a bunch more.
I have 19 of the codes like the red one below, one after the other.

[/QUOTE]

 

[Piranha]

Norman,
Whats the url where you can do this?

Hi Dave,
Reading plain text NG posts, I (and most contributors to the NG) a
unable
to see your 'red' data.

Regards,
Norman



"Piranha" <[email protected]> wrot
in
message [/url

 

[Norman Jones]

Hi Dave,

See the following comprehensive post from Dave Peterson:

http://tinyurl.com/b6oyc


---
Regards,
Norman

Norman,
Whats the url where you can do this?

Hi Dave,
Reading plain text NG posts, I (and most contributors to the NG) am
unable
to see your 'red' data.

Regards,
Norman



in
message [/url]

 

[Piranha]

Hi Dave,
See the following comprehensive post from Dave Peterson:
http://tinyurl.com/b6oyc
---
Regards,
Norman

"Piranha" <[email protected]> wrot
in
message news[email protected]...

Norman,
Whats the url where you can do this?

Hi Dave,
Reading plain text NG posts, I (and most contributors to the NG) am
unable
to see your 'red' data.

Regards,
Norman

"Piranha" <[email protected] wrote
in
message [/url] --
Piranha

------------------------------------------------------------------------
Piranha's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=20435
View this thread http://www.excelforum.com/showthread.php?threadid=467024
Hi Norman,

Thanks for the link, very interesting.

For anyone interested in this thread. Norman has furnished me with th
following code,
which solves all my problems. It works flawlessly.
Thank you very very much Norman.

Code
-------------------
‘===================>>
Sub CopyPasteStoreData()
Dim rngFileNames As Range
Dim rCell As Range
Dim WB As Workbook
Dim filelistSH As Worksheet
Dim copySH As Worksheet
Dim destSH As Worksheet
Dim RngCopy As Range
Dim RngDest As Range
Dim LastRow As Long
Dim iCtr As Long
ActiveSheet.Unprotect password:="xxx"
'Initially, delete old data!!
ThisWorkbook.Sheets(1).Range("List").ClearContents
Application.DisplayAlerts = False
Application.ScreenUpdating = False
With ThisWorkbook
Set filelistSH = .Sheets("Sheet2")
Set destSH = .Sheets("sheet1")
End With
With filelistSH
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set rngFileNames = .Range("B1").Resize(LastRow)
End With
For Each rCell In rngFileNames.Cells
If Not IsEmpty(rCell) Then
'Open file listed in B1.
On Error Resume Next 'In case file not found!
Set WB = Nothing
Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & rCell.Value)
On Error GoTo 0
If Not WB Is Nothing Then
Set copySH = WB.Sheets(1)
Set RngCopy = copySH.Cells(Rows.Count, "C").End(xlUp).EntireRow
Set RngDest = destSH.Range("A6").Offset(iCtr)
RngCopy.Copy Destination:=RngDest
'Close file copied from.
WB.Close savechanges:=False
iCtr = iCtr + 2
End If
End If
Next rCell
destSH.Range("F1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveSheet.Protect , password:="xxx"
End Sub
‘<<====================

 

[Norman Jones]

Hi Dave,

As a minor addendum, should you wish to hide the deletion of historic data
from the user, move the line ( and comment):

'Initially, delete old data!!
ThisWorkbook.Sheets(1).Range("List").ClearContents

down two lines, so that it follows the instruction:

Application.ScreenUpdating = False

---
Regards,
Norman

Hi Dave,
See the following comprehensive post from Dave Peterson:
http://tinyurl.com/b6oyc
---
Regards,
Norman

in
message news[email protected]...

Norman,
Whats the url where you can do this?
DaveNorman Jones Wrote:
Hi Dave,
Reading plain text NG posts, I (and most contributors to the NG) am
unable
to see your 'red' data.

Regards,
Norman

in
message [/url]
--
Piranha
------------------------------------------------------------------------
Piranha's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=20435
View this thread: http://www.excelforum.com/showthread.php?threadid=467024
Hi Norman,

Thanks for the link, very interesting.

For anyone interested in this thread. Norman has furnished me with the
following code,
which solves all my problems. It works flawlessly.
Thank you very very much Norman.

Code:
--------------------
'===================>>
Sub CopyPasteStoreData()
Dim rngFileNames As Range
Dim rCell As Range
Dim WB As Workbook
Dim filelistSH As Worksheet
Dim copySH As Worksheet
Dim destSH As Worksheet
Dim RngCopy As Range
Dim RngDest As Range
Dim LastRow As Long
Dim iCtr As Long
ActiveSheet.Unprotect password:="xxx"
'Initially, delete old data!!
ThisWorkbook.Sheets(1).Range("List").ClearContents
Application.DisplayAlerts = False
Application.ScreenUpdating = False
With ThisWorkbook
Set filelistSH = .Sheets("Sheet2")
Set destSH = .Sheets("sheet1")
End With
With filelistSH
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set rngFileNames = .Range("B1").Resize(LastRow)
End With
For Each rCell In rngFileNames.Cells
If Not IsEmpty(rCell) Then
'Open file listed in B1.
On Error Resume Next 'In case file not found!
Set WB = Nothing
Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & rCell.Value)
On Error GoTo 0
If Not WB Is Nothing Then
Set copySH = WB.Sheets(1)
Set RngCopy = copySH.Cells(Rows.Count, "C").End(xlUp).EntireRow
Set RngDest = destSH.Range("A6").Offset(iCtr)
RngCopy.Copy Destination:=RngDest
'Close file copied from.
WB.Close savechanges:=False
iCtr = iCtr + 2
End If
End If
Next rCell
destSH.Range("F1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveSheet.Protect , password:="xxx"
End Sub
'<<=====================

 

[Piranha]

Norman,
Yes that works GREAT.
Thanks very much.
Dave

Hi Dave,

As a minor addendum, should you wish to hide the deletion of histori
data
from the user, move the line ( and comment):

'Initially, delete old data!!
ThisWorkbook.Sheets(1).Range("List").ClearContents

down two lines, so that it follows the instruction:

Application.ScreenUpdating = False

---
Regards,
Norman



"Piranha" <[email protected]> wrot
in
message news[email protected]...

Hi Dave,
See the following comprehensive post from Dave Peterson:
http://tinyurl.com/b6oyc
---
Regards,
Norman

"Piranha" <[email protected] wrote
in
messag
Norman,
Whats the url where you can do this?
DaveNorman Jones Wrote:
Hi Dave,
Reading plain text NG posts, I (and most contributors to the NG am
unable
to see your 'red' data.

Regards,
Norman

"Piranha" <[email protected]>
wrote
in
messag [/url]

------------------------------------------------------------------------

Piranha's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=20435
View this thread:
http://www.excelforum.com/showthread.php?threadid=467024
Hi Norman,

Thanks for the link, very interesting.

For anyone interested in this thread. Norman has furnished me wit the
following code,
which solves all my problems. It works flawlessly.
Thank you very very much Norman.

Code:
--------------------
'===================>>
Sub CopyPasteStoreData()
Dim rngFileNames As Range
Dim rCell As Range
Dim WB As Workbook
Dim filelistSH As Worksheet
Dim copySH As Worksheet
Dim destSH As Worksheet
Dim RngCopy As Range
Dim RngDest As Range
Dim LastRow As Long
Dim iCtr As Long
ActiveSheet.Unprotect password:="xxx"
'Initially, delete old data!!
ThisWorkbook.Sheets(1).Range("List").ClearContents
Application.DisplayAlerts = False
Application.ScreenUpdating = False
With ThisWorkbook
Set filelistSH = .Sheets("Sheet2")
Set destSH = .Sheets("sheet1")
End With
With filelistSH
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set rngFileNames = .Range("B1").Resize(LastRow)
End With
For Each rCell In rngFileNames.Cells
If Not IsEmpty(rCell) Then
'Open file listed in B1.
On Error Resume Next 'In case file not found!
Set WB = Nothing
Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & rCell.Value)
On Error GoTo 0
If Not WB Is Nothing Then
Set copySH = WB.Sheets(1)
Set RngCopy = copySH.Cells(Rows.Count, "C").End(xlUp).EntireRow
Set RngDest = destSH.Range("A6").Offset(iCtr)
RngCopy.Copy Destination:=RngDest
'Close file copied from.
WB.Close savechanges:=False
iCtr = iCtr + 2
End If
End If
Next rCell
destSH.Range("F1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveSheet.Protect , password:="xxx"
End Sub
'<<=====================

http://www.excelforum.com/showthread.php?threadid=467024