I want to have a Treeview control on a userform that shows the
complete folder-tree(expandable) on a hard drive and Excel-files in
it.Then open a selected file as a Workbook to be able to communicate
with it. Can someone help me out with the necessary code to populate
the Treeview control and the code for opening a selected file as a
Workbook. I am lost here, none of the code i found with a google
search seems to be working properly, unless i am doing something
wrong,
thanks
JH

You may have a good reason to do this, but it seems like it would be simpler
to just do

Dim fName as Variant
fname = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
if fName < False then
workbooks.Open fName
End If

--
Regards,
Tom Ogilvy


"John Holland" wrote in message
om...
I want to have a Treeview control on a userform that shows the
complete folder-tree(expandable) on a hard drive and Excel-files in
it.Then open a selected file as a Workbook to be able to communicate
with it. Can someone help me out with the necessary code to populate
the Treeview control and the code for opening a selected file as a
Workbook. I am lost here, none of the code i found with a google
search seems to be working properly, unless i am doing something
wrong,
thanks
JH

Hi,

would this work?



Option Explicit


Private Sub tvPopulate(sPath As String)

Dim fso As FileSystemObject
Dim fld As Folder

Set fso = New Scripting.FileSystemObject
If fso.FolderExists(sPath) Then
TreeView1.Nodes.Clear
Set fld = fso.GetFolder(sPath)
Call GetFiles(fld)
Else
MsgBox "The folder path " & sPath & "does not exist"
End If
End Sub

Private Sub GetFiles(fld As Folder, Optional par As Folder = Null)
Dim kid As Folder
Dim fil As File
Dim nod As Node

On Error Resume Next

If par Is Nothing Then
Set nod = TreeView1.Nodes.Add(, , fld.Name, fld.Name)
nod.Expanded = True
Else
TreeView1.Nodes.Add par.Name, tvwChild, fld.Name, fld.Name
End If
Application.StatusBar = "Filling nodes for " & fld.Path
For Each kid In fld.SubFolders
Call GetFiles(kid, fld)
Next
For Each fil In fld.Files
If fil.Name Like "*xls" Then
TreeView1.Nodes.Add fld.Name, tvwChild, fil.Path, fil.Name
End If
Next
End Sub


Private Sub TreeView1_Click()
MsgBox TreeView1.SelectedItem
End Sub


Private Sub UserForm_activate()
Call tvPopulate("D:\my documents")
Application.StatusBar = ""
End Sub

Private Sub UserForm_Initialize()
With TreeView1
.Appearance = cc3D
.Indentation = 12
End With
End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(John Holland) wrote:

I want to have a Treeview control on a userform that shows the
complete folder-tree(expandable) on a hard drive and Excel-files in
it.Then open a selected file as a Workbook to be able to communicate
with it. Can someone help me out with the necessary code to populate
the Treeview control and the code for opening a selected file as a
Workbook. I am lost here, none of the code i found with a google
search seems to be working properly, unless i am doing something
wrong,
thanks
JH

Thanks for your help,
i will try it out. Tom Ogilvy helped me to a very short solution as
well. In ieder geval bedankt voor je hulp.
cheers,
JH
keepITcool wrote in message . ..
Hi,

would this work?



Option Explicit


Private Sub tvPopulate(sPath As String)

Dim fso As FileSystemObject
Dim fld As Folder

Set fso = New Scripting.FileSystemObject
If fso.FolderExists(sPath) Then
TreeView1.Nodes.Clear
Set fld = fso.GetFolder(sPath)
Call GetFiles(fld)
Else
MsgBox "The folder path " & sPath & "does not exist"
End If
End Sub

Private Sub GetFiles(fld As Folder, Optional par As Folder = Null)
Dim kid As Folder
Dim fil As File
Dim nod As Node

On Error Resume Next

If par Is Nothing Then
Set nod = TreeView1.Nodes.Add(, , fld.Name, fld.Name)
nod.Expanded = True
Else
TreeView1.Nodes.Add par.Name, tvwChild, fld.Name, fld.Name
End If
Application.StatusBar = "Filling nodes for " & fld.Path
For Each kid In fld.SubFolders
Call GetFiles(kid, fld)
Next
For Each fil In fld.Files
If fil.Name Like "*xls" Then
TreeView1.Nodes.Add fld.Name, tvwChild, fil.Path, fil.Name
End If
Next
End Sub


Private Sub TreeView1_Click()
MsgBox TreeView1.SelectedItem
End Sub


Private Sub UserForm_activate()
Call tvPopulate("D:\my documents")
Application.StatusBar = ""
End Sub

Private Sub UserForm_Initialize()
With TreeView1
.Appearance = cc3D
.Indentation = 12
End With
End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(John Holland) wrote:

I want to have a Treeview control on a userform that shows the
complete folder-tree(expandable) on a hard drive and Excel-files in
it.Then open a selected file as a Workbook to be able to communicate
with it. Can someone help me out with the necessary code to populate
the Treeview control and the code for opening a selected file as a
Workbook. I am lost here, none of the code i found with a google
search seems to be working properly, unless i am doing something
wrong,
thanks
JH

Thanks for your help Tom, did not know it was that simple.
Is there a simple way to include a reference in my code to the just
opened file ? I mean how does my code pick up the name of the newly
opened workbook, so that i can refer to it ? Thanks, JH

"Tom Ogilvy" wrote in message ...
You may have a good reason to do this, but it seems like it would be simpler
to just do

Dim fName as Variant
fname = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
if fName < False then
workbooks.Open fName
End If

--
Regards,
Tom Ogilvy


"John Holland" wrote in message
om...
I want to have a Treeview control on a userform that shows the
complete folder-tree(expandable) on a hard drive and Excel-files in
it.Then open a selected file as a Workbook to be able to communicate
with it. Can someone help me out with the necessary code to populate
the Treeview control and the code for opening a selected file as a
Workbook. I am lost here, none of the code i found with a google
search seems to be working properly, unless i am doing something
wrong,
thanks
JH

forget my last message on picking up a reference to the file. must
obviously just be "filename", cheers, JH

"Tom Ogilvy" wrote in message ...
You may have a good reason to do this, but it seems like it would be simpler
to just do

Dim fName as Variant
fname = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
if fName < False then
workbooks.Open fName
End If

--
Regards,
Tom Ogilvy


"John Holland" wrote in message
om...
I want to have a Treeview control on a userform that shows the
complete folder-tree(expandable) on a hard drive and Excel-files in
it.Then open a selected file as a Workbook to be able to communicate
with it. Can someone help me out with the necessary code to populate
the Treeview control and the code for opening a selected file as a
Workbook. I am lost here, none of the code i found with a google
search seems to be working properly, unless i am doing something
wrong,
thanks
JH