How to open files from excel list using VBA?

[badsham77]

I need some help with this coding. I want to open files from an excel list.

Sub Openfile()

Dim wkbOne as Workbook

Set wkbOne = Application.Workbooks.Open(Filename:=Worksheets("Sheet1").Range("A1") & Worksheets("Sheet1").Range("B1")
'where "A1" is the path where the file located and "B1" is the file name.'
End Sub

When I run this it will work, but if I change to below it won't open all file in range, what's wrong with this?

Sub Openfile()

Dim wkbOne as Workbook

Set wkbOne = Application.Workbooks.Open(Filename:=Worksheets("Sheet1").Range("A1:A10") & Worksheets("Sheet1").Range("B1:B10")

End Sub

Thanks for the help.

 

[VoG]

Perhaps like this

Sub Openfile()
Dim wkbOne(1 To 10) As Workbook, i As Long
For i = 1 To 10
    Set wkbOne(i) = Workbooks.Open(Worksheets("Sheet1").Range("A" & i).Value & Worksheets("Sheet1").Range("B" & i).Value)
Next i
End Sub

 

[badsham77]

Hi VoG,

Why do i get this error message eventhough i have put the correct path and filename?

" could not be found. Check the spelling of the file name, and verify that the location is correct

???

thanks for the help

 

[VoG]

Can you give examples of what is in A1 and B1.

 

[badsham77]

in A1
C:\Documents and Settings\user\Desktop\trial\

in B1
test.xls

 

[VoG]

That should work provided that C:\Documents and Settings\user\Desktop\trial\ is a valid path.

 

[badsham77]

Sorry VoG,

It does work. The error happens because the A2 and B2 value is empty. is there a way to command the VB to stop at empty value?

Thanks

 

[VoG]

Try this

Sub Openfile()
Dim wkbOne(1 To 10) As Workbook, i As Long, j As Long
For i = 1 To 10
    If Worksheets("Sheet1").Range("A" & i).Value <> "" And Worksheets("Sheet1").Range("B" & i).Value <> "" Then
        j = j + 1
        Set wkbOne(j) = Workbooks.Open(Worksheets("Sheet1").Range("A" & i).Value & Worksheets("Sheet1").Range("B" & i).Value)
    End If
Next i
MsgBox j & "files opened", vbInformation
End Sub

 

[badsham77]

That was great. Thanks

I try to put password on the files.

I add on the below

Sub Openfile()
Dim wkbOne(1 To 10) As Workbook, i As Long, j As Long
For i = 1 To 10
If Worksheets("Sheet1").Range("A" & i).Value <> "" And Worksheets("Sheet1").Range("B" & i).Value <> "" Then
j = j + 1
Set wkbOne(j) = Workbooks.Open(Worksheets("Sheet1").Range("A" & i).Value & Worksheets("Sheet1").Range("B" & i).Value)
End If
wkbOne(i).Password = Worksheets("Sheet1").Range("C" & i).Value
Next i
MsgBox j & " Files Protected", vbInformation
End Sub

But it didn't work. please help

 

[VoG]

Try

Sub Openfile()
Dim wkbOne(1 To 10) As Workbook, i As Long, j As Long
With ThisWorkbook
    For i = 1 To 10
        If .Worksheets("Sheet1").Range("A" & i).Value <> "" And .Worksheets("Sheet1").Range("B" & i).Value <> "" Then
            j = j + 1
            Set wkbOne(j) = Workbooks.Open(.Worksheets("Sheet1").Range("A" & i).Value & .Worksheets("Sheet1").Range("B" & i).Value)
            Application.DisplayAlerts = False
            wkbOne(j).SaveAs Filename:=.Worksheets("Sheet1").Range("A" & i).Value & .Worksheets("Sheet1").Range("B" & i).Value, Password:=.Worksheets("Sheet1").Range("C" & i).Value
            Application.DisplayAlerts = True
        End If
    Next i
End With
MsgBox j & "files opened", vbInformation
End Sub