asked on 7/14/2011

Macro that opens a series of workbooks with inconsistent tiles

I need to open a bunch of workbooks whose names are in the defined range"Days" with the values: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday. Most of the workbooks have names like "Monday.xls," and Tuesday.xls". However some of them have names like "WednesdayNext.xls"

I thought it would be fairly simple but when I couldn't get it to work i ended up with this ridiculous clunky code which of course also doesn't work. If you can see what I'm getting at, how would i write the code so that it opens up all seven books regardless of whether the have the 'Next' string in the title?

Thanks,
John

Sub OpenBooks()
Dim cel As Range
For Each cel In [Days]
    Dim dir As String, wb As String
    dir = "C:\Documents and Settings\T0122059\Desktop\XYZBooks\Files\"
On Error GoTo here
    wb = cel & "Next.xls"
    Workbooks.Open Filename:=dir & wb
    [A1] = 22
here:
On Error GoTo there
    wb = cel & ".xls"
    Workbooks.Open Filename:=dir & wb
    [A1] = 22
there:
ThisWorkbook.Activate
Next
End Sub

Last Comment

7/14/2011

7/14/2011

I do not see anything "wrong" with the code. Can you tell how it is "not working"?

Or can you upload the file only with the days range. You can delete everything else.

ASKER CERTIFIED SOLUTION

redmondb

7/14/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

redmondb

7/14/2011

gabrielPennyback,

Previous version will only open one file for each day. If you want multiples (e.g. Monday.xls and MondayNext.xls), please see below.

Regards,
Brian.

Sub OpenBooks()
Dim cel As Range
Dim foundx As Boolean

For Each cel In [Days]
    
    Dim dirx As String
    dirx = "C:\Documents and Settings\T0122059\Desktop\XYZBooks\Files\"
    foundx = False

    If Dir(dirx & cel & "Next.xls") <> "" Then
        Workbooks.Open Filename:=dirx & cel.Value & "Next.xls"
        [A1] = 22
        foundx = True
    End If
    If Dir(dirx & cel & ".xls") <> "" Then
        Workbooks.Open Filename:=dirx & cel & ".xls"
        [A1] = 22
        foundx = True
    End If
    If Not foundx Then MsgBox ("""" & cel & """ not found.")
    
Next

ThisWorkbook.Activate

End Sub

Open in new window

John Carney

7/14/2011

ASKER

Thanks, Brian. This does exactly what I need it to do!

- John

redmondb

7/14/2011

Thanks, John!