2 Replies Latest reply: May 15, 2014 10:12 AM by elginreigner _ RSS

    Import excel with multiple tabs into Monarch

    smisra _

      I am new to Monarch and am currently using Monarch pro V11. Is it possible to import an MS-Excel file with multiple tabs into Monarch. Is there a way to do it.

      Please help.

        • Import excel with multiple tabs into Monarch
          Olly Bond

          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,

           

          Olly

            • Import excel with multiple tabs into Monarch
              elginreigner _

              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

              Dim objExcel,objWorkbook

               

              Set fso = CreateObject("Scripting.FilesystemObject")

              Set myFolder = fso.GetFolder(WorkingDir)

              Set fileColl = myFolder.Files

               

              Set objExcel = CreateObject("Excel.Application")

               

              objExcel.Visible = False

              objExcel.DisplayAlerts= False

               

              For Each aFile In fileColl

                   ext = Right(aFile.Name,4)

                   If UCase(ext) = UCase(extension) Then

                        'open excel

                        FileName = Left(aFile,InStrRev(aFile,"."))

                        Set objWorkbook = objExcel.Workbooks.Open(aFile)

                        SaveName = FileName & "csv"

                        objWorkbook.SaveAs SaveName, 23

                        objWorkbook.Close

                   End If     

              Next

               

              Set objWorkbook = Nothing

              Set objExcel = Nothing

              Set fso = Nothing

              Set myFolder = Nothing

              Set fileColl = Nothing

              /CODE