Loop cells in VBA.

Question

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!

If the folder path is always the same, can just use the Dir Function to loop through the files in that folder. Otherwise yeah, you could loop through each cell in a range. — tigeravatar
– tigeravatar, Commented Dec 5, 2017 at 16:07

Xabier · Accepted Answer · 2017-12-06 09:51:55Z

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

Thank you for your help! Any Idea how I could do it on my second macro? I have a last question for you. When I am closing I am using 'Workbooks("file1").Close SaveChanges:=True 'Workbooks("file2").Close SaveChanges:=True How could I make it efficent with a loop too? Its quite tricky to me too..
Unfortunately no. But the file are so big if I open more than 5 I ll make everything crash. So I ll do it 5 by 5, so you can assume its 5 for now.
Thanks again for these amazing answers... but unfortunately 'file1' was a generic name, it can be anything. It can be apple theb for file2 = pear. That was my stuggle... I should probalby use the path already present and crop ".xlsx" and everything before the last "\"...
Well if you have all the file names stored in a column, you could use a similar For Loop as previously mentioned... I'll update my answer...

freeflyz · Accepted Answer · 2017-12-05 17:29:24Z

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

Collectives™ on Stack Overflow

Loop cells in VBA.

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related