Saving a Collection of files

[Jason8598]

The below macro, that I use to open files from a http location, works well for me if all I want to do is open the files. Is there anyway that, instead of opening, I can save the files to a folder that I define in cell I1?

Sub OpenFiles()
Dim wkb As Workbook
Dim file As Variant
Dim coll As New Collection
Range("G2").Select
Do
    If ActiveCell = True Then
        coll.Add ActiveCell.Offset(0, 2)
        End If
    ActiveCell.Offset(3, 0).Select
Loop Until IsEmpty(ActiveCell)
For Each file In coll
    Set wkb = Workbooks.Open(file)
Next file
End Sub

 

[AlphaFrog]

Sub OpenFiles()
    Dim wkb    As Workbook
    Dim file   As Variant
    Dim coll   As New Collection
    Range("G2").Select
    Do
        If ActiveCell = True Then
            coll.Add ActiveCell.Offset(0, 2)
        End If
        ActiveCell.Offset(3, 0).Select
    Loop Until IsEmpty(ActiveCell)
    For Each file In coll
        Set wkb = Workbooks.Open(file)
[COLOR="Red"]        wkb.SaveAs Filename:=Range("I1") & wkb.Name
        wkb.Close[/COLOR]
    Next file
End Sub

 

[Jason8598]

Thanks for the quick reply. The files are saving, however they always save to my desktop no matter what I put into I1. Do I need to format I1 a certain way or have the location entered a certain way?

 

[AlphaFrog]

I'm guessing the sheet with the directory path in I1 is not the active sheet.

Try something like this

wkb.SaveAs Filename:=Sheets("[COLOR="Red"]Sheet1[/COLOR]").Range("I1") & wkb.Name

...where Sheet1 is the name of the sheet with the I1 folder

Also, make sure the path in I1 includes a \ at the end e.g. "C:\MyFolder\"

 

[Jason8598]

I'm guessing the sheet with the directory path in I1 is not the active sheet.

Try something like this

wkb.SaveAs Filename:=Sheets("[COLOR=red]Sheet1[/COLOR]").Range("I1") & wkb.Name

...where Sheet1 is the name of the sheet with the I1 folder

I am not sure I can do this as each file will have different names for the sheets. Also, there are a few files that have multiple sheets.

Also, make sure the path in I1 includes a \ at the end e.g. "C:\MyFolder\"

This did not help. I did a test by saving the file and reopening it. After running the macro, the files saved to My Documents. I did a save as and resaved the file to desktop, ran the macro again, and now the files save to my desktop. For whatever reason, the files are saving to the last active folder.

 

[AlphaFrog]

Sorry, I made a mistake. Try this..

Sub OpenFiles()
    Dim wkb    As Workbook
    Dim file   As Variant
    Dim coll   As New Collection
[COLOR="Red"]    Dim strPath as String
    strPath = Range("I1").Value[/COLOR]
    Range("G2").Select
    Do
        If ActiveCell = True Then
            coll.Add ActiveCell.Offset(0, 2)
        End If
        ActiveCell.Offset(3, 0).Select
    Loop Until IsEmpty(ActiveCell)
    For Each file In coll
        Set wkb = Workbooks.Open(file)
[COLOR="Red"]        wkb.SaveAs Filename:=strPath & wkb.Name
        wkb.Close[/COLOR]
    Next file
End Sub

 

[Jason8598]

Awesome, works great. Thank you for the help.