I am new to the world of VB.NET and have been tasked to put together a little program to search a directory of about 2000 Excel spreadsheets and put together a list to display based on the value of a Custom Document Property within that spreadsheet file. Given that I am far from a computer programmer by education or trade, this has been an adventure.
I've gotten it to work, the results are fine. The problem is, it takes well over a minute to run. It is being run over a LAN connection. When I run it locally (using a 'test' directory of about 300 files) it executes in about 4 seconds.
I'm not sure even what to expect as a reasonable execution speed, so I thought I would ask here.
The code is below, if anyone thinks changes there might be of use in speeding things up.
Thank you in advance!
Private Sub listByPt()
Dim di As New IO.DirectoryInfo(dir_loc)
Dim aryFiles As IO.FileInfo() = di.GetFiles("*" & ext_to_check)
Dim fi As IO.FileInfo
Dim dso As DSOFile.OleDocumentProperties
Dim sfilename As String
Dim sheetInfo As Object
Dim sfileCount As String
Dim ifilesDone As Integer
Dim errorList As New ArrayList()
Dim ErrorFile As Object
Dim ErrorMessage As String
'Initialize progress bar values
ifilesDone = 0
sfileCount = di.GetFiles("*" & ext_to_check).Length
Me.lblHighProgress.Text = sfileCount
Me.lblLowProgress.Text = 0
With Me.progressMain
.Maximum = di.GetFiles("*" & ext_to_check).Length
.Minimum = 0
.Value = 0
End With
'Loop through all files in the search directory
For Each fi In aryFiles
dso = New DSOFile.OleDocumentProperties
sfilename = fi.FullName
Try
dso.Open(sfilename, True)
'grab the PT Initials off of the logsheet
Catch excep As Runtime.InteropServices.COMException
errorList.Add(sfilename)
End Try
Try
sheetInfo = dso.CustomProperties("PTNameChecker").Value
Catch ex As Runtime.InteropServices.COMException
sheetInfo = "NONE"
End Try
'Check to see if the initials on the log sheet
'match those we are searching for
If sheetInfo = lstInitials.SelectedValue Then
Dim logsheet As New LogSheet
logsheet.PTInitials = sheetInfo
logsheet.FileName = sfilename
PTFiles.Add(logsheet)
End If
'update progress bar
Me.progressMain.Increment(1)
ifilesDone = ifilesDone + 1
lblLowProgress.Text = ifilesDone
dso.Close()
Next
lstResults.Items.Clear()
'loop through results in the PTFiles list
'add results to the listbox, removing the path info
For Each showsheet As LogSheet In PTFiles
lstResults.Items.Add(Path.GetFileNameWithoutExtension(showsheet.FileName))
Next
'build error message to display to user
ErrorMessage = ""
For Each ErrorFile In errorList
ErrorMessage += ErrorFile & vbCrLf
Next
MsgBox("The following Log Sheets were unable to be checked" _
& vbCrLf & ErrorMessage)
PTFiles.Clear() 'empty PTFiles for next use
End Sub
