Subscript out of bounds help

[bftbeckett]

I am attempting to create an array to create a mapping file but I keep getting a Subscript out of bounds error on the tempArray function. I can't figure out what I need to do.

Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
Dim itemCount As Integer
Dim tempArray() As Variant
Dim result As Variant
Dim Filename As Variant
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False


For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "/" & ws.Name & " " & Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0) & Int((99999 - 1 + 1) * Rnd + 1) & ".csv"
Next
itemCount = 0
result = Filename
tempArray(Filename) = result
result = ""
itemCount = itemCount + 1
Application.ActiveWorkbook.Close False
generateMappingFile (tempArray)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[offthelip]

You get subscript out of bounds because your Variable "Filename" is empty. this is because you have never loaded anything into it. Your variable which you have called Filename is a completely different entity to the "filename" which is a parameter which is passed to the workbooksaveas method. It is NOT good practice to use variable names for any of the VBA or EXCEL functions of parameters
Try this :

Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
Dim itemCount As Integer
Dim tempArray() As Variant
Dim result As Variant
Dim Fname As Variant
Dim wscnt As Integer
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
wscnt = Worksheets.Count
ReDim tempArray(1 To wscnt)
For Each ws In ThisWorkbook.Sheets
ws.Copy
Fname = FPath & "/" & ws.Name & " " & Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0) & Int((99999 - 1 + 1) * Rnd + 1) & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=Fname
itemCount = 1
'result = Fname
tempArray(itemCount) = Fname
'result = ""
itemCount = itemCount + 1
Next

Application.ActiveWorkbook.Close False
generateMappingFile (tempArray)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Note I presume you have generatemappingfile subroutine that takes an array.

 

[bftbeckett]

You get subscript out of bounds because your Variable "Filename" is empty. this is because you have never loaded anything into it. Your variable which you have called Filename is a completely different entity to the "filename" which is a parameter which is passed to the workbooksaveas method. It is NOT good practice to use variable names for any of the VBA or EXCEL functions of parameters
Try this :

Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
Dim itemCount As Integer
Dim tempArray() As Variant
Dim result As Variant
Dim Fname As Variant
Dim wscnt As Integer
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
wscnt = Worksheets.Count
ReDim tempArray(1 To wscnt)
For Each ws In ThisWorkbook.Sheets
ws.Copy
Fname = FPath & "/" & ws.Name & " " & Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0) & Int((99999 - 1 + 1) * Rnd + 1) & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=Fname
itemCount = 1
'result = Fname
tempArray(itemCount) = Fname
'result = ""
itemCount = itemCount + 1
Next

Application.ActiveWorkbook.Close False
generateMappingFile (tempArray)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Note I presume you have generatemappingfile subroutine that takes an array.

Thank you so much for this.

This is my first time actually building a mapping file so I could use some help. What is your recommendation for the generatemappingfile subroutine to take an array and actual get the file?

 

[offthelip]

I think you probably need to raise another question about that because it is completely unrelated to the title of this thread.
By the way I spotted an error in my code. I have put the initialisation of itemcount in th wrong place try this:

Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
Dim itemCount As Integer
Dim tempArray() As Variant
Dim result As Variant
Dim Fname As Variant
Dim wscnt As Integer
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
wscnt = Worksheets.Count
ReDim tempArray(1 To wscnt)
itemCount = 1                       ' move this up to here
For Each ws In ThisWorkbook.Sheets
ws.Copy
Fname = FPath & "/" & ws.Name & " " & Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0) & Int((99999 - 1 + 1) * Rnd + 1) & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=Fname
'result = Fname
tempArray(itemCount) = Fname
'result = ""
itemCount = itemCount + 1
Next

Application.ActiveWorkbook.Close False
generateMappingFile (tempArray)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub