loop through excel files and open workbook

MachuPichu92

New Member
Joined
Feb 4, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I'm facing issues with the my simple code below.
I want to create a loop that would open a specific excel file, copy cell value and paste it to another workbook.

See my code:

VBA Code:
Dim TWB As Workbook
Dim WB As Workbook
Dim WS As Worksheet
Dim LR As Long
Dim LC As Long
ReDim FILE(6)
Dim i As Long
Dim DIR As String


LR = Range("A" & Rows.Count).End(xlUp).Row
LR = LR + 1


Set TWB = ThisWorkbook
Set WS = TWB.Worksheets("List1")

DIR = "C:\Users\VB\Desktop\V\"
FILE1 = DIR & "test1.xlsx"
FILE2 = DIR & "test2.xlsx"
FILE3 = DIR & "test3.xlsx"
FILE4 = DIR & "test4.xlsx"
FILE5 = DIR & "test5.xlsx"
FILE6 = DIR & "test6.xlsx"



For i = 1 To 6

    Set WB = Workbooks.Open(FILE & "i")
    
    ActiveWorkbook.Sheets("Sheet1").Cells(1, 1).Select

        If Cells(1, 1).Value <> "" Then
        Selection.Copy

        TWB.Activate

        WS.Cells(LR, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
        LR = LR + 1

        WB.Close
        
        Else:

        WB.Close

        End If
        
Next i

End Sub

When I set Workbooks.Open(FILE & "i") macro looks for FILE1.xlsx file instead of FILE1, but if I insert Workbooks.Open(FILE1) it successfully opens correct file.


Thank you for your answers.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
  1. 365
Platform
  1. Windows
FILE is an array and values need to be assigned to the array elements.

e.g.
FILE(1)="test1.xlsx"
FILE(2)="test2.xlsx"

You will need to refer to the array element when opening the file.

e.g. Set WB = Workbooks.Open(FILE(i))

You dont need to select and copy values from cells.

You can make a reference to the 'from cell' and assign the value to the 'to cell'.

e.g. Workbooks("TOWORKBOOK").Sheets("Sheet1").Range("A1") = Workbooks("FROMWORKBOOK").Sheets("Sheet1").Range("A1")

Hope that this helps somewhat.

I could rewrite it all for you but then you would have nothing to do for the rest of the day!!

Take care and stay safe.
 

CephasOz

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi MachuPichu92. There are a few things with your code that are not "good form", such as using a VBA reserved word (DIR) as a variable name, and SELECTing and ACTIVATEing when you don't have to.

Anyway, have a look at the re-write below, and see what you think. (It is untested, but it looks right to me.)

VBA Code:
Sub DoStuff()
    Const FileDir As String = "C:\Users\VB\Desktop\V\"
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim LR As Long
    Dim i As Integer
    Dim FileToUse As String
    '
    Set WS = ThisWorkbook.Worksheets("List1")
    LR = WS.Range("A" & CStr(WS.Rows.Count)).End(xlUp).Row
    For i = 1 To 6
        FileToUse = FileDir & Replace("test~.xlsx", "~", CStr(i))
        Set WB = Workbooks.Open(Filename:=FileToUse, ReadOnly:=True)
        With WB.Sheets("Sheet1").Cells(1, 1)
            If CStr(.Value) <> vbNullString Then
                LR = LR + 1
                WS.Cells(LR, 1).Value = .Value
            End If
        End With
        WB.Close
    Next i
    Set WB = Nothing
    Set WS = Nothing
End Sub
 

MachuPichu92

New Member
Joined
Feb 4, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
FILE is an array and values need to be assigned to the array elements.

e.g.
FILE(1)="test1.xlsx"
FILE(2)="test2.xlsx"

You will need to refer to the array element when opening the file.

e.g. Set WB = Workbooks.Open(FILE(i))

You dont need to select and copy values from cells.

You can make a reference to the 'from cell' and assign the value to the 'to cell'.

e.g. Workbooks("TOWORKBOOK").Sheets("Sheet1").Range("A1") = Workbooks("FROMWORKBOOK").Sheets("Sheet1").Range("A1")

Hope that this helps somewhat.

I could rewrite it all for you but then you would have nothing to do for the rest of the day!!

Take care and stay safe.
Thanks, It works now.
 

MachuPichu92

New Member
Joined
Feb 4, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hi MachuPichu92. There are a few things with your code that are not "good form", such as using a VBA reserved word (DIR) as a variable name, and SELECTing and ACTIVATEing when you don't have to.

Anyway, have a look at the re-write below, and see what you think. (It is untested, but it looks right to me.)

VBA Code:
Sub DoStuff()
    Const FileDir As String = "C:\Users\VB\Desktop\V\"
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim LR As Long
    Dim i As Integer
    Dim FileToUse As String
    '
    Set WS = ThisWorkbook.Worksheets("List1")
    LR = WS.Range("A" & CStr(WS.Rows.Count)).End(xlUp).Row
    For i = 1 To 6
        FileToUse = FileDir & Replace("test~.xlsx", "~", CStr(i))
        Set WB = Workbooks.Open(Filename:=FileToUse, ReadOnly:=True)
        With WB.Sheets("Sheet1").Cells(1, 1)
            If CStr(.Value) <> vbNullString Then
                LR = LR + 1
                WS.Cells(LR, 1).Value = .Value
            End If
        End With
        WB.Close
    Next i
    Set WB = Nothing
    Set WS = Nothing
End Sub
Hello, Cep,

I tested your code and it also works, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,935
Members
418,253
Latest member
TheJackal26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top