I have an Excel file that has multiple sheets. Each sheet has the same format (same header, fields, etc). There are multiple sheets since Excel only allows 65535 rows max per sheet. In the overall workbook I have 3-5 sheets each with different names. The names of the sheets are dynamic (and irrelevant from my perspective) and change on each instance of the Excel workbook file. All of the data is ultimately loaded into an Oracle database. Can this be done?

1. Declare a global array.

2 . Get all sheet names into above array

Option Explicit

Dim ExcelSheet As Object
Dim I

Set ExcelSheet   = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "FileName" 'The excel file name

For I = 1 To ExcelSheet.ActiveWorkbook.Worksheets.Count
  MsgBox(ExcelSheet.ActiveWorkbook.Worksheets(I).Name) 'Put sheet name to the global array
Next I
 

3. loop each sheet go through Pervasive Map one by one.

4. In map use "change source" (or "dynamic map" if you are in version 9)