CODEWITHSUNDEEP
vbaexcel
sbagnato
sbagnato

Reputation: 496

Excel VBA - Create loop for opening files

I have the code below for opening files. Specifically, it prompts to open 12 different files corresponding to each month of the year.

While the code works as is, it seems excessively long and I would assume could be shortened with a loop. However, I am not sure how to write this.

Any help is appreciated.

'Intialize workbooks
    Dim myFile As String, monthSumJan As Workbook, monthSumFeb As Workbook, monthSumMar As Workbook, monthSumApr As Workbook, monthSumMay As Workbook, monthSumJun As Workbook, monthSumJul As Workbook, monthSumAug As Workbook, monthSumSep As Workbook, monthSumOct As Workbook, monthSumNov As Workbook, monthSumDec As Workbook
    Dim Jan As Worksheet, Feb As Worksheet, Mar As Worksheet, Apr As Worksheet, May As Worksheet, Jun As Worksheet, Jul As Worksheet, Aug As Worksheet, Sep As Worksheet, Oct As Worksheet, Nov As Worksheet, Dec As Worksheet
    ChDir "C:\Users\stefan.bagnato\Desktop\Monthly Performance Summary"
    'January
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumJan = ThisWorkbook
    ActiveSheet.name = "Jan"
    Call ECHIBasicMonthlySummary
    'February
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumFeb = ThisWorkbook
    ActiveSheet.name = "Feb"
    Call ECHIBasicMonthlySummary
    'March
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumMar = ThisWorkbook
    ActiveSheet.name = "Mar"
    Call ECHIBasicMonthlySummary
    'April
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumApr = ThisWorkbook
    ActiveSheet.name = "Apr"
    Call ECHIBasicMonthlySummary
    'May
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumMay = ThisWorkbook
    ActiveSheet.name = "May"
    Call ECHIBasicMonthlySummary
    'June
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumJun = ThisWorkbook
    ActiveSheet.name = "Jun"
    Call ECHIBasicMonthlySummary
    'July
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumJul = ThisWorkbook
    ActiveSheet.name = "Jul"
    Call ECHIBasicMonthlySummary
    'August
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumAug = ThisWorkbook
    ActiveSheet.name = "Aug"
    Call ECHIBasicMonthlySummary
    'September
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumSep = ThisWorkbook
    ActiveSheet.name = "Sep"
    Call ECHIBasicMonthlySummary
    'October
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumOct = ThisWorkbook
    ActiveSheet.name = "Oct"
    Call ECHIBasicMonthlySummary
    'November
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumNov = ThisWorkbook
    ActiveSheet.name = "Nov"
    Call ECHIBasicMonthlySummary
    'December
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumDec = ThisWorkbook
    ActiveSheet.name = "Dec"
    Call ECHIBasicMonthlySummary

Upvotes: 1

Views: 787

Answers (2)

Drew Chapin
Drew Chapin

Reputation: 7989

You can store your workbooks in an array if you need to reference them later. 

' Array of Monthly Summary Workbook objects
Dim MonthSum(1 To 12) As Workbook

'Application.GetOpenFileName can return a path as a String, 
' or `False` if the user hits Cancel, so the variable should be a Variant
Dim File As Variant

' Set Current Directory
ChDir "C:\Users\stefan.bagnato\Desktop\Monthly Performance Summary"

' Loop through each month
Dim Month As Integer
For Month = 1 To 12

    ' Show Open File Dialog
    File = Application.GetOpenFilename

    ' Stop the loop if user hits cancel
    If File = False Then
        Exit For
    End If

    ' Open selected file, store Workbook object in the MonthSum array
    ' Workbooks.Open() returns the Worbook object of the opened file. 
    Set MonthSum(Month) = Workbooks.Open(File)

    ' Set Sheet Name, MonthName() returns the name of the month
    ' given its number. The true parameter indicates the name
    ' should be abbreviated to 3 letters
    ActiveSheet.Name = MonthName(Month,True)

    ' Call your custom macro
    Call ECHIBasicMonthlySummary

Next

' Do some other stuff

' Close the Workbooks
For Month = 1 To 12
    ' If User hit Cancel, MonthSum(Month) might not be a workbook object
    ' so check and make sure it's not Nothing before calling Close on it.
    If Not MonthSum(Month) Is Nothing Then 
        MonthSum(Month).Close SaveChanges:=True
    End If
Next

Upvotes: 2

Storax
Storax

Reputation: 12167

A quick shot would be

Dim i As Long
For i = 1 To 12
    myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile
    Set monthSumJan = ThisWorkbook
    Call ECHIBasicMonthlySummary
Next i

Loops are explained here

You are aware that you do not check if the user presses cancel in the dialog GetOpenFilename, aren't you?

UPDATE:

As the complete relevant code is not known do sth like that

Dim actWkb As Workbook
Dim i As Long
For i = 1 To 12
    myFile = Application.GetOpenFilename
    Set actWkb =  Workbooks.Open(myFile)
    Call ECHIBasicMonthlySummary(actWkb)
Next i

and adjust 

Sub ECHIBasicMonthlySummary(ByVal monthSh As Workbook)

' adjust it your code here
' you need to use monthSh

End Sub

Upvotes: 2

Related Questions