within my macro i wqant a message box to come up requesting the user to
either type in a file name or paste it and then use that file name to open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so i need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same macro on
supplying 10 different filenames

the files will always be located in the same folder as the spreadsheet i am
running the macro on.

thanks in advance
Arnie

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator &
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the user to
either type in a file name or paste it and then use that file name to open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so i need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same macro on
supplying 10 different filenames

the files will always be located in the same folder as the spreadsheet i
am
running the macro on.

thanks in advance
Arnie

Bob thats great thank you however once i select a file i need to pass the
file name to the macro to act on so i'm not sure if it is the open file box i
need

just a msgbox that i can pass the file names to the macro depending on how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator &
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the user to
either type in a file name or paste it and then use that file name to open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so i need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same macro on
supplying 10 different filenames

the files will always be located in the same folder as the spreadsheet i
am
running the macro on.

thanks in advance
Arnie

Ok, but how will you process multiple files, calling the macro with the name
one at a time?

--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
Bob thats great thank you however once i select a file i need to pass the
file name to the macro to act on so i'm not sure if it is the open file
box i
need

just a msgbox that i can pass the file names to the macro depending on how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator
&
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the user to
either type in a file name or paste it and then use that file name to
open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so i
need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same macro
on
supplying 10 different filenames

the files will always be located in the same folder as the spreadsheet
i
am
running the macro on.

thanks in advance
Arnie

Bob Hi sorry if this has taken a while for me to get back to you

i can create a msgbox that i can select "number of Files" say 5 this will
then be passed to a loop which then asks the user for the file name

it goes off and does its job and then the filename box reapears to allow the
second filename to be acted upon and so on until all 5 files have been
processed (all of the files are set out the same way jjust have different
data in them)

Does that make sense?

"Bob Phillips" wrote:

Ok, but how will you process multiple files, calling the macro with the name
one at a time?

--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
Bob thats great thank you however once i select a file i need to pass the
file name to the macro to act on so i'm not sure if it is the open file
box i
need

just a msgbox that i can pass the file names to the macro depending on how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator
&
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the user to
either type in a file name or paste it and then use that file name to
open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so i
need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same macro
on
supplying 10 different filenames

the files will always be located in the same folder as the spreadsheet
i
am
running the macro on.

thanks in advance
Arnie

Hi Arnie

This may not be the most efficient method, but you code modify Bob's code to
make a temporary list of the files, then run your macro reading back each of
the file names in turn as per the following

Dim i As Long, c As Range
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator _
& "*.xls"
If .Show = -1 Then
For i = 1 To .SelectedItems.Count
Sheets("Sheet2").Cells(i, "A") = .SelectedItems(i)
Next i
End If
End With
For Each c In Sheets("Sheet2").Range("A:A")
If c.Value = "" Then Exit Sub
'run your macro here using the value of c as file name
Next

--
Regards
Roger Govier

"Arnie" wrote in message
...
Bob Hi sorry if this has taken a while for me to get back to you

i can create a msgbox that i can select "number of Files" say 5 this will
then be passed to a loop which then asks the user for the file name

it goes off and does its job and then the filename box reapears to allow
the
second filename to be acted upon and so on until all 5 files have been
processed (all of the files are set out the same way jjust have different
data in them)

Does that make sense?

"Bob Phillips" wrote:

Ok, but how will you process multiple files, calling the macro with the
name
one at a time?

--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
Bob thats great thank you however once i select a file i need to pass
the
file name to the macro to act on so i'm not sure if it is the open file
box i
need

just a msgbox that i can pass the file names to the macro depending on
how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path &
Application.PathSeparator
&
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the user
to
either type in a file name or paste it and then use that file name
to
open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so i
need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same
macro
on
supplying 10 different filenames

the files will always be located in the same folder as the
spreadsheet
i
am
running the macro on.

thanks in advance
Arnie

You already have a list Roger, SelectedItems

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & _
Application.PathSeparator & "*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Call myMacro (.SelectedItems(i))
Next i
End If
End With

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Arnie

This may not be the most efficient method, but you code modify Bob's code
to make a temporary list of the files, then run your macro reading back
each of the file names in turn as per the following

Dim i As Long, c As Range
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator _
& "*.xls"
If .Show = -1 Then
For i = 1 To .SelectedItems.Count
Sheets("Sheet2").Cells(i, "A") = .SelectedItems(i)
Next i
End If
End With
For Each c In Sheets("Sheet2").Range("A:A")
If c.Value = "" Then Exit Sub
'run your macro here using the value of c as file name
Next

--
Regards
Roger Govier

"Arnie" wrote in message
...
Bob Hi sorry if this has taken a while for me to get back to you

i can create a msgbox that i can select "number of Files" say 5 this will
then be passed to a loop which then asks the user for the file name

it goes off and does its job and then the filename box reapears to allow
the
second filename to be acted upon and so on until all 5 files have been
processed (all of the files are set out the same way jjust have different
data in them)

