Hello smisra, and welcome,
Monarch treats data from Excel sources as a database, not as a report. When you open an Excel file in Monarch, you have to choose which worksheet (tab) or named range you want Monarch to work from. Monarch will then take that set of data as input for the table. If you have an Excel file where, say, sales for January are on the first tab, for February on the second tab, and so on, then there are two approaches you can take.
The first option is to export each tab from Excel as a separate CSV file (comma separated values). You can then get Monarch to open all the CSV files together. This works as long as all the tabs have teh same number of fields and the fields are in the same order on each tab, and the field types are the same across the different tabs.
The second option is to open the first tab in Monarch, and export the data as a project export to a new file say, new.xls). Then close Monarch, and start Monarch again but open the second tab of your original Excel file. This time, export the data to new.xls but tick the box to "append data to existing table". Close Monarch, and repeat for the remaining tabs.
The advantage of the first approach is that it's faster to set up, but it requires manual intervention to export the Excel tabs to CSV. The advantage of the second approach is that you can then automate the execution of the Monarch projects, so that next month when you have to do the same thing, it's just a matter of one click.
Hope this helps,
Depending on your ability and what your file looks like, the following VBS code converts Excel files to CSV. It could easily be adjusted for each tab in the file.
WorkingDir = "I:\NewBusiness\Import\AMD"
Extension = ".XLS"
Dim fso, myFolder, fileColl, aFile, FileName, SaveName
Set fso = CreateObject("Scripting.FilesystemObject")
Set myFolder = fso.GetFolder(WorkingDir)
Set fileColl = myFolder.Files
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
For Each aFile In fileColl
ext = Right(aFile.Name,4)
If UCase(ext) = UCase(extension) Then
FileName = Left(aFile,InStrRev(aFile,"."))
Set objWorkbook = objExcel.Workbooks.Open(aFile)
SaveName = FileName & "csv"
objWorkbook.SaveAs SaveName, 23
Set objWorkbook = Nothing
Set objExcel = Nothing
Set fso = Nothing
Set myFolder = Nothing
Set fileColl = Nothing