Reputation: 1793
I have a folder with many files, out of which I need to: open the files for this week, store them in an array, pass them to a sub, and loop through them for getting summary information.
I am able to get the desired day files from the below code. But the code is throwing an error for storing it in the array and passing it to the array.
Sub BatchProcessing()
firstday = Date - Weekday(Date) + 2 'To get the 1st day of week
lastday = Date - Weekday(Date) + 6 'To get the 5th day of week
MyPath = "P:\Data\" 'Path where my files were present
Dim Day
Dim strArray(0 To 5) As String
iCount=0
For Day = firstday To lastday 'To loop through all 5 day files
formatted_date = Format(Day, "yyyyMd")
MyTemplate = "TestFile" & formatted_date & ".xlsx" ' Set the template.
Workbooks.Open MyPath & MyTemplate
strArray(iCount) = ActiveWorkbook.Name
iCount = iCount+1
Next
CreateStats(strArray) 'Calling a sub which will do the required calculation
End Sub
Sub CreateStats(strArray As String)
For Each element in strArray
set OriginalWorkbook = strArray(i)
'Do the processing'
Next
End Sub
Upvotes: 1
Views: 3433
Reputation: 5867
Your strArray
variable is of type Single
. If you want that variable to be a string array, you must declare it as such:
Dim strArray(0 to 5) As String
EDIT:
Now that you've changed your code to use strArray() As String
rather than strArray As Single
, you should update your CreateStats
sub procedure to accept an array as a parameter. It should now look something like this:
Private Sub CreateStats(myArray() As String)
As you have it now, your procedure only accepts a single string. It must accept an array of strings. Once you have that, you can loop through each string and do your processing.
Upvotes: 3
Reputation: 2587
By naming your array strArray
it appears that you are going to have an array of strings, and in fact you attempt to store workbook names in it. However, you declared array to be Single, which is a numeric data type. Depending on what your CreateStats(strArray)
sub does, you may need to change that from Single to String or possibly set up another array to hold the Single and this one to hold the String.
Upvotes: 1