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
''''''''''''''''''''''''''''''''''
Dim rngFileNames As Range, rngfilename As Range, wb As Workbook
With Worksheets("sheet2")
Set rngFileNames = .Range("B1")
For Each rngfilename In rngFileNames
''''Open file listed in B1. This is just the file name in B1.
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & rngfilename)''''''''''''''''''''''''''''''''''
'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
''''''''''''''''''''''''''''
So i have to copy the below code
19 times and stick a worksheet code in each one right?
'''''''''''''''''''''''''''''''
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
>
Bookmarks