PDA

View Full Version : [SOLVED] Use of File Array in Excel VBA



rkc2009
01-14-2009, 06:17 AM
Hey everyone. I needed some help on implementing an array that has individual files as its elements. The files are all formatted in the same way. I created an Excel template as follows:

BEGIN

NEXT

BACK

DONE

When the user clicks begin, the files in a specified folder are stored into the file array, and the first iteration of the macro must be run (i.e. for my case, I generate a plot from two columns of data in the first file). I want the next and back buttons to help me move through elements in the array. For example, if I click next, the macro operates on the second element in the array and stops. If I press back, the macro operates on the first element again. I was thinking of using a loop to go through all the elements in the array, but this would execute the macro continuously until all files were processed. I want it to work so that I only execute the macro on the next or previous file each time I click next or back. When I click done, the macro stops (Exit Sub). My question is how do I get the next and back buttons to work as I want, and what would be some type of VBA I could use to go one by one in the array rather than looping through all the elements?

Bob Phillips
01-14-2009, 06:43 AM
Here are a couple of procedures to get the next/previous array index


Private aryFiles As Variant
Private aryIndex As Long

Function NextInArray()
aryIndex = aryIndex + 1
If aryIndex > UBound(aryFiles) Then aryIndex = UBound(aryFiles)
NextInArray = aryIndex
End Function

Function PrevInArray()
aryIndex = aryIndex - 1
If aryIndex < LBound(aryFiles) Then aryIndex = Bound(aryFiles)
PrevInArray = aryIndex
End Function

rkc2009
01-14-2009, 06:59 AM
My code for storing files into array:


Sub FilesToArray(FileName As String)
For j = 1 To FS.FoundFiles.Count
FileArray(j) = FS.FoundFiles(j)
Next j
End Sub


I'm just slightly confused on how to use the functions you provided me. For example, if I pressed Next to execute the macro on the second element of the array, how would I use the functions you provided me? Thanks for your help thus far, much appreciated.

Kenneth Hobs
01-14-2009, 07:59 AM
I would recommend that you create a button to fill a sheet with the filenames. One column would have short filenames and the 2nd column would have the full paths. A Data Validation cell could let the user see all of the filenames and select the one they want. You can set your code to act on a change event for that cell to do your thing.

Using the 4 button method:
For the 4 buttons on a sheet, you will need to use Public variables to track the incremental values for the array of filenames. You can incorporate what xld posted into something like this.

In a Module, notice the Public variables. You can use your own routine to put the filenames into an array.

Public aFiles() As Variant
Public element As Long
Public countAFiles As Long

Function MyFiles(myFolder As String, wcFiles As String) As Variant
'Requires reference to Microsoft Scripting Runtime
Dim cFiles As New Scripting.Dictionary
Dim FileName As String, a() As Variant
'Put filenames into dictionary
FileName = myFolder & "\" & Dir(wcFiles)
Do While FileName <> myFolder & "\"
cFiles.Add FileName, Nothing
FileName = myFolder & "\" & Dir
Loop
'Return keys or items as an array
If cFiles.Count > 0 Then
a = cFiles.Keys
MyFiles = a
Else
ReDim a(1) As Variant
a(0) = "NA"
MyFiles = a
End If
Set cFiles = Nothing
End Function


Right Click the sheet tab with the 4 buttons named as shown below, View Code and paste this code. Change the MsgBox as needed to act on the selected filename.


Private Sub BeginBttn_Click()
Dim myFolder As String, wcFiles As String
'Input parameters
myFolder = "x:\MsWord" 'No trailing backslash
wcFiles = "*.xls"
aFiles = MyFiles(myFolder, wcFiles)
'Check to see if s() has any filenames
If aFiles(0) = "NA" Then Exit Sub
MsgBox "Found " & UBound(aFiles) + 1 & ", " & wcFiles & ", files in:" & vbCrLf & myFolder
element = 0
countAFiles = UBound(aFiles) + 1
End Sub

Private Sub NextBttn_Click()
IncrementAFiles 1
End Sub

Private Sub BackBttn_Click()
IncrementAFiles -1
End Sub

Private Sub DoneBttn_Click()
Erase aFiles
MsgBox "Array of filenames as be erased. Begin must be ran to get array of filenames.", vbInformation
End Sub

Sub IncrementAFiles(inc As Integer)
Dim bttn As String, lf As String
bttn = "Back"
lf = "first"
If inc > 0 Then
bttn = "Next"
lf = "last"
End If
On Error GoTo TheEnd
If aFiles(0) = "NA" Then
MsgBox "Run Begin again as no files were found.", vbCritical
Exit Sub
End If
On Error GoTo 0
element = element + inc
If element > countAFiles - 1 Then
element = countAFiles
MsgBox "You were at the " & lf & " element in the file list already.", vbInformation
Exit Sub
End If
If element < 0 Then
element = 0
MsgBox "You were at the " & lf & " element in the file list already.", vbInformation
Exit Sub
End If
MsgBox bttn & " file is: " & aFiles(element)
Exit Sub
TheEnd:
MsgBox "Run Begin as no files were found.", vbCritical
End Sub

Bob Phillips
01-14-2009, 08:32 AM
My code for storing files into array:


Sub FilesToArray(FileName As String)
For j = 1 To FS.FoundFiles.Count
FileArray(j) = FS.FoundFiles(j)
Next j
End Sub


I'm just slightly confused on how to use the functions you provided me. For example, if I pressed Next to execute the macro on the second element of the array, how would I use the functions you provided me? Thanks for your help thus far, much appreciated.



myFile = FileArray(NextInArray)

'or


myFile = FileArray(PrevInArray)


or if it is workbook objects in the array then



Set myFile = FileArray(NextInArray)

'or


Set myFile = FileArray(PrevInArray)