Treeview control and view of folders and files
 

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

Treeview control and view of folders and files
 

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

Treeview control and view of folders and files
 

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

Treeview control and view of folders and files
 

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

Treeview control and view of folders and files
 

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

Treeview control and view of folders and files
 

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