Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

OPEN MULTIPLE WORKBOOKS

Posted on 2013-11-10
7
Medium Priority
?
1,407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Accepted Solution

by:
broro183 earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

722 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