CODEWITHSUNDEEP
vbaexcelloops
user6457870
user6457870

Reputation: 247

Loop cells in VBA.

I am trying to make my code better, as every beginner I have problem to make it more "systematic", I would like your advice on how to do it.

I open few workbook, so now my macro looks like this. 

 Sub OpenWorkbooks()
 workbooks.Open Filename :="C/.../file1.xlsx"
 workbooks.Open Filename :="C/.../file2.xlsx"
 workbooks.Open Filename :="C/.../file3.xlsx"
 .
 .
 End sub

Its quite ugly, I would like to have each path in a cell. Let say from A1 to A3 and to loop this cell to open the workbooks. Any idea how I could do this?

In an other part of my code, nicely found on the web, I have the same problem. I would like to be able to enter my paths somewhere in my spreadsheet and then to loop it from there instead of entering manually one by one...

This is the second part of the code, quite clueless how I should do this... 

Sub GetNumber()
Dim wWbPath As String, WbName As String
Dim WsName As String, CellRef As String
Dim Ret As String


Workbooks("file1").Close SaveChanges:=True

wbPath = "C:/etc...."

WbName = "file1.xlsx"
WsName = "Sheet1"
CellRef = "AD30"


arg = "'" & wbPath & "[" & wbName & "]" & _ 
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1) 

Worksheets("Sheet1").Range("A1") = ExecuteExcel4Macro(arg)


'Then I need to do all again for the second workbook etc.... 

End sub

Any idea is welcome, Thank you!

Upvotes: 0

Views: 139

Answers (2)

Xabier
Xabier

Reputation: 7735

To answer the first part of your question:

 Sub OpenWorkbooks()
 For i = 1 to 3 ' Loop 3 times
     Workbooks.Open Filename:=Sheet1.cells(i,1).value 
'Cells refers to Row and column, so i will iterate three times while keeping the column the same.
 Next i
 End sub

If you don't know how many loops you will want to make, you could use the following to check the Last Row with data and loop until you reach it:

Sub OpenWorkbooks()
LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 to LastRow ' Loop as many times until the last row with data
     Workbooks.Open Filename:=Sheet1.cells(i,1).value 
'Cells refers to Row and column, so i will iterate three times while keeping the column the same.
 Next i
 End sub

For the second part of your code you could do something like:

Sub GetNumber()
Dim wWbPath As String, WbName As String
Dim WsName As String, CellRef As String
Dim Ret As String

For i = 1 to 5 'Change this to however many files you will be using
FileName = Sheet1.cells(i,1).value
Workbooks(FileName).Close SaveChanges:=True

wbPath = "C:/etc...."

WbName = FileName & ".xlsx"
WsName = "Sheet1"
CellRef = "AD30"


arg = "'" & wbPath & "[" & wbName & "]" & _ 
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1) 

Worksheets("Sheet1").Range("A" & i) = ExecuteExcel4Macro(arg)


'Then I need to do all again for the second workbook etc.... 
Next i
End sub

Upvotes: 2

freeflyz
freeflyz

Reputation: 1

I had to figure out how do something similar recently. Try this ...

 Dim i As Long
 Dim SelectedFiles As Variant


SelectedFiles = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", _
Title:="Select files", MultiSelect:=True)

 If IsArray(SelectedFiles) Then

    For i = LBound(SelectedFiles) To UBound(SelectedFiles)
        Set wbkToOpen = Workbooks.Open(Filename:=SelectedFiles(i), corruptload:=xlRepairFile)

        Debug.Print wbkToOpen.Name
        Debug.Print SelectedFiles(i)

        wbkToOpen.Close savechanges:=False

    Next

End If

Upvotes: 0

Related Questions