Batch File Processing, Visual Basic for Excel, Visual Basic .NET?

Hello to all the experts out there.

I don't know how "difficult" this question is, so here it goes.

I am trying to write some scripts that will process data.  Ideally, I want to be able to batch process groups of files.  We have a large collection of data that needs to be processed, and we want a script to automate the process.  I am new to VB, but I really like how easy it is to use and code, so I am trying to figure out what the limitations of the software are, and what tools I will need to accomplish the task at hand.

Right now, I am using the VB editor that is included in MS Office, which is obviously a version of VB designed specifically for use with Office (and in my case, MS Excel).  SO far, this editor is meeting our needs, but we want to add the ability to batch process files.  Basically, we would like to run a script that can search through a folder of our choosing, and search all the *.xls files and process the data as we see fit.  This will involve the creation of new data files, reading from source data files (most likely still in the excel format) and processing the source data as well (appending).  Is all this possible with the built in VB Editor or should we look into buying the VB package itself so we further utilize the power of this coding language?  If we can use the built in editor, any examples of how to open files and batch process them, then close those opened files?

If we need to purchase the full VB package, can we still use it to easily process excel data as we are currently doing with the VB for Applications software that comes with MS Office?  Or does the syntax change when you move to VB.net, etc (referencing to specific cells, ranges, performing mathematical computation, etc)?

Thanks for the time and answers!
zoomer777Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Richie_SimonettiConnect With a Mentor IT OperationsCommented:
what a stupid person i am!

replace
Set wb = Application.Workbooks.Open(sPath & "\" & ffile)

with
Set wb = Application.Workbooks.Open(arr(i))
0
 
Richie_SimonettiIT OperationsCommented:
You don't need full VB. For the description of the task, VBA would be enougth.
These are some clues:
'searching a folder
sub SearchFiles(sPath as string)
dim arrFiles() as string,ffile as string
dim i as integer

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
   redim preserve arrfiles(i)
   arrfiles(i) = spath & "\" & ffile
   i=i+1
   ffile=dir$()
loop

Arrfiles is an array that would be populated which every path and file for a specific folder given in the sPath parameter.
Cheers
0
 
Richie_SimonettiIT OperationsCommented:
Also, Excel has all what you need to manage data in worksheet by Automation.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
zoomer777Author Commented:
Richie,

So where would the processing code be placed in this example that you have given?  Would it come after the loop?  Somewhere in the loop?  Sorry, I am trying to learn!  Where am I supposed to put the data path location?  And will it be in this format: "c:\documents and settings\..."?

sub SearchFiles(sPath as string *folder location here?*)
dim arrFiles() as string,ffile as string
dim i as integer

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
  redim preserve arrfiles(i)
  arrfiles(i) = spath & "\" & ffile
  i=i+1
  ffile=dir$()
****code here****?
loop

thanks for the extra help
0
 
Richie_SimonettiIT OperationsCommented:
Well, you have to choices:
(by the way, what did you suspect about parameter is correct)

1) Populate the array and processing it again to load each file
2) Not using array at all and processing every file as it is found.
let me know.
0
 
zoomer777Author Commented:
I think that processing each file as it is found would be sufficient.  I don't see if there are any plus/minuses to each method.  
0
 
zoomer777Author Commented:
I guess the way that would be cleanest for us would be to process through a file at a time and then close that file.  We obviously don't want to eat up system memory by opening up thousands of windows (and we do have thousands of files to process!).
0
 
Richie_SimonettiIT OperationsCommented:
"I guess the way that would be cleanest for us would be to process through a file at a time and then close that file.  We obviously don't want to eat up system memory by opening up thousands of windows (and we do have thousands of files to process!). "

I agree with you 100%! If you don't know how to open each file, process it and close it, just ask.
0
 
zoomer777Author Commented:
Well, i know how to open the file myself, run a script on it, then close it, but I want this to be automated.  I want to execute a script that will search a location for all of a specific file type, process each one as it is opened, then move on to the next one.  I am guessing the sample code you already put in here will work, I just need the questions from above answered I guess.

from before:

So where would the processing code be placed in this example that you have given?  Would it come after the loop?  Somewhere in the loop?  Sorry, I am trying to learn!  Where am I supposed to put the data path location?  And will it be in this format: "c:\documents and settings\..."?

sub SearchFiles(sPath as string *folder location here?*)
dim arrFiles() as string,ffile as string
dim i as integer

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
 redim preserve arrfiles(i)
 arrfiles(i) = spath & "\" & ffile
 i=i+1
 ffile=dir$()
****code here****?
loop

thanks for the extra help


------------------------------------

0
 
Richie_SimonettiIT OperationsCommented:
sub SearchFiles(sPath as string *folder location here? (YES!!)*)
dim ffile as string
dim wb as workbook

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
   set wb = application.workbooks.open(spath & "\" & ffile)
   '*** Convert to html here******
   with wb
       .saved=true
       .close
   end with
   set wb = nothing
   ffile=dir$()

loop
0
 
Richie_SimonettiIT OperationsCommented:
You could dinamically change the target folder with this little code:

'This module contains all the declarations to use the
'Windows 95 Shell API to use the browse for folders
'dialog box.  To use the browse for folders dialog box,
'please call the BrowseForFolders function using the
'syntax: stringFolderPath=BrowseForFolders(Hwnd,TitleOfDialog)
'
'For contacting information, see other module

Option Explicit

Public Type BrowseInfo
     hwndOwner As Long
     pIDLRoot As Long
     pszDisplayName As Long
     lpszTitle As Long
     ulFlags As Long
     lpfnCallback As Long
     lParam As Long
     iImage As Long
End Type

Public Const BIF_RETURNONLYFSDIRS = 1
Public Const MAX_PATH = 260

Public Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Public Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Public Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Public Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long

Public Function BrowseForFolder(hwndOwner As Long, sPrompt As String) As String
     
    'declare variables to be used
     Dim iNull As Integer
     Dim lpIDList As Long
     Dim lResult As Long
     Dim sPath As String
     Dim udtBI As BrowseInfo

    'initialise variables
     With udtBI
        .hwndOwner = hwndOwner
        .lpszTitle = lstrcat(sPrompt, "")
        .ulFlags = BIF_RETURNONLYFSDIRS
     End With

    'Call the browse for folder API
     lpIDList = SHBrowseForFolder(udtBI)
     
    'get the resulting string path
     If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        lResult = SHGetPathFromIDList(lpIDList, sPath)
        Call CoTaskMemFree(lpIDList)
        iNull = InStr(sPath, vbNullChar)
        If iNull Then sPath = Left$(sPath, iNull - 1)
     End If

    'If cancel was pressed, sPath = ""
     BrowseForFolder = sPath
End Function
0
 
Richie_SimonettiIT OperationsCommented:
That code was extracted from www.mvps.org/vbnet
0
 
zoomer777Author Commented:
Alright, what do you mean, convert to HTML here?  I am going to stick the code I want in HTML format now?  I'm confused.  Here is part of the code that I have:

Sub BatchProcess(sPath As String)
    Dim R As Range
    Set R = ActiveSheet.Range("m7:m300")
    Dim wb As Workbook
    stringFolderPath = BrowseForFolders(HWnd, TitleOfDialog)
    ffile = Dir$(sPath & "\*.xls", vbArchive)
    Do While ffile <> ""
      Set wb = Application.Workbooks.Open(sPath & "\" & ffile)
      '*** Convert to html here******
      With wb
          .Saved = True
          .Close
      End With
      Set wb = Nothing
      ffile = Dir$()
   
    Loop

I have the syntax inserted with the extra function that will allow my to dynamically select the folder I want to search.  Where you have "Convert to html here", I have no idea what that means, do I insert my code there?

Thanks again...
0
 
zoomer777Author Commented:
I am going to try to increase the point value for this question as I believe it's increased in difficulty, and is taking more time than just a "simple" or "easy" level!
0
 
Richie_SimonettiIT OperationsCommented:
Sorry, i did a mistake. I was answering other question in YOUR question (:D)

You have added the code incorrectly. It should be:

Sub BatchProcess()
   Dim R As Range
   Dim wb As Workbook
   Dim sPath As String

   sPath = BrowseForFolders(0, "Select source folder:")
   if sPath = "" then exit sub
   ffile = Dir$(sPath & "\*.xls", vbArchive)
   Do While ffile <> ""
     Set wb = Application.Workbooks.Open(sPath & "\" & ffile)
     Set R = wb.ActiveSheet.Range("m7:m300")

     'do your processing code here
     With wb
         .Saved = True
         .Close
     End With
     Set wb = Nothing
     ffile = Dir$()
   
   Loop
end sub
0
 
zoomer777Author Commented:
I had to declare a few more variables, but finally it ran!  Let me choose and folder, and the appears to go through each file in that folder, but the only problem is that my code doesn't appear to do the formatting that I requested.  Also, with the processing script on its own, I never had to actually declare "c" in the For loops, it ran just fine, but this time, it asked me to declare it, and I had to end up declaring it as an object (not as "single", etc).  Maybe this belongs as a new question then, but why is the example processing code below not actually altering the workbooks as they are opened?

Sub BatchProcess()
  Dim R As Range
  Dim wb As Workbook
  Dim sPath, ffile As String
  Dim c As Object
  sPath = BrowseForFolder(0, "Select source folder:")
  If sPath = "" Then Exit Sub
  ffile = Dir$(sPath & "\*.xls", vbArchive)
  Do While ffile <> ""
    Set wb = Application.Workbooks.Open(sPath & "\" & ffile)
    Set R = wb.ActiveSheet.Range("m7:m300")
    For Each c In R
        If c.Offset(0, -5).Value = 0 Or c.Offset(-1, -5).Value = 0 Then
            c.Clear
        End If
    Next c
    For Each c In R
        If IsError(c) Then
        ElseIf c.Value <= 1 And c.Value > 0 Then
            If c.Value <= 1 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
                c.Interior.ColorIndex = 6
                c.Offset(0, 1).Interior.ColorIndex = 6
            Else
                c.Interior.ColorIndex = 6
            End If
        End If
    Next c
    For Each c In R
        If IsError(c) Then
        ElseIf c.Value <= 0.25 And c.Value > 0 Then
                c.Borders.LineStyle = xlContinuous
                c.Borders.Weight = xlThick
                c.Borders.ColorIndex = 1
        End If
    Next c
    For Each c In R
        If IsError(c) Then
        ElseIf c.Value <= 0.5 And c.Value > 0 Then
            If c.Value <= 0.5 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
                c.Interior.ColorIndex = 3
                c.Offset(0, 1).Interior.ColorIndex = 3
            End If
        End If
    Next c
    For Each c In R
        If IsError(c) Then
        ElseIf c.Value <= 0.5 And c.Value > 0.25 Then
            If c.Value <= 0.5 And c.Value > 0.25 And c.Offset(0, 1).Value >= 300 Then
                c.Interior.ColorIndex = 33
            End If
        End If
    Next c
    With wb
        .Saved = True
        .Close
    End With
    Set wb = Nothing
    ffile = Dir$()
  Loop
End Sub

It just runs through all the files in the folder I choose, appears to run the script on it, but i open the files after it finishes and everything is the same!

0
 
Richie_SimonettiIT OperationsCommented:
c should be declare as Range.
Did you do a step-by-step debug?
Also, there is a part of code that doesn't do any save, it is the following:
With wb
       .Saved = True
       .Close
   End With

so, change to:
wb.Close true

That's my mistake, sorry again.

Complete code should be:

Sub BatchProcess()
 Dim R As Range, c As Range
 Dim wb As Workbook
 Dim sPath, ffile As String

 sPath = BrowseForFolder(0, "Select source folder:")
 If sPath = "" Then Exit Sub
 
 ffile = Dir$(sPath & "\*.xls", vbArchive)
 Do While ffile <> ""
   Set wb = Application.Workbooks.Open(sPath & "\" & ffile)
   Set R = wb.ActiveSheet.Range("m7:m300")
   For Each c In R
       If c.Offset(0, -5).Value = 0 Or c.Offset(-1, -5).Value = 0 Then
           c.Clear
       End If
   Next c
   For Each c In R
       If IsError(c) Then
       ElseIf c.Value <= 1 And c.Value > 0 Then
           If c.Value <= 1 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
               c.Interior.ColorIndex = 6
               c.Offset(0, 1).Interior.ColorIndex = 6
           Else
               c.Interior.ColorIndex = 6
           End If
       End If
   Next c
   For Each c In R
       If IsError(c) Then
       ElseIf c.Value <= 0.25 And c.Value > 0 Then
               c.Borders.LineStyle = xlContinuous
               c.Borders.Weight = xlThick
               c.Borders.ColorIndex = 1
       End If
   Next c
   For Each c In R
       If IsError(c) Then
       ElseIf c.Value <= 0.5 And c.Value > 0 Then
           If c.Value <= 0.5 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
               c.Interior.ColorIndex = 3
               c.Offset(0, 1).Interior.ColorIndex = 3
           End If
       End If
   Next c
   For Each c In R
       If IsError(c) Then
       ElseIf c.Value <= 0.5 And c.Value > 0.25 Then
           If c.Value <= 0.5 And c.Value > 0.25 And c.Offset(0, 1).Value >= 300 Then
               c.Interior.ColorIndex = 33
           End If
       End If
   Next c
   wb.Close True
   Set wb = Nothing
   ffile = Dir$()
 Loop
End Sub

0
 
zoomer777Author Commented:
alrighty!  i did it an alternate way, but I figured it might not be saving when it was finished, so the file obviously reflects as if nothing happened.  However, now I am stuck in an infinite loop!  The script will execute correctly all the way through a group of files, but now it loops infinitely, going back to the first file in the loop and restarting the process.  I have to hit escape to cancel the process!  Any idea?
0
 
zoomer777Author Commented:
oh yeah, it wasn't just the way i did it, but your code did the same thing with the infinite loop
0
 
Richie_SimonettiIT OperationsCommented:
Ouch!, well, then we have to go to our first approach and use the array:

sub SearchFiles(sPath as string)
dim arrFiles() as string,ffile as string
dim i as integer

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
  redim preserve arrfiles(i)
  arrfiles(i) = spath & "\" & ffile
  i=i+1
  ffile=dir$()
loop
Call ProcessFiles(arrfiles)
end sub

sub ProcessFiles(arr() as string)
dim i as integer
Dim R As Range, c As Range
Dim wb As Workbook

for i= 0 to ubound(arr)-1
Set wb = Application.Workbooks.Open(sPath & "\" & ffile)
  Set R = wb.ActiveSheet.Range("m7:m300")
  For Each c In R
      If c.Offset(0, -5).Value = 0 Or c.Offset(-1, -5).Value = 0 Then
          c.Clear
      End If
  Next c
  For Each c In R
      If IsError(c) Then
      ElseIf c.Value <= 1 And c.Value > 0 Then
          If c.Value <= 1 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
              c.Interior.ColorIndex = 6
              c.Offset(0, 1).Interior.ColorIndex = 6
          Else
              c.Interior.ColorIndex = 6
          End If
      End If
  Next c
  For Each c In R
      If IsError(c) Then
      ElseIf c.Value <= 0.25 And c.Value > 0 Then
              c.Borders.LineStyle = xlContinuous
              c.Borders.Weight = xlThick
              c.Borders.ColorIndex = 1
      End If
  Next c
  For Each c In R
      If IsError(c) Then
      ElseIf c.Value <= 0.5 And c.Value > 0 Then
          If c.Value <= 0.5 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
              c.Interior.ColorIndex = 3
              c.Offset(0, 1).Interior.ColorIndex = 3
          End If
      End If
  Next c
  For Each c In R
      If IsError(c) Then
      ElseIf c.Value <= 0.5 And c.Value > 0.25 Then
          If c.Value <= 0.5 And c.Value > 0.25 And c.Offset(0, 1).Value >= 300 Then
              c.Interior.ColorIndex = 33
          End If
      End If
  Next c
  wb.Close True
  Set wb = Nothing

next i
end sub
0
 
Richie_SimonettiIT OperationsCommented:
ups! i did it again:
sub SearchFiles()
dim arrFiles() as string,ffile as string
dim i as integer
dim sPath as string

sPath = BrowseForFolders(0, "Select source folder:")
  if sPath = "" then exit sub

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
 redim preserve arrfiles(i)
 arrfiles(i) = spath & "\" & ffile
 i=i+1
 ffile=dir$()
loop
Call ProcessFiles(arrfiles)
end sub

0
 
Richie_SimonettiIT OperationsCommented:
i have to go home so i could not see any new messages until tomorrow (here is 7:00 PM)
C U Tomorrow.
0
 
zoomer777Author Commented:
This code isn't working right, it's giving me an error once the folder of choice has been selected that it cannot find the target "C:\", even though I did not pick the root drive as the target!
0
 
Richie_SimonettiIT OperationsCommented:
I don't know why. I tested the code to some extents and it works.

test it by yourself with this:


sub SearchFiles()
dim arrFiles() as string,ffile as string
dim i as integer
dim sPath as string

sPath = BrowseForFolders(0, "Select source folder:")
msgbox sPath
if sPath = "" then exit sub
If Right$(spath, 1) = "\" Then
   spath = Mid$(spath, 1, Len(spath) - 1)
End If

ffile=Dir$(sPath & "\*.xls",vbarchive)
do while ffile<>""
redim preserve arrfiles(i)
arrfiles(i) = spath & "\" & ffile
i=i+1
ffile=dir$()
loop
Call ProcessFiles(arrfiles)
end sub

0
 
zoomer777Author Commented:
here is the code as I have it set:

Sub SearchFiles()
Dim arrFiles() As String, ffile As String
Dim i As Integer
Dim sPath As String

sPath = BrowseForFolder(0, "Select source folder:")
MsgBox sPath
If sPath = "" Then Exit Sub
If Right$(sPath, 1) = "\" Then
  sPath = Mid$(sPath, 1, Len(sPath) - 1)
End If

ffile = Dir$(sPath & "\*.xls", vbArchive)
Do While ffile <> ""
ReDim Preserve arrFiles(i)
arrFiles(i) = sPath & "\" & ffile
i = i + 1
ffile = Dir$()
Loop
Call ProcessFiles(arrFiles)
End Sub

_____________________

Sub ProcessFiles(arr() As String)
Dim i As Integer
Dim R As Range, c As Range
Dim wb As Workbook
Dim sPath, ffile As String
For i = 0 To UBound(arr) - 1
Set wb = Application.Workbooks.Open(sPath & "\" & ffile)
 Set R = wb.ActiveSheet.Range("m7:m300")
 For Each c In R
     If c.Offset(0, -5).Value = 0 Or c.Offset(-1, -5).Value = 0 Then
         c.Clear
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 1 And c.Value > 0 Then
         If c.Value <= 1 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
             c.Interior.ColorIndex = 6
             c.Offset(0, 1).Interior.ColorIndex = 6
         Else
             c.Interior.ColorIndex = 6
         End If
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 0.25 And c.Value > 0 Then
             c.Borders.LineStyle = xlContinuous
             c.Borders.Weight = xlThick
             c.Borders.ColorIndex = 1
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 0.5 And c.Value > 0 Then
         If c.Value <= 0.5 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
             c.Interior.ColorIndex = 3
             c.Offset(0, 1).Interior.ColorIndex = 3
         End If
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 0.5 And c.Value > 0.25 Then
         If c.Value <= 0.5 And c.Value > 0.25 And c.Offset(0, 1).Value >= 300 Then
             c.Interior.ColorIndex = 33
         End If
     End If
 Next c
 wb.Close True
 Set wb = Nothing

Next i
End Sub

___________________________________
There is also the public functions to call the Folder Selection popup of course, but not included because it works fine.  The script runs, shows the dialogue popup with the correct folder selection, but halts with an error "1004" on this line:

Set wb = Application.Workbooks.Open(sPath & "\" & ffile)

Saying "C:\" cannot be found.

Any idea why?  It appears that maybe the array of file locations is not being passed correctly?
____________________________________
0
 
zoomer777Author Commented:
It worked!!!!!!!!!!!!!!!!!!!

I can't say that you are stupid though!  You have been marvelous help.  Points well earned!
0
 
Richie_SimonettiIT OperationsCommented:
Uf! at least!!
:D
0
 
zoomer777Author Commented:
actually before I award the points, there might be a problem with subfolders not being processed correctly?  does this code properly look into subfolders in the folder you choose?  I get an error message run-time "9" "subscript out of range" if I attempt to choose a folder that has sub folders.

more ideas?
0
 
Richie_SimonettiIT OperationsCommented:
Well, we never did talk about subfolders. It only proccess files in the current folder and not scan for subfolders.

Redo the function as:

Sub SearchFiles()
Dim arrFiles() As String, ffile As String
Dim i As Integer
Dim sPath As String

sPath = BrowseForFolder(0, "Select source folder:")
MsgBox sPath
If sPath = "" Then Exit Sub
If Right$(sPath, 1) = "\" Then
 sPath = Mid$(sPath, 1, Len(sPath) - 1)
End If

ffile = Dir$(sPath & "\*.xls", vbArchive)
Do While ffile <> ""
    If Not ((GetAttr(sPath & "\" & ffile) And vbDirectory) = vbDirectory) Then
        ReDim Preserve arrFiles(i)
        arrFiles(i) = sPath & "\" & ffile
        i = i + 1
    End If
    ffile = Dir$()
Loop
Call ProcessFiles(arrFiles)
End Sub
0
 
zoomer777Author Commented:
still getting the out of range error with that new sub!  is there something in the ProcessFiles line that needs to be changed?

For i = 0 To UBound(arr) - 1

0
 
Richie_SimonettiIT OperationsCommented:
I don't get the error. Plase, recheck what you have added.
Take a look where do you get subscript out of range.
0
 
zoomer777Author Commented:
Actually, the scripts both work just fine if there is no sub folders.  I don't think the second script you gave me is properly searching subfolders:

Sub SearchFiles()
Dim arrFiles() As String, ffile As String
Dim i As Integer
Dim sPath As String

sPath = BrowseForFolder(0, "Select source folder:")
MsgBox sPath
If sPath = "" Then Exit Sub
If Right$(sPath, 1) = "\" Then
sPath = Mid$(sPath, 1, Len(sPath) - 1)
End If

ffile = Dir$(sPath & "\*.xls", vbArchive)
Do While ffile <> ""
   If Not ((GetAttr(sPath & "\" & ffile) And vbDirectory) = vbDirectory) Then
       ReDim Preserve arrFiles(i)
       arrFiles(i) = sPath & "\" & ffile
       i = i + 1
   End If
   ffile = Dir$()
Loop
Call ProcessFiles(arrFiles)
End Sub


Because it won't search subfolders, it will pass an empty array to the ProcessFiles function, which in turn will return an error because there is no file to process.

Any fixes to this code you sent?

 
0
 
Richie_SimonettiIT OperationsCommented:
As i told you and you said so in you question:

"....Basically, we would like to run a script that can search through a folder of our choosing, and search all the *.xls files and process the data as we see fit...."

We did never talk about subfolders. That is a complete different situation.
Regarding the error when it didn't found any file:

Sub SearchFiles()
Dim arrFiles() As String, ffile As String
Dim i As Integer
Dim sPath As String
dim flagFound as boolean


sPath = BrowseForFolder(0, "Select source folder:")
MsgBox sPath
If sPath = "" Then Exit Sub
If Right$(sPath, 1) = "\" Then
sPath = Mid$(sPath, 1, Len(sPath) - 1)
End If

ffile = Dir$(sPath & "\*.xls", vbArchive)
flagfound=false
Do While ffile <> ""

   If Not ((GetAttr(sPath & "\" & ffile) And vbDirectory) = vbDirectory) Then
       flagfound= true
       ReDim Preserve arrFiles(i)
       arrFiles(i) = sPath & "\" & ffile
       i = i + 1
   End If
   ffile = Dir$()
Loop
if flagfound then Call ProcessFiles(arrFiles)
End Sub
0
 
Richie_SimonettiIT OperationsCommented:
0
 
zoomer777Author Commented:
well, that makes for a nice interface, but since I am still a retard with VB, anyway you can modify the code you gave me to work for searching sub folders?
0
 
zoomer777Author Commented:
i am going to go ahead and award you the points here and start this as a new question!

0
 
zoomer777Author Commented:
See previous responses for the code used...
0
All Courses

From novice to tech pro — start learning today.