Does that make sense?

"Bob Phillips" wrote:

Ok, but how will you process multiple files, calling the macro with the
name
one at a time?

--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
Bob thats great thank you however once i select a file i need to pass
the
file name to the macro to act on so i'm not sure if it is the open
file
box i
need

just a msgbox that i can pass the file names to the macro depending on
how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path &
Application.PathSeparator
&
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the
user to
either type in a file name or paste it and then use that file name
to
open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so
i
need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same
macro
on
supplying 10 different filenames

the files will always be located in the same folder as the
spreadsheet
i
am
running the macro on.

thanks in advance
Arnie

Thanks Bob.

Absolutely stupid of me to write it out elsewhere first of all.
I obviously like making work!!!

--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
You already have a list Roger, SelectedItems

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & _
Application.PathSeparator & "*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Call myMacro (.SelectedItems(i))
Next i
End If
End With

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Arnie

This may not be the most efficient method, but you code modify Bob's code
to make a temporary list of the files, then run your macro reading back
each of the file names in turn as per the following

Dim i As Long, c As Range
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator _
& "*.xls"
If .Show = -1 Then
For i = 1 To .SelectedItems.Count
Sheets("Sheet2").Cells(i, "A") = .SelectedItems(i)
Next i
End If
End With
For Each c In Sheets("Sheet2").Range("A:A")
If c.Value = "" Then Exit Sub
'run your macro here using the value of c as file name
Next

--
Regards
Roger Govier

"Arnie" wrote in message
...
Bob Hi sorry if this has taken a while for me to get back to you

i can create a msgbox that i can select "number of Files" say 5 this
will
then be passed to a loop which then asks the user for the file name

it goes off and does its job and then the filename box reapears to allow
the
second filename to be acted upon and so on until all 5 files have been
processed (all of the files are set out the same way jjust have
different
data in them)

Does that make sense?

"Bob Phillips" wrote:

Ok, but how will you process multiple files, calling the macro with the
name
one at a time?

--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
Bob thats great thank you however once i select a file i need to pass
the
file name to the macro to act on so i'm not sure if it is the open
file
box i
need

just a msgbox that i can pass the file names to the macro depending
on how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path &
Application.PathSeparator
&
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the
user to
either type in a file name or paste it and then use that file name
to
open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on so
i
need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same
macro
on
supplying 10 different filenames

the files will always be located in the same folder as the
spreadsheet
i
am
running the macro on.

thanks in advance
Arnie

You must be a consultant <g

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Thanks Bob.

Absolutely stupid of me to write it out elsewhere first of all.
I obviously like making work!!!

--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
You already have a list Roger, SelectedItems

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & _
Application.PathSeparator & "*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Call myMacro (.SelectedItems(i))
Next i
End If
End With

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Arnie

This may not be the most efficient method, but you code modify Bob's
code to make a temporary list of the files, then run your macro reading
back each of the file names in turn as per the following

Dim i As Long, c As Range
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path & Application.PathSeparator
_
& "*.xls"
If .Show = -1 Then
For i = 1 To .SelectedItems.Count
Sheets("Sheet2").Cells(i, "A") = .SelectedItems(i)
Next i
End If
End With
For Each c In Sheets("Sheet2").Range("A:A")
If c.Value = "" Then Exit Sub
'run your macro here using the value of c as file name
Next

--
Regards
Roger Govier

"Arnie" wrote in message
...
Bob Hi sorry if this has taken a while for me to get back to you

i can create a msgbox that i can select "number of Files" say 5 this
will
then be passed to a loop which then asks the user for the file name

it goes off and does its job and then the filename box reapears to
allow the
second filename to be acted upon and so on until all 5 files have been
processed (all of the files are set out the same way jjust have
different
data in them)

Does that make sense?

"Bob Phillips" wrote:

Ok, but how will you process multiple files, calling the macro with
the name
one at a time?

--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
Bob thats great thank you however once i select a file i need to
pass the
file name to the macro to act on so i'm not sure if it is the open
file
box i
need

just a msgbox that i can pass the file names to the macro depending
on how
many files i need to run through could be 3 files or as many as 10

does that make sense

Arnie

"Bob Phillips" wrote:

Dim i As Long

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.Path &
Application.PathSeparator
&
"*.xls"
If .Show = -1 Then

For i = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(i)
Next i
End If
End With


--
__________________________________
HTH

Bob

"Arnie" wrote in message
...
within my macro i wqant a message box to come up requesting the
user to
either type in a file name or paste it and then use that file
name to
open
the file

also a way of selecting how many files i want the macro to run on

for example run 1 i may have 3 files i want to run the macro on
so i
need
the message box to ask me three times for each filename

the next time i run it i may need to use 10 files to run the same
macro
on
supplying 10 different filenames

the files will always be located in the same folder as the
spreadsheet
i
am
running the macro on.

thanks in advance
Arnie