CODEWITHSUNDEEP
vbaloopsexcelforeach
Jason Kowalski
Jason Kowalski

Reputation: 13

Loop through folder, and for each workbook copy paste a range to a static workbook

I am trying to loop through a list of workbooks, and then for each workbook copy and paste several ranges on a worksheet in the current workbook to a master workbook.

I am having trouble selecting the workbook so I can use it - I keep getting subscript out of range, and I think my file path is correct. I've tried with extensions and without, and when I search that extension in my computer it works fine. Any ideas?

I have tried to open the file (which is a variant) and this does not work because I don't think it's recognized as a workbook. I have tried to open the name of that file, but that doesn't work either. Lastly, I named a range the actual name and tried to do Workbooks(APath).Open, but that did not work. What am I missing? Thanks!

I switched file paths to fake ones. 

Dim fso As FileSystemObject
Dim MyObj As Object, MySource As Object, file As Variant
Set fso = New FileSystemObject

FilePath = InputBox(Prompt:="Type the folder file path: this:C:\Users\A\Desktop \test_folder\", Title:="UPDATE MACRO")

Set MySource = fso.GetFolder(FilePath)
For Each file In MySource.Files

'This does not work...
file.Activiate
file.Open

'So I tried this, and still did not work. Any ideas?
APath = "\\file\A\Template_1.xlsx"

MsgBox FilePath & file.Name
actwb = FilePath & file.Name

Workbooks(APath).Open
Workbooks(APath).Activate
MsgBox ActiveWorkbook

Upvotes: 1

Views: 1955

Answers (1)

David Zemens
David Zemens

Reputation: 53623

The file here is a Scripting.File object, not an Excel Workbook (yet). So there is no Activate or Open method. You have to open it as a Workbook object in order to use the workbook methods. To get it opened in Excel, do this:

Dim wb as Workbook

For Each file In MySource.Files


    Set wb = Workbooks.Open(file.path)

    'now you can do stuff to the wb Workbook
    MsgBox wb.Name 'etc...

    'when you're done with the workbook, save it/close it:
    wb.Save 'or omit this line if you don't want to save it, etc.
    wb.Close 

Next

Upvotes: 1

Related Questions