Opening Workbooks in a Folder
 

Hello All,
I would like to open each workbook in a folder, get some information from it
and close it. How can that be done? I can get the path but when I do
workbooks count, it just counts the open workbooks.

Thanks,

Bill

Opening Workbooks in a Folder
 

Hi Bill,

See Ron de Bruin's sample code at:

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

and

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

See also Ro's index page at:

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


---
Regards,
Norman



"Bill" wrote in message
k.net...
Hello All,
I would like to open each workbook in a folder, get some information from
it and close it. How can that be done? I can get the path but when I do
workbooks count, it just counts the open workbooks.

Thanks,

Bill

Opening Workbooks in a Folder
 

On Thu, 29 Jun 2006 13:33:46 GMT, Bill wrote:

Hello All,
I would like to open each workbook in a folder, get some information from it
and close it. How can that be done? I can get the path but when I do
workbooks count, it just counts the open workbooks.

Thanks,

Bill

Test this sample module:

Option Explicit

Function GetAllXLSFiles(ByVal strFolder As String) As Variant
Dim strCurFile As String
Dim strTmp As String
strCurFile = Dir(strFolder & "\*.xls")
Do While strCurFile < ""
strTmp = strTmp & Switch(strTmp = "", "", True, ";") & strCurFile
strCurFile = Dir
Loop
GetAllXLSFiles = Split(strTmp, ";")
End Function

Sub test()
Dim v As Variant
v = GetAllXLSFiles("d:\somefolder")
End Sub

Opening Workbooks in a Folder
 

Piotr, how would I amend your Function to include subfolders and only
open the last modified file in each subfolder. TIA

Greg
Piotr Lipski wrote:
On Thu, 29 Jun 2006 13:33:46 GMT, Bill wrote:

Hello All,
I would like to open each workbook in a folder, get some information from it
and close it. How can that be done? I can get the path but when I do
workbooks count, it just counts the open workbooks.

Thanks,

Bill

Test this sample module:

Option Explicit

Function GetAllXLSFiles(ByVal strFolder As String) As Variant
Dim strCurFile As String
Dim strTmp As String
strCurFile = Dir(strFolder & "\*.xls")
Do While strCurFile < ""
strTmp = strTmp & Switch(strTmp = "", "", True, ";") & strCurFile
strCurFile = Dir
Loop
GetAllXLSFiles = Split(strTmp, ";")
End Function

Sub test()
Dim v As Variant
v = GetAllXLSFiles("d:\somefolder")
End Sub

Opening Workbooks in a Folder
 

Dnia 29 Jun 2006 08:33:58 -0700, GregR napisa³(a):

Piotr, how would I amend your Function to include subfolders and only
open the last modified file in each subfolder. TIA

Dealing with subfolders is not trivial when using Dir function (it cannot
be used recursively). You should probably read about FileScripting library
(or sth like that) - I've never used it, but recurring throught subfolders
is quite common task. Google should be helpful ;)

--
PL

Opening Workbooks in a Folder
 

See
http://www.rondebruin.nl/copy3.htm

Number 1


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Piotr Lipski" wrote in message ...
Dnia 29 Jun 2006 08:33:58 -0700, GregR napisa³(a):

Piotr, how would I amend your Function to include subfolders and only
open the last modified file in each subfolder. TIA

Dealing with subfolders is not trivial when using Dir function (it cannot
be used recursively). You should probably read about FileScripting library
(or sth like that) - I've never used it, but recurring throught subfolders
is quite common task. Google should be helpful ;)

--
PL