Solved

Search Through Excel Sheet Using VBScript

Posted on 2002-04-16
5
506 Views
Last Modified: 2012-08-14
Hello Experts.  I have the following question:

I have a list of about 300 folders in Excel spreadsheet.  When I go to that directory, there are actually 550 folders in the directory. I need to compare the folders in the directory with those in Excel sheet to determine which folders are extra.  I am fairly confident with VBScript, but I don't know what properties (or objects) the Excel.Application has and how to use them.

In Excel file I have three sheets each with a listing of files that are in a specific directory. I don't know how to go to a specific sheet to do the search.  Can anyone help?
0
Comment
Question by:Dmitriy
  • 3
5 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
Comment Utility
Hi Dmitry,

You can take a look at the sample here from a prvious answer

http://www.bredlum.com/ee_temp/filedb.xls

basically you can reuse the code, i can prepare a sample specific to your situation....

but these steps could give you a go, this was only a complete file lookup in excel, the comaprison could be done with a vlookup

-open a new workbook
-place two buttons on sheet1
-open the VB Editor with ALT+F11
-paste this code under the sheet1 icon

Option Explicit

Private Sub CommandButton1_Click()
 Call Start
End Sub

Private Sub CommandButton2_Click()
 Call ClearSheet
End Sub

-choose insert new module
-then paste this code in the new module

Option Explicit

Public writerow As Integer 'on top of module

Public Sub Start()
 Dim GetDirName As String

On Error GoTo errhandle
 Worksheets(1).Range("A11").Select
 writerow = ActiveCell.Row
 GetDirName = Application.GetOpenFilename ' get filepath from user
 GetDirName = SplitPath(GetDirName, 3)    ' extract drive + path from filepath
 If Len(GetDirName) > 0 Then
 Application.ScreenUpdating = False
     GetFilesInDirectory GetDirName
     LookForDirectories (GetDirName)
     Application.StatusBar = False
     Columns("A:A").EntireColumn.AutoFit
     Columns("B:B").EntireColumn.AutoFit
     Columns("C:C").EntireColumn.AutoFit
     Columns("D:D").EntireColumn.AutoFit
     Columns("E:E").EntireColumn.AutoFit
 Application.ScreenUpdating = True
 End If
 Exit Sub
errhandle:
 MsgBox "An error has occurred with name " & Err.Description & " and number " & Err.Number
 Exit Sub
End Sub

Public Sub ClearSheet()
 Worksheets(1).Range("A11:E11").Select
 Worksheets(1).Range(Selection, Selection.End(xlDown)).Select
 Selection.Clear
End Sub

Sub LookForDirectories(ByVal DirToSearch As String)
 Dim counter As Integer
 Dim i As Integer
 Dim Directories() As String
 Dim Contents As String
 counter = 0
 DirToSearch = DirToSearch & "\"
 Contents = Dir(DirToSearch, vbDirectory)
 Do While Contents <> ""
     If Contents <> "." And Contents <> ".." Then
         If (GetAttr(DirToSearch & Contents) And vbDirectory) = _
         vbDirectory Then
             counter% = counter% + 1
             ReDim Preserve Directories(counter)
             Directories(counter) = DirToSearch & Contents
         End If
     End If
     Contents = Dir()
 Loop
 If counter = 0 Then Exit Sub
 For i = 1 To counter
     GetFilesInDirectory Directories(i)
     LookForDirectories Directories(i)
 Next i
End Sub

Sub GetFilesInDirectory(ByVal DirToSearch As String)
 Dim NextFile As String
     ActiveSheet.Cells(writerow, 1).Value = DirToSearch
     writerow = writerow + 1
 NextFile = Dir(DirToSearch & "\" & "*.*")
 Do Until NextFile = ""
     Application.StatusBar = writerow & "  " _
     & DirToSearch & "\" & NextFile
     ActiveSheet.Cells(writerow, 1).Value = _
     DirToSearch
     ActiveSheet.Cells(writerow, 2).Value = _
     DirToSearch & "\" & NextFile
     ActiveSheet.Cells(writerow, 3).Value = _
     Mid(NextFile, InStr(NextFile, ".") + 1)
     ActiveSheet.Cells(writerow, 4).Value = _
     FileDateTime(DirToSearch & "\" & NextFile)
     ActiveSheet.Cells(writerow, 5).Value = _
     FileLen(DirToSearch & "\" & NextFile) ' Returns file length (bytes).
     writerow = writerow + 1
     NextFile = Dir()
 Loop
End Sub

Public Function SplitPath(ByVal Path As String, ReturnType As Integer) As String
Dim Drv, DirPath, File, Ext As String
Dim PathLength, Offset, ThisLength As Integer
Dim FileFound As Boolean
Drv = "": DirPath = "": File = "": Ext = ""
If Mid(Path, 2, 1) = ":" Then
  Drv = Left(Path, 2)
  Path = Mid(Path, 3)
End If
PathLength = Len(Path)
For Offset = PathLength To 1 Step -1
  Select Case Mid(Path, Offset, 1)
    Case ".":
      ThisLength = Len(Path) - Offset
      If ThisLength >= 1 And ThisLength <= 3 Then
        Ext = Mid(Path, Offset, ThisLength + 1)
      End If
      Path = Left(Path, Offset - 1)
    Case "\":
      ThisLength = Len(Path) - Offset
      If ThisLength >= 1 And ThisLength <= 40 Then
        File = Mid$(Path, Offset + 1, ThisLength)
        Path = Left(Path, Offset)
        DirPath = Path
        FileFound = True
        Exit For
      End If
    Case Else
  End Select
Next Offset
SplitPath = Drv & Path & File & Ext
Select Case ReturnType
  Case 1: SplitPath = Drv
  Case 2: SplitPath = Path
  Case 3: SplitPath = Drv & Path
  Case 4: SplitPath = File
  Case 5: SplitPath = File & Ext
  Case 6: SplitPath = Ext
End Select
End Function

-close and save and have fun

HTH:O)Bruintje
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Hi bruintje, let something to the others!!!
:))
Cheers
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
sorry...but msoffice is to quiet and all office q's end up here :)
0
 
LVL 3

Author Comment

by:Dmitriy
Comment Utility
Thanx for fast responce, bruintje.  I haven't had a chance to test the answer yet.  My friend helped me out with VB macro to the excel spreadsheet.  It works perfectly.

However, since you provided such a detailed answer, I will use it for my future refference.

Regards,
Dmitriy
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
thanks for the grade, and certainly check that last function to split dir/path/file something i found a few weeks back and is certainly a smart piece to use in future situations

thanks again
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now