Highlighted
Absent Member.
Absent Member.
1338 views

SaveAs whit atributes excel in cobol native

Jump to solution

Good day, I come to your collaboration and knowledge. How can I give attributes when saving to excel?

 

This is the code, Chris I collaborated with the separation of the text in columns, thanks to the I could generate direct reports to excel

78 xlDelimited value 1.
01 ExcelObject object reference.
01 WorkBooksCollection object reference.
01 WorkBook object reference.
01 CellRange object reference.
01 theRange object reference.
01 ExcelFileFormat pic x(4) value "true".
01 SaveName pic x(256) value spaces.
01 NOMBRE PIC X(15) VALUE "TECNOLOGIA".
01 pausa pic x.
01 GUARDADO OBJECT REFERENCE.

LINKAGE SECTION.
01 AREA-LINK.
03 ARCHIVO-WS PIC X(90).

Procedure Division USING AREA-LINK.
*> Creo a Excel como un objeto y lo llamo
invoke MSExcel "new" returning ExcelObject

*> El objeto excel se hace visible en memoria
invoke ExcelObject "setVisible" using by value 1

*> Obtengo el numero de libros y pongo por def el numero uno
invoke ExcelObject "getWorkBooks"
returning WorkBooksCollection

*> Abro el archivo desde la ruta
invoke WorkbooksCollection "Open" using ARCHIVO-WS
returning Workbook
invoke ExcelObject "getRange"
*> Si cambio el rango desde A1 no me separa la ultima linea
using z"A2:A1000000"
returning CellRange
end-invoke
*> Aca separo por el delimitador el archivo y lo escribo en memo
invoke olesup "setNamedParameters" using by value 4
invoke CellRange "TextToColumns" using
by reference z"Destination"
by value cellrange
by reference z"DataType"
by value xlDelimited
by reference z"Other"
by value oletrue
by reference z"OtherChar"
"|"
* invoke CellRange "finalize" returning CellRange
*AUTOFID DE COLUMNAS
INVOKE ExcelObject "GetColumns" using z"A:IV"
returning theRange
* invoke theRange "select"
* invoke theRange "Copy"
* invoke theRange "PasteSpecial" using
* by value -4163
* by value -4142
invoke theRange "Autofit"
invoke theRange "finalize" returning theRange
invoke ExcelObject "getRange"
using z"A1:A2"
returning CellRange
end-invoke


*> Aca salgo de Excel, si quiero puedo guardarlo automaticamente
*> sin preguntarle al usuario. si comentariza el
*> "invoke ExcelObject "Quit"" Excel termina sin preguntar si
*> desea guardar
 *>Saves the file, but with TXT attributes of not Excel

invoke olesup "setNamedParameters" using by value 3 size 4
invoke WorkBook "SaveAs" using Z"Filename" SaveName
by reference z"XlFileFormat" z"xlWorkbookNormal"
end-invoke

*> Close the WorkBook, discarding the contents
* invoke WorkBook "Close" using by value 0

*> Finalize all objects
invoke theRange "finalize" returning theRange
invoke CellRange "finalize" returning CellRange
invoke WorkBook "finalize" returning WorkBook
invoke WorkBooksCollection "finalize"
returning WorkBooksCollection
end-invoke

*> Exit Excel
* invoke ExcelObject "Quit"
invoke ExcelObject "Finalize" returning ExcelObject
exit program

Aprediz desarrollador en cobol nativo

Tags (3)
0 Likes
2 Solutions

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SaveAs whit atributes excel in cobol native

Jump to solution
When you are using Named parameters with COM you must pass the parameters as a pair where the first one is the name of the parameter and the second is the value.
Try the following:

*> the following is the actual value for WorkbookNormal
78 xlWorkbookNormal value -4143.

invoke olesup "setNamedParameters" using by value 2 size 4
invoke WorkBook "SaveAs"
using Z"Filename"
SaveName
z"FileFormat"
by value xlWorkbookNormal
end-invoke

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SaveAs whit atributes excel in cobol native

Jump to solution
This works for me but the file name must be null terminated.
01 savename pic x(256) value z"c:\temp\myexcel".

This will save the file myexel.xls into the C:\temp folder.
There is nothing in Visual COBOL that would allow you to debug deeper into the Excel COM server as this is a Microsoft class and not a Micro Focus one. The best documentation for the available methods is within the Excel Help itself under programming VBA object model.

Net Express had a utility called Type Library Assistant that would generate the COBOL definitions for the available methods but this is not available in Visual COBOL.

View solution in original post

0 Likes
5 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SaveAs whit atributes excel in cobol native

Jump to solution
Hi, You have posted your question in the Acu extend portion of the forum. This question should be posted in the Net Express / Server Express or Visual COBOL portion of the forum.
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SaveAs whit atributes excel in cobol native

Jump to solution
When you are using Named parameters with COM you must pass the parameters as a pair where the first one is the name of the parameter and the second is the value.
Try the following:

*> the following is the actual value for WorkbookNormal
78 xlWorkbookNormal value -4143.

invoke olesup "setNamedParameters" using by value 2 size 4
invoke WorkBook "SaveAs"
using Z"Filename"
SaveName
z"FileFormat"
by value xlWorkbookNormal
end-invoke

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: SaveAs whit atributes excel in cobol native

Jump to solution

Hello Chris, I tried your solution and it generates the same error that I have been generating, I am misusing the parameters to save in excel format. You know how I can do a graphic debug to see more in depth the treatment of the chains, I would like to be able to understand more because I will have to do many things through

Aprediz desarrollador en cobol nativo

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SaveAs whit atributes excel in cobol native

Jump to solution
This works for me but the file name must be null terminated.
01 savename pic x(256) value z"c:\temp\myexcel".

This will save the file myexel.xls into the C:\temp folder.
There is nothing in Visual COBOL that would allow you to debug deeper into the Excel COM server as this is a Microsoft class and not a Micro Focus one. The best documentation for the available methods is within the Excel Help itself under programming VBA object model.

Net Express had a utility called Type Library Assistant that would generate the COBOL definitions for the available methods but this is not available in Visual COBOL.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: SaveAs whit atributes excel in cobol native

Jump to solution

Hello Chris
Thanks for your answer. The path and filename I am opting for through this instruction, I have forgotten "returning SaveName". I already managed to save Chris thanks to your help, but the file is still created without excel format

invoke olesup "setDispatchType" using by value 0 size 4
invoke ExcelObject "getSaveAsFilename"
using
by reference z"FileFilter"
z"Excel Files (*.xlsx), *.xlsx, All Files (*.*), *.*"
returning SaveName
end-invoke

Change the "78 xlWorkbookNormal value -4143." By "78 XlFileFormat VALUE 51." And in that way manage to save it and open it with excel format, thanks for your very valuable orientation Chris

invoke olesup "setNamedParameters" using by value 2 size 4
invoke WorkBook "SaveAs"
using Z"Filename"
SaveName
z"FileFormat"
by value XlFileFormat
end-invoke

Aprediz desarrollador en cobol nativo

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.