Highlighted
BeckyG
Visitor.
97 views

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

Jump to solution

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!

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

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

Jump to 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.

 

2 Replies
Micro Focus Expert
Micro Focus Expert

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

Jump to 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
Visitor.

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

Jump to solution

Thank you!  I got it to work!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.