Solved

OPEN MULTIPLE WORKBOOKS

Posted on 2013-11-10
7
934 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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