Selecting a directory from a form in excel

[ennui]

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

 

[mudraker]

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

 

[Paul Robinson]

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

 

[Tom Ogilvy]

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

 

[Paul Robinson]

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

 

[ennui]

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?

 

[ennui]

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