Macro to open, calculate, the save many files.
 

I would like to open, calculate, then close about 200 plus files. The files
are too large to open at once. I need to open one file, calculate, then
close it before opening the next file. The file names are in column A, the
path is the same for all files. I am guessing this involves an "array".
Unfortunately I haven't yet learned to work with arrays. Hope somebody can
help me. Thanks.

Macro to open, calculate, the save many files.
 

Something like

Dim aryNames
Dim i As Long

aryNames = Array("File1.xls", "File2.xls", "File3.xls", _
"File4.xls")
For i = LBound(aryNames) To UBound(aryNames)
Workbooks.Open aryNames(i)
'your code
ActiveWorkbook.Close savechanges:=False
Next i


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"msdrolf" wrote in message
...
I would like to open, calculate, then close about 200 plus files. The
files
are too large to open at once. I need to open one file, calculate, then
close it before opening the next file. The file names are in column A,
the
path is the same for all files. I am guessing this involves an "array".
Unfortunately I haven't yet learned to work with arrays. Hope somebody
can
help me. Thanks.

Macro to open, calculate, the save many files.
 

Hi M,

Subject to your requirements, you may be able to interrogate the files
without opening them.

For pointers, see Ron de Bruin's example code at:

http://www.rondebruin.nl/ado.htm

and

http://www.rondebruin.nl/copy7.htm



---
Regards,
Norman


"msdrolf" wrote in message
...
I would like to open, calculate, then close about 200 plus files. The
files
are too large to open at once. I need to open one file, calculate, then
close it before opening the next file. The file names are in column A,
the
path is the same for all files. I am guessing this involves an "array".
Unfortunately I haven't yet learned to work with arrays. Hope somebody
can
help me. Thanks.

Macro to open, calculate, the save many files.
 

Oops,

Savechanges should be true, not false.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Something like

Dim aryNames
Dim i As Long

aryNames = Array("File1.xls", "File2.xls", "File3.xls", _
"File4.xls")
For i = LBound(aryNames) To UBound(aryNames)
Workbooks.Open aryNames(i)
'your code
ActiveWorkbook.Close savechanges:=False
Next i


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"msdrolf" wrote in message
...
I would like to open, calculate, then close about 200 plus files. The
files
are too large to open at once. I need to open one file, calculate, then
close it before opening the next file. The file names are in column A,
the
path is the same for all files. I am guessing this involves an "array".
Unfortunately I haven't yet learned to work with arrays. Hope somebody
can
help me. Thanks.

Macro to open, calculate, the save many files.
 

Thanks for the input. One follow-up question:
Is it necessary to list all files in the Array("File1.xls",....) or is it
possible to somehow input a range with all the file names - there are over
200 files.

"Bob Phillips" wrote:

Something like

Dim aryNames
Dim i As Long

aryNames = Array("File1.xls", "File2.xls", "File3.xls", _
"File4.xls")
For i = LBound(aryNames) To UBound(aryNames)
Workbooks.Open aryNames(i)
'your code
ActiveWorkbook.Close savechanges:=False
Next i


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"msdrolf" wrote in message
...
I would like to open, calculate, then close about 200 plus files. The
files
are too large to open at once. I need to open one file, calculate, then
close it before opening the next file. The file names are in column A,
the
path is the same for all files. I am guessing this involves an "array".
Unfortunately I haven't yet learned to work with arrays. Hope somebody
can
help me. Thanks.

Macro to open, calculate, the save many files.
 

You can pick up the values from a range in the worksheet like:

Option Explicit
Sub testme01()
Dim aryNames As Variant
Dim i As Long

With Worksheets("sheet1")
aryNames = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With

For i = LBound(aryNames, 1) To UBound(aryNames, 1)
MsgBox aryNames(i, 1)
'Workbooks.Open aryNames(i, 1)
'your code
'ActiveWorkbook.Close savechanges:=True 'or false
Next i

End Sub

This assumes that the names are all in column A starting with A1.

That aryNames is a 2 dimensional array (200 rows by 1 column). That's why
there's that ", 1" hanging around. It means the first (and only) column.

msdrolf wrote:

Thanks for the input. One follow-up question:
Is it necessary to list all files in the Array("File1.xls",....) or is it
possible to somehow input a range with all the file names - there are over
200 files.

"Bob Phillips" wrote:

Something like

Dim aryNames
Dim i As Long

aryNames = Array("File1.xls", "File2.xls", "File3.xls", _
"File4.xls")
For i = LBound(aryNames) To UBound(aryNames)
Workbooks.Open aryNames(i)
'your code
ActiveWorkbook.Close savechanges:=False
Next i


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"msdrolf" wrote in message
...
I would like to open, calculate, then close about 200 plus files. The
files
are too large to open at once. I need to open one file, calculate, then
close it before opening the next file. The file names are in column A,
the
path is the same for all files. I am guessing this involves an "array".
Unfortunately I haven't yet learned to work with arrays. Hope somebody
can
help me. Thanks.




--

Dave Peterson