Solved

OPEN MULTIPLE WORKBOOKS

Posted on 2013-11-10
7
602 Views
Last Modified: 2013-11-12
Hi
I have a workbook with multiple worksheets with a macro that extracts data from each worksheet, the data is than pasted into the extract' worksheet in the same workbook.

A change in requirements has resulted in the sheets being moved into new workbooks with each workbook being renamed daily with the suffix of the date.
All workbooks will remain in the same folder
There is still a requirement for the data to be collected in the COMMSMAST - Copy workbook

I have attached an abridged version of the model showing how the core LOOKUP value in column A of the 'LIST COMMS' sheet  will need to be changed to the format shown in column I of the sheet 'ADMINISTRATION'

The present macro works perfectly, all I need help with is how to include the instruction  to OPEN each of the workbooks shown in column B of the 'ADMINISTRATION' before the existing macro is applied.
Thanks
David
0
Comment
Question by:DAVID131
  • 3
  • 3
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39637374
no attachment
0
 
LVL 10

Expert Comment

by:broro183
ID: 39638400
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

Open in new window


hth
Rob
0
 

Author Comment

by:DAVID131
ID: 39638903
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 39639876
hi David,

Yes, option 1 does bring up a dialogbox, this allows for more flexibility for the user to choose specific files. This method allows for multiple files within the same folder to be chosen. I've added some extra code which may save you from having to browse through different folders. This method removes the need for checking which workbooks are actually being opened.

Sorry, I've also fixed a mistake in the syntax I had written for Option 2 (I fell for one of the risks of copying & pasting from old work!).

To overcome the issue that you were having in your OpenExcelFiles sub I have added an If statement which checks the full name of the file that is being opened.

Hopefully one of these v2 options helps you...

Option Explicit

Sub OpenManyFiles_v2()
Const strPath As String = "S:\CP Production Data\COMMSMASTFOLD\"
Dim OriPath    ' Original path
Dim sFname As Variant
Dim i As Long

    'Option 1
    'v2: identify the current directory & then change it to the desired path before using getopen filename
    OriPath = CurDir
    ChDir strPath
    'or    ChDir ThisWorkbook.Path
    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

    'v2: change back to user's original path
    ChDir OriPath

    '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 = 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)
        'v2: corrected the syntax
        Workbooks.Open Filename:=sFname(i, 1)
    Next i

End Sub

Sub OpenExcelFiles_v2()

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
        'v2: test added
        If strPath & strFilename <> ThisWorkbook.FullName Then
            Set wbkTemp = Workbooks.Open(strPath & strFilename)
            '
            ' apply additional code with the workbook
            '

            ' save and close it
            'wbkTemp.Close True

            strFilename = Dir
        Else
            'do nothing because it is the Masterfile
        End If
    Loop

End Sub

Open in new window


hth
Rob
0
 

Author Closing Comment

by:DAVID131
ID: 39642504
Rob
Thanks for the perseverance and appreciate being given options both of which work
David
0
 
LVL 10

Expert Comment

by:broro183
ID: 39643234
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

Open in new window


hth
Rob
0
 

Author Comment

by:DAVID131
ID: 39643830
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now