bftbeckett
New Member
- Joined
- Apr 20, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
I have never had to create a mapping file before so I need help. I am not even sure that generating a mapping file is the correct function to use. I call it a mapping file because basically, I am attempting to split out the worksheeets of an workbook into their own files, put the worksheet name and the file name into a file that shows the worksheet names and the associated file name. Mapping file seems like the best route. I am using an array to generate the data.
VBA Code:
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