?
Solved

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

Posted on 2003-03-18
37
Medium Priority
?
212 Views
Last Modified: 2013-11-25
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!
0
Comment
Question by:zoomer777
[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
  • 19
  • 18
37 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8160597
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8160616
Also, Excel has all what you need to manage data in worksheet by Automation.
0
 

Author Comment

by:zoomer777
ID: 8162080
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8162121
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
 

Author Comment

by:zoomer777
ID: 8162153
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
 

Author Comment

by:zoomer777
ID: 8166840
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8166973
"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
 

Author Comment

by:zoomer777
ID: 8167001
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8167119
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8167139
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8167148
That code was extracted from www.mvps.org/vbnet
0
 

Author Comment

by:zoomer777
ID: 8168785
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
 

Author Comment

by:zoomer777
ID: 8168799
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8169022
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
 

Author Comment

by:zoomer777
ID: 8169374
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8169749
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
 

Author Comment

by:zoomer777
ID: 8169806
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
 

Author Comment

by:zoomer777
ID: 8169821
oh yeah, it wasn't just the way i did it, but your code did the same thing with the infinite loop
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8169877
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8169886
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8169891
i have to go home so i could not see any new messages until tomorrow (here is 7:00 PM)
C U Tomorrow.
0
 

Author Comment

by:zoomer777
ID: 8175891
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8175957
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
 

Author Comment

by:zoomer777
ID: 8176092
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
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 1200 total points
ID: 8176134
what a stupid person i am!

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

with
Set wb = Application.Workbooks.Open(arr(i))
0
 

Author Comment

by:zoomer777
ID: 8176155
It worked!!!!!!!!!!!!!!!!!!!

I can't say that you are stupid though!  You have been marvelous help.  Points well earned!
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8176188
Uf! at least!!
:D
0
 

Author Comment

by:zoomer777
ID: 8176197
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8176264
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
 

Author Comment

by:zoomer777
ID: 8176306
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8176507
I don't get the error. Plase, recheck what you have added.
Take a look where do you get subscript out of range.
0
 

Author Comment

by:zoomer777
ID: 8181135
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8181266
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8181279
0
 

Author Comment

by:zoomer777
ID: 8181573
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
 

Author Comment

by:zoomer777
ID: 8181688
i am going to go ahead and award you the points here and start this as a new question!

0
 

Author Comment

by:zoomer777
ID: 8181701
See previous responses for the code used...
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…

800 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