Highlighted
Absent Member.
Absent Member.
3442 views

Excel Interop

set ExcelApp to new Application()

set ExcelWorkBook to ExcelApp::Workbooks::Add(1)

set ExcelWorkSheet to ExcelWorkBook::Sheets[1] as type Worksheet

set ExcelWorkSheet::Cells[1,1] to "test"

invoke ExcelWorkBook::SaveAs("C:\temp\test1.xlsx")

invoke ExcelWorkBook::Close()

invoke ExcelApp::Quit()

 

The above code works in C#, not sure what is going on with Visual Cobol.  I get

0 Likes
7 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Can you show me how the data items are defined as well?

Thanks

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

It might be useful to see the equivalent C# as well.  One thing that occurs to me is that subscripting using  square brackets is zero-based in COBOL, just as in C#.  Is it possible that you meant to reference Sheets[0] and Cells[0, 0] ?

Robert.

0 Likes
Highlighted
Absent Member.
Absent Member.

      01 ExcelApp type Application.

      01 ExcelWorkBook type Workbook.

      01 ExcelWorkSheet type Worksheet.

Sorry, it would have made sense to add them.  Thanks

0 Likes
Highlighted
Absent Member.
Absent Member.

           Application ExcelApp = new Application();

           Workbook ExcelWorkBook = ExcelApp.Workbooks.Add(1);

           Worksheet ExcelWorkSheet = (Worksheet)ExcelWorkBook.Sheets[1];

           ExcelWorkSheet.Cells[1, 1] = "test";

           ExcelWorkBook.SaveAs(@"C:\temp\test1.xlsx");

           ExcelWorkBook.Close();

           ExcelApp.Quit();

Here is the C# code.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

We were able to reproduce the problem and a fix has been created.

The problem is occurring because the SaveAs method uses optional parameters, as do a number of these Interop methods and if not actually supplied to the method the compiler was generating a null parameter instead of the expected value of System.Reflection.Missing::Value.

Please create a support incident and place my name in the description and I will ensure that it gets registered against the rpi so that I can get a fix to you when available.

In the meantime you can workaround the issue by specifying the parameters as follows, although you might have to do this for other Excel Interop methods as well.

invoke ExcelWorkBook::SaveAs("\temp\test1.xlsx"
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type XlSaveAsAccessMode::xlNoChange
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       type System.Reflection.Missing::Value
                                       )

Thanks

0 Likes
Highlighted
Absent Member.
Absent Member.

I copied and pasted your code and I got the same message.  I then changed the filename parameter to be an absolute path and it worked.  Apparently relative paths don't work with the saveas method.  

Just incase somebody else was interested here is the code to get the drive letter.

          set myString to type Environment::CurrentDirectory()

          set myString to type Path::GetPathRoot(myString)

          set myString to myString & "temp\test1.xlsx"

I will create a support incident for this asap.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

I also had a problem with relative path, but I actually found that when I did pass a relative path to SaveAs it was using the setting of the USERPROFILE environment variable as the path base.  I didn't investigate further and don't know why this is the case...

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.