Open Workbook

  • Thread starter Thread starter Arnie
  • Start date Start date
A

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
 
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
 
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
 
Ok, but how will you process multiple files, calling the macro with the name
one at a time?
 
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?
 
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
 
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
 
Thanks Bob.

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