Need help with creating a mapping file using array

bftbeckett

New Member
Joined
Apr 20, 2022
Messages
6
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,176,636
Messages
5,904,165
Members
435,076
Latest member
khoadientu247

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top