Excel - Adding another csv file as a new worksheet in an already open or existing workbook

[BeckyG]

I already found the example here on how to open a csv file into a new excel workbook. I did all the formatting I needed and now I need to add(import) another csv file into the same excel workbook, but on a different (new) sheet. I can't figure out how to do it.

Here is the code I found here for the first worksheet. It works.

set ExcelObject to new type Microsoft.Office.Interop.Excel.Application
set ExcelObject::Visible to true
set WorkBooksCollection to ExcelObject::Workbooks

*> Open File
set Workbook to WorkBooksCollection::Open(
WS-CSVFILE,
type Type::Missing,
type Type::Missing,
2,
type Type::Missing,
type Type::Missing,
type Type::Missing,
type Type::Missing,
",",
type Type::Missing,
type Type::Missing,
type Type::Missing,
type Type::Missing,
type Type::Missing,
type Type::Missing)
set Worksheets to Workbook::Worksheets
set Worksheet to Worksheets[ws-wkst-name] as type _Worksheet.

*> (did all my formatting here)

*> save
invoke WorkSheet::SaveAs(WS-XLSFILE, type XlFileFormat::xlExcel7 , type Type::Missing, type Type::Missing, type Type::Missing
type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)

Thank You!

[Chris Glazier · Accepted solution]

I got this to work by opening up a second workbook and then using the Worksheet::Copy method to copy the sheet from the second workbook into the first one.

I found this documented here

      $set ilusing(Microsoft.Office.Interop.Excel)
       program-id. Program1 as "excelworksheets.Program1".
       data division.
       working-storage section.
       01 ExcelObject type Application.
       01 WorkBooksCollection type Workbooks.
       01 Workbook type Workbook.
       01 Workbook2 type Workbook.
       01 Worksheet type Worksheet.
       01 Worksheet2   type Worksheet.
       procedure division.

               set ExcelObject to new type Microsoft.Office.Interop.Excel.Application
               set ExcelObject::Visible to true
               set WorkBooksCollection to ExcelObject::Workbooks

               *> Open File
               set Workbook to WorkBooksCollection::Open(z"D:\tests\excelworksheets\CSVFILE1.csv", type Type::Missing,
                 type Type::Missing, 2, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,
                 ",", type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,
                 type Type::Missing)
               
               set Worksheet to Workbook::Worksheets["CSVFILE1"] as type Microsoft.Office.Interop.Excel.Worksheet
               *> (did all my formatting here)
               *> Open File for 2nd workbook
               set Workbook2 to WorkBooksCollection::Open(z"D:\tests\excelworksheets\CSVFILE2.csv", type Type::Missing,
                 type Type::Missing, 2, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,
                 ",", type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,
                 type Type::Missing)

               set Worksheet2 to Workbook2::Worksheets["CSVFILE2"] as type Microsoft.Office.Interop.Excel.Worksheet
      *        *> (did all my formatting here)
               *> Copy worksheet CSVFILE2 from workbook CSVFILE2 after worksheet CSVFILE1 in workbook CSVFILE1
               invoke Worksheet2::Copy(type Type::Missing, param After = Worksheet)
               *> Save as a new Exzcel file containing both sheets
               invoke WorkSheet::SaveAs("D:\tests\excelworksheets\Newfile.xls", type XlFileFormat::xlExcel7, type Type::Missing, type Type::Missing, type Type::Missing
                   type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)

               invoke Workbook2::Close
               invoke Workbook::Close
               invoke WorkBooksCollection::Close
               invoke ExcelObject::Quit
               goback.
           
       end program Program1.

[BeckyG]

Thank you!  I got it to work!