Prokopis
12-28-2015, 01:46 AM
Hi all, I am trying to make my first app in vba ( I am using office excel 2010).
I want the user to browse for a zip file and then the system will load in
a treeview all the file structure of zip. (If there is another subzip in zip file will not be analyzed)
If a file with name 2.sql is found then the node in treeview I want to be in red font.
Could you help me do it ?
My code is the following :
Dim FileFound As Integer
Dim Counter As Integer
Dim searchForFile As String
Private Sub BrowseButton_Click()
Dim FilePathName As Variant
'select a file using a dialog and get the full name with path included
FilePathName = Application.GetOpenFilename("Zip File (*.zip), *.zip")
If FilePathName <> False Then
TextBoxPath.Value = FilePathName
Me.TreeView1.Nodes.Clear
'crashes text is not displayed
Me.TreeView1.Nodes.Add Key:="Main2", Text:=GetZipNameWithExtension(CStr(PathFile))
zpath FilePathName, searchForFile
End If
End Sub
Private Sub UserForm_Initialize()
searchForFile = "2.sql"
Counter = 0
Me.TreeView1.Nodes.Clear
Me.TreeView1.LineStyle = tvwRootLines
Me.TreeView1.Indentation = 20
Me.TreeView1.Nodes.Add Key:="Main", Text:="Zip Structure"
End Sub
Public Function ExpandAllNodes()
On Error Resume Next
Dim expAll As Integer
For expAll = 1 To TreeView1.Nodes.count
If TreeView1.Nodes(expAll).Children Then
TreeView1.Nodes(expAll).Expanded = True
End If
Next
End Function
Sub zpath(PathFilename As Variant, searchForFile As String)
Dim sh, n
FileFound = 0
Set sh = CreateObject("shell.application")
Set n = sh.Namespace(PathFilename) 'n keeps the name of the file without path
recur sh, n, searchForFile
End Sub
Sub recur(sh, n, searchForFile)
Dim i, subn
' ------------------------------------------------
' | Counter | Company | Path | Name of Search File |
' ------------------------------------------------
Dim arrayInfo()
Dim intRows
Dim intCols
intCols = 4
For Each i In n.items
If i.isfolder Then
Set subn = sh.Namespace(i)
Counter = Counter + 1
Me.TreeView1.Nodes.Add Relative:="Main2", _
relationship:=tvwChild, Key:="Folder" & CStr(Counter), Text:=subn
recur sh, subn, searchForFile
Else
If i.Name = searchForFile Then
FileFound = FileFound + 1
intRows = FileFound
Redim Preserve arrayInfo(1 To intRows, 1 To intCols)
' Fill the arrayInfo
arrayInfo(FileFound, 1) = CStr(FileFound)
arrayInfo(FileFound, 3) = i.path
arrayInfo(FileFound, 4) = i.Name
End If
End If
Next
End Sub
Function GetZipName(ByVal path As String) As String
GetZipName = Mid(path, InStrRev(path, "\") + 1, InStrRev(path, ".") - InStrRev(path, "\") - 1)
End Function
Function GetZipNameWithExtension(ByVal strPath As String) As String
If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
GetZipNameWithExtension = GetZipNameWithExtension(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If
End Function
I attach also my files
Thanks a lot
I want the user to browse for a zip file and then the system will load in
a treeview all the file structure of zip. (If there is another subzip in zip file will not be analyzed)
If a file with name 2.sql is found then the node in treeview I want to be in red font.
Could you help me do it ?
My code is the following :
Dim FileFound As Integer
Dim Counter As Integer
Dim searchForFile As String
Private Sub BrowseButton_Click()
Dim FilePathName As Variant
'select a file using a dialog and get the full name with path included
FilePathName = Application.GetOpenFilename("Zip File (*.zip), *.zip")
If FilePathName <> False Then
TextBoxPath.Value = FilePathName
Me.TreeView1.Nodes.Clear
'crashes text is not displayed
Me.TreeView1.Nodes.Add Key:="Main2", Text:=GetZipNameWithExtension(CStr(PathFile))
zpath FilePathName, searchForFile
End If
End Sub
Private Sub UserForm_Initialize()
searchForFile = "2.sql"
Counter = 0
Me.TreeView1.Nodes.Clear
Me.TreeView1.LineStyle = tvwRootLines
Me.TreeView1.Indentation = 20
Me.TreeView1.Nodes.Add Key:="Main", Text:="Zip Structure"
End Sub
Public Function ExpandAllNodes()
On Error Resume Next
Dim expAll As Integer
For expAll = 1 To TreeView1.Nodes.count
If TreeView1.Nodes(expAll).Children Then
TreeView1.Nodes(expAll).Expanded = True
End If
Next
End Function
Sub zpath(PathFilename As Variant, searchForFile As String)
Dim sh, n
FileFound = 0
Set sh = CreateObject("shell.application")
Set n = sh.Namespace(PathFilename) 'n keeps the name of the file without path
recur sh, n, searchForFile
End Sub
Sub recur(sh, n, searchForFile)
Dim i, subn
' ------------------------------------------------
' | Counter | Company | Path | Name of Search File |
' ------------------------------------------------
Dim arrayInfo()
Dim intRows
Dim intCols
intCols = 4
For Each i In n.items
If i.isfolder Then
Set subn = sh.Namespace(i)
Counter = Counter + 1
Me.TreeView1.Nodes.Add Relative:="Main2", _
relationship:=tvwChild, Key:="Folder" & CStr(Counter), Text:=subn
recur sh, subn, searchForFile
Else
If i.Name = searchForFile Then
FileFound = FileFound + 1
intRows = FileFound
Redim Preserve arrayInfo(1 To intRows, 1 To intCols)
' Fill the arrayInfo
arrayInfo(FileFound, 1) = CStr(FileFound)
arrayInfo(FileFound, 3) = i.path
arrayInfo(FileFound, 4) = i.Name
End If
End If
Next
End Sub
Function GetZipName(ByVal path As String) As String
GetZipName = Mid(path, InStrRev(path, "\") + 1, InStrRev(path, ".") - InStrRev(path, "\") - 1)
End Function
Function GetZipNameWithExtension(ByVal strPath As String) As String
If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
GetZipNameWithExtension = GetZipNameWithExtension(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If
End Function
I attach also my files
Thanks a lot