Open Workbook

[Arnie]

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

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

 

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

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

 

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

 

[Roger Govier]

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

 

[Bob Phillips]

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

 

[Roger Govier]

Thanks Bob.

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

 

[Bob Phillips]

You must be a consultant <g>