Hello,

On a form I need to create in Excel I have to be able to 'Open'
several workbooks and summarise their information into one workbook.
In order to do this I need to be able to select which directory the
workbooks are in.

Is there an easy way to do this from a form in Excel? If I click an
'Open' button can I create a little pop up menu that allows me to
select the directory I wish to be in?

Any help would be greatly appreciated.
ennui

ennui


have a look at

Application.GetOpenFilename

I have used it to get the names of files

using extra code you should be able to get the path of the selecte
fil

--
Message posted from http://www.ExcelForum.com

Also have a look at John Walkenbach's page and sample code for displaying
the folder/directory selection dialog

http://j-walk.com/ss/excel/tips/tip29.htm
Selecting a Directory

--
Regards,
Tom Ogilvy



"ennui" wrote in message
om...
Hello,

On a form I need to create in Excel I have to be able to 'Open'
several workbooks and summarise their information into one workbook.
In order to do this I need to be able to select which directory the
workbooks are in.

Is there an easy way to do this from a form in Excel? If I click an
'Open' button can I create a little pop up menu that allows me to
select the directory I wish to be in?

Any help would be greatly appreciated.
ennui

Hi
some code. Take care with line wrapping:

WBFullName = Application.GetOpenFilename("Excel Files(*.xls), *.xls",
Title:="Where is the File?")
If WBFullName = "False" Then 'form cancelled, so exit sub
Exit Sub
End If
'Get the file name
WBFileName = GetFileName(WBFullName)
If IsWorkBookOpen(WBFileName) Then 'workbook already open on
user
machine
'MsgBox "You already have this file open. Close it and try
again", vbOKOnly, "Help with Upload and Download"
Set MyWorkBook = Workbooks(WBFileName)
Else 'Get the file
Set MyWorkBook = Workbooks.Open(filename:=WBFullName)
End If

where GetFileName is

'See Green p80
'Returns the full file name from the end of a path by looking for
first \
'If no \, returns the file name
Public Function GetFileName(FullPathString As String) As String
Dim stPathSep As String 'Path separator, \
Dim FPLength As Integer 'length of FullPathString
Dim i As Integer 'counter
stPathSep = Application.PathSeparator
FPLength = Len(FullPathString)
For i = FPLength To 1 Step -1
If Mid(FullPathString, i, 1) = stPathSep Then Exit For
Next i
GetFileName = Right(FullPathString, FPLength - i)
End Function


regards
Paul


(ennui) wrote in message . com...
Hello,

On a form I need to create in Excel I have to be able to 'Open'
several workbooks and summarise their information into one workbook.
In order to do this I need to be able to select which directory the
workbooks are in.

Is there an easy way to do this from a form in Excel? If I click an
'Open' button can I create a little pop up menu that allows me to
select the directory I wish to be in?

Any help would be greatly appreciated.
ennui

Hi
You need this function too.


'See Green p81
Function IsWorkBookOpen(WorkBookName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next
Set Wkb = Workbooks(WorkBookName)
If Not Wkb Is Nothing Then
IsWorkBookOpen = True
End If
Set Wkb = Nothing
End Function

Cheers everyone - that has been really really *really* helpful. Thank
you so much.


Once question though... how would I call it recursively so that I open
all the WorkBooks in the directory?

The idea behind my project is - from my 'Summary' SS I need to be able
to open all the workbooks in a directory one at a time, check they are
the correct format (ie. Sheet 1 is named 'Blah' and Sheet 2 is nameed
'Cat'), select some data from a form in the open workbook, insert it
into the new 'Summary' workbook and then close the open workbook. I
need to do this for up to 100 excel workbooks in a directory...

Sounds easy?

Update: I have managed to select a directory in my file system and
then recursively select all the workbooks in that directory to open.
There are only a few in there at the moment so it works okay though I
am aware of the memory limitaions of trying to open 100 workbooks.

So if anyone has any ideas on:

a) how to stop that irritating popup asking me if I wish to update my
links
b) the most effiecent way to copy information from one workbook to
another

Cheers,
ennui