Using VBA to access files in a list in EXCEL

[Barb Reinhardt]

I have a list of file names (with directory path included) in Column A of a
worksheet. I'd like to generate a loop in VBA to access each of those
files and perform a sequence of activities on the list of documents. How
would I do that? The start and end row may vary.

Is there a resource that I could view that would help me with this?

Thanks,
Barb Reinhardt

[Bob Phillips]

Barb,

Here is an example using FSO. It opens worksheets and copies data. AMend
that bit for your purposes.

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
sFolder = "C:\MyTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
this.Worksheets.Add.Name = "File" & cnt
With ActiveWorkbook
.Worksheets(1).Range("A1:C100").Copy _
Destination:=this.Worksheets("File" &
cnt).Range("A1")
.Close
End With
cnt = cnt + 1
End If
Next file

End If ' sFolder <> ""

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> I have a list of file names (with directory path included) in Column A of
a
> worksheet. I'd like to generate a loop in VBA to access each of those
> files and perform a sequence of activities on the list of documents. How
> would I do that? The start and end row may vary.
>
> Is there a resource that I could view that would help me with this?
>
> Thanks,
> Barb Reinhardt

[PY & Associates]

Try something like this

first row is 1
last_ row=range("A65536").end(xlup).row
for i=1 to last_ row
workbook.open range("A" & i)
do something
workbook.close
next i

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> I have a list of file names (with directory path included) in Column A of
a
> worksheet. I'd like to generate a loop in VBA to access each of those
> files and perform a sequence of activities on the list of documents. How
> would I do that? The start and end row may vary.
>
> Is there a resource that I could view that would help me with this?
>
> Thanks,
> Barb Reinhardt