Solved

Search Through Excel Sheet Using VBScript

Posted on 2002-04-16
5
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
ID: 6944975
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
ID: 6945091
Hi bruintje, let something to the others!!!
:))
Cheers
0
 
LVL 44

Expert Comment

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

Author Comment

by:Dmitriy
ID: 6946020
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
ID: 6946034
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 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