0

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!

user6457870
  • 247
  • 5
  • 14
  • If the folder path is always the same, can just use the [Dir Function](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/dir-function) to loop through the files in that folder. Otherwise yeah, you could [loop through each cell in a range](https://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object). – tigeravatar Dec 05 '17 at 16:07

2 Answers2

2

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
Xabier
  • 7,587
  • 1
  • 8
  • 20
  • 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.. – user6457870 Dec 05 '17 at 16:43
  • 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. – user6457870 Dec 05 '17 at 16:52
  • Updated my answer to reflect your comments. – Xabier Dec 05 '17 at 16:55
  • 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 "\"... – user6457870 Dec 05 '17 at 17:10
  • 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... – Xabier Dec 05 '17 at 17:11
  • No worries, I understand it. That's perfecct. Thank you. – user6457870 Dec 05 '17 at 17:13
0

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
freeflyz
  • 1
  • 3