OPEN MULTIPLE WORKBOOKS

no attachment

hi,

Here are a couple of options which you may be able to modify to do what you need:

Option Explicit

Sub OpenManyFiles()

Dim sFname As Variant
Dim i As Long

    'Option 1
    sFname = Application.GetOpenFilename( _
             FileFilter:="All Files, *.*, Excel Files, *.xl*;*.xls;*.xlt", _
             FilterIndex:=2, _
             MultiSelect:=True)

    If IsArray(sFname) Then
        For i = LBound(sFname) To UBound(sFname)
            Workbooks.Open Filename:=sFname(i)
        Next i
    End If

    'Option 2
    sFname = ThisWorkbook.Worksheets("Adminstration").Range("List_Of_Files")
    'the above could be written to grab a list from the selected cells on the current sheet eg...
    '    sFname = ThisWorkbook.Worksheets("Adminstration").Selection
    '16/03/2011, RB: +1 is used in the below line instead of just "LBound(sFName)" to prevent unnecessary
    'looping over the header of the dynamic named range (DNR). This is done here instead of changing the DNR
    'to prevent the DNR from erroring if the first data row is deleted.
    For i = LBound(sFname) + 1 To UBound(sFname)
        Workbooks.Open Filename:=sFname(i)
    Next i

End Sub

Select allOpen in new window

hth
Rob

Hi Rob
Option 1
When the macro runs a new window appears asking me to select navigate to the file.

Option 2
I set up the range but the macro produces a run time error 9 as soon as you try to run it

I did try the code below with mixed success as it does open files but as soon as it gets to the already open Master file (containing this macro) it produces a run time error 1004 Method 'Open' of object 'Workbooks' failed

Sub OpenExcelFiles()

    Dim strFilename As String
    Dim strPath As String
    Dim wbkTemp As Workbook
   
    strPath = "S:\CP Production Data\COMMSMASTFOLD\"
    strFilename = Dir(strPath & "*.xls")
    Do While Len(strFilename) > 0
        Set wbkTemp = Workbooks.Open(strPath & strFilename)
        '
        ' apply additional code with the workbook
        '
       
        ' save and close it
        'wbkTemp.Close True
               
        strFilename = Dir
    Loop
   
End Sub

Don't know what to do bext

Rob
Thanks for the perseverance and appreciate being given options both of which work
David

hi David,

Thank you for the feedback & the points :-)

Hopefully I can help "teach you to fish" instead of just giving you a fish!
Here are some points that I considered when giving you the options. Each of the options has different aspects & may be more appropriate for different situations ie:
Option 1:
+ more flexible when the code is run.
+ ensures that the desired files do actually exist because they are selected from existing files.
- requires user interaction during code execution time. To minimise the impact that this has on slowing down the macro, I would put the getopenfilename line of code near the very start of the macro. This means that the user can "start" the macro, select the files, then go & get a coffee or put their feet up & let the rest of the macro run by itself.

Option 2:
+ doesn't require user interaction during code execution time.
+ filenames & paths can be built using appropriate logic in formulae on the spreadsheet.
- there is a risk that the files which are listed in the spreadsheet don't actually exist. It can be worthwhile adding extra error checking to handle such situations so that the code doesn't error & come up with the debug dialog during execution.

The OpenExcelFiles_v2 approach:
+ ensures that the desired files do actually exist because they are selected from existing files.
- I personally consider that using a "strFilename = Dir(strPath & "*.xls")" & identifying the rest using "strFilename = Dir" is undesirable. This is because I use Dir(...) as part of a standard function* to identify if files exist and I think that each time Dir(...) is used, it "resets" it. This would mean that if I was to use the OpenExcelFiles_v2 approach, then the next string created by "strFilename = Dir" could be wrong or could cause an error.

*Here is the function that I use in many of my vba coding projects to identify if a file or folder exists:

Public Function DoesFileFolderExist(strfullpath As String) As Boolean
'25/10/2010, RB: sourced from www.excelguru.ca/node/30 by Ken Puls
'note it only checks for the existence of the lowest folder (or the file) in the strfullpath string.
    On Error GoTo EarlyExit
    If Not Dir(strfullpath, vbDirectory) = vbNullString Then DoesFileFolderExist = True
EarlyExit:
    On Error GoTo 0
End Function

Select allOpen in new window

hth
Rob

Good Morning Rob

It is a very good idea to 'teach to fish' although it appears to require more of your resource ultimately it should result in the novice fishing on his/her own and saving some of your resource.
I realise there are numerous ways of writing code to achieve the same results and everyone has their preferences but to offer options and explanation of those options can not be recommended highly enough and I hope others reading this can take that on board.

Once again - Thanks
David