Highlighted
Absent Member.
Absent Member.
11765 views

[archive] Cell manipulations in Excel

[Migrated content. Thread originally posted on 07 July 2009]

From my ever hard working buddy Claudio. A lot of examples on how to manipulate cells in Excel.

       IDENTIFICATION               DIVISION.
       PROGRAM-ID.                  ExcelSimple.
      * Compile with one of the following commandlines,
      * accordingly with your Office version
      *
      * ccbl32 -ga -si 2003 -sx 2000 ExcelSimple.cbl
      * ccbl32 -ga -sx 2003 -si 2000 ExcelSimple.cbl
      *
      *           
       ENVIRONMENT DIVISION.
       CONFIGURATION                SECTION.
       SPECIAL-NAMES.
      *    COPY    "excel.def".                                          | 2003
      *    COPY    "excel-2000.def".                                     | 2000
           decimal-point is comma.
       DATA        DIVISION.
       WORKING-STORAGE              SECTION.
       77  olExcel                  HANDLE OF APPLICATION.
       77  olWrkBk                  HANDLE OF WORKBOOK.
       77  olWrkSh                  HANDLE OF WORKSHEET.
       77  olWrkShNEW               HANDLE OF WORKSHEET.   
       77  oRange                   HANDLE OF RANGE.     
       77  oFont                    HANDLE OF @Font. 
       77  oInterior                HANDLE OF Interior.
       77  MY-STRING                PIC X(80).
       77  ws-count                 PIC 9(5).
       77  H-VARIANT                USAGE HANDLE.
       77  RESULT-CODE              PIC S9.
       77  ws-date                  PIC X(10).
       77  WS-SEPARATOR             PIC X.

       PROCEDURE DIVISION.
       Main.
           CREATE  Application      OF Excel
                   HANDLE           IN olExcel.
      *This is for training purposes only. If you don't want to see
      *Excel, remove this line.
           MODIFY  olExcel          @Visible = 1.
           MODIFY  olExcel          Workbooks::Add()
                   GIVING           olWrkBk.
           INQUIRE olWrkBk          Worksheets::Item(1) IN
                   olWrkSh.
      *Set the value of a cell.
           MODIFY  olWrkSh          Range("A1")::Value = "Last Name".
      *Get the value of a cell.
           INQUIRE olWrkSh          Range("A1")::Value IN MY-STRING.
      *Force a recalculation of a sheet
      *    MODIFY  olWrkSh          Range("A1")::Calculate().

      * INIZIO ***********************************************************
      * FORMATTAZIONE CELLE   
      *
           MODIFY  olWrkSh      Range("C4")::Value = "A".
           MODIFY  olWrkSh      Range("D4")::Value = "B".
           MODIFY  olWrkSh      Range("C5")::Value = "Dream Theater".
           MODIFY  olWrkSh      Range("C6")::Value = "Blind Guardian".
           MODIFY  olWrkSh      Range("D5")::Value = "Heaven & Hell".
           MODIFY  olWrkSh      Range("D6")::Value = "Queensryche".

      *     Columns("C:D").Select
      *     Columns("C:D").EntireColumn.AutoFit
           MODIFY  olWrkSh      Range("C:D")::Columns()::Select().
           MODIFY  olWrkSh      Range("C:D")::EntireColumn()::AutoFit().
      *     Range("C5").Select
      *     Selection.Font.Bold = True
      *    MODIFY  olWrkSh      Range("C5")::Select()                             | this causes MAV
      *            GIVING       oRange.                                           | this causes MAV
      *    MODIFY  oRange       @Font::Bold = 1.                                  | this causes MAV

           INQUIRE olWrkSh      Range("C5")::Font
                   IN oFont.
           MODIFY  oFont        Bold = 1.   

      * alternative method for Bold     
           MODIFY  olWrkSh      Range("A1:B1")::Font()::Bold(1)

      *     Range("D5").Select
      *     Selection.Font.Italic = True
           INQUIRE olWrkSh      Range("D5")::Font
                   IN oFont.
           MODIFY  oFont        Italic = 1. 

      *     Range("D6").Select
      *     With Selection.Font
      *             .Color = -16776961
      *             .TintAndShade = 0
      *     End With   
           INQUIRE olWrkSh      Range("D6")::Font
                   IN oFont.
           MODIFY  oFont        Color = -16776961.   

      *     Range("C6").Select
      *     With Selection.Interior
      *             .Pattern = xlSolid
      *             .PatternColorIndex = xlAutomatic
      *             .Color = 65535
      *             .TintAndShade = 0
      *             .PatternTintAndShade = 0
      *         End With   
           INQUIRE olWrkSh      Range("C6")::Interior
                   IN           oInterior.       
           MODIFY  oInterior    Pattern = xlSolid
                                PatternColorIndex = xlAutomatic
                                Color = 65535.

      *     Columns("C:D").Select
      *     With Selection
      *             .HorizontalAlignment = xlCenter
      *             .VerticalAlignment = xlCenter
      *             .WrapText = False
      *             .Orientation = 0
      *             .AddIndent = False
      *             .IndentLevel = 0
      *             .ShrinkToFit = False
      *             .ReadingOrder = xlContext
      *             .MergeCells = False
      *     End With   
           MODIFY olWrkSh       
                       Range("C4:D6")::HorizontalAlignment = xlCenter
           MODIFY olWrkSh       
                       Range("C4:D6")::VerticalAlignment = xlCenter.
      *     With Selection.Borders(xlEdgeBottom)
      *         .LineStyle = xlContinuous
      *         .Weight = xlThin
      *         .ColorIndex = xlAutomatic
      *     End With
           MODIFY  olWrkSh      Range("C5:D6")::BorderAround(
                                   BY NAME LineStyle = xlContinuous
                                   BY NAME Weight = xlThin
                                   BY NAME ColorIndex = xlAutomatic
                                   )             

      * FINE *************************************************************



      * FORMATO NUMERO **************************************************
           MODIFY  olWrkSh      Range("A5")::Value = 12345,99.
           MODIFY  olWrkSh      Range("A5")::NumberFormat = "0,00".       

      *     MODIFY  olWrkSh      Range("A17")::NumberFormat = "General"
      *     MODIFY  olWrkSh      Rows(1)::NumberFormat = "hh:mm:ss"
      *     MODIFY  olWrkSh      Columns("C")::NumberFormat =
      *                          "$#,##0.00_);[Red]($#,##0.00)"


      * FORMATO DATA ****************************************************
      * set the value of a date into a cell
      *    objExcel.Cells(1,1).NumberFormat = "mm.dd.yyyy"
      *
      *    01 is the day, 02 is the month
      *
           MOVE "01/02/2003" TO ws-date.
           MODIFY olWrkSh Range("A2")::Value = ws-date.
           INQUIRE olWrkSh Range("A2")::NumberFormat IN ws-date.
           IF ws-date(1:2) = "gg"
              MOVE "01/02/2003" TO ws-date
           ELSE
              MOVE "02/01/2003" TO ws-date
           END-IF
           MODIFY olWrkSh Range("A2")::Value = ws-date.
           MODIFY olWrkSh Range("A2")::NumberFormat = "gg/MM/aaaa".

      * LARGHEZZA COLONNA ***********************************************
      * [URL]http://msdn.microsoft.com/en-us/library/aa214203(office.11).aspx[/URL]

           MODIFY olWrkSh        Range("F1")::ColumnWidth = 4
           MODIFY olWrkSh        Range("F1")::RowHeight = 30

      *   
      * END *************************************************************                   
      *   
      * INIZIO ***********************************************************
      * Aggiungi un foglio a destra
           MODIFY  olWrkBk          Worksheets::Add()
                   GIVING olWrkShNEW.

           INQUIRE olWrkBk          Worksheets::Count IN ws-count
           INQUIRE olWrkBk          Worksheets::Item(ws-count) IN
                   olWrkSh.
           MODIFY  olWrkShNEW       @Move(BY NAME After olWrkSh)           

      * FINE *************************************************************                   
      *
      * INIZIO ***********************************************************
      * Copia un foglio
      *
      *    Sheets("Sheet1").Select
      *    Sheets("Sheet1").Copy After:=Sheets(3)
           INQUIRE olWrkBk          Worksheets::Item(1) IN
                   olWrkSh.

           MODIFY  olWrkSh          @Copy(BY NAME After olWrkSh)

      * FINE *************************************************************                   
      *

      * INIZIO ***********************************************************
      * INQUIRE DEL SEGNO DI SEPARAZIONE DECIMALE
      *
      *    INQUIRE olWrkBk @Application::DecimalSeparator                | 2003
      *                     IN WS-SEPARATOR                              | 2003
      * FINE *************************************************************                   
      *

           DESTROY oFont.
           DESTROY olWrkSh.
           DESTROY olWrkShNEW.   

      *Enforce a close without save, or Excel will prompt you.
           MODIFY  olWrkBk          @Close(BY NAME SaveChanges 0).
           DESTROY olWrkBk.
           MODIFY  olExcel          Quit().
      *This next line is very important, otherwise Excel will stick in
      *memory.

           DESTROY olExcel.
           GOBACK.

0 Likes
22 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

Hi gforseth,

Could you please provide an example for "AUTOFILL" function?

I tried this way:


           MOVE "=G1*H1" TO FORMULA-VALUE.
           MODIFY EX-WORKSHEET @Range("I1")::Formula = FORMULA-VALUE.
           MODIFY EX-WORKSHEET @Range("I1")::Select().
080309  INQUIRE EX-WORKSHEET @Range("I2:I5") IN EX-RANGE-1.
080309  MODIFY
080309  EX-WORKSHEET @Range("I1")::AutoFill(EX-RANGE-1,xlFillDefault).
072309  DESTROY EX-RANGE-1.


But it pops up "AutoFill method of Range class failed".

Thanks in advance.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

According to AutoFill documentation, destination (e.g. EX-RANGE-1) must contain source range (e.g. cell I1 must be included), perhaps that is the reason. I am not familiar with the AutoFill method, so I cannot tell other than is listed in the documentation.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

According to AutoFill documentation, destination (e.g. EX-RANGE-1) must contain source range (e.g. cell I1 must be included), perhaps that is the reason. I am not familiar with the AutoFill method, so I cannot tell other than is listed in the documentation.



080309     MODIFY EX-WORKSHEET @Range("I2")::Formula = FORMULA-VALUE.
080309     MODIFY EX-WORKSHEET @Range("I2")::Select().
080309     INQUIRE EX-WORKSHEET @Range("I2:I60772") IN EX-RANGE.
080309     MODIFY
080309     EX-WORKSHEET @Range("I2")::AutoFill(EX-RANGE,xlFillDefault).
080309     DESTROY EX-RANGE.


Mr. gforseth, you exactly got the point. When I included cell I1, it immediately works.

Thanks a lot.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

Just didn't see this simple but useful function in the above sample code, so append it here:


080309     INQUIRE EX-WORKSHEET @Range("A65536")::End(xlUp)::Row
080309     IN LastRowColA.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

Hello,

i have the problem to set the NumberFormat to "General".

In the example
MODIFY olWrkSh Range("A17")::NumberFormat = "General"
is used, but this dosn't work.

I tested "Standard" which works in Delphi... but still no change.

Can someone help?

David
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

Interesting...

Define "this dosn't work".

What version of Excel are you using?
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

Excel 2003 (11.8169.8172) SP3
US-Version with German Language Pack

Dosn't work = the old format was ('00000') and not ('General')
and when i set ('General') no error nothing occurs and ('00000') is still the format in the cell.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

I am relatively new at this so bear with me.
I run Excel, recording a macro, manually do whatever I wish to the worksheet, study the generated macro, and convert that to 'Inquire' and 'Modify' statements in for use in a 'Create'(d) application of Excel. Normally this is reasonably straightforward, and by reference to excel.def it is not normally too difficult to establish the various handles required before 'Modify'. For example macro generated code
Columns("C:C").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0

Translates quite successfully to

inquire hExcelWks Range("C:C") hRange
inquire hRange @Interior hInterior
modify hInterior @Pattern = (xlSolid)
@PatternColorIndex = (xlAutomatic)
@ThemeColor = (xlThemeColorAccent5)
@TintAndShade = (0.799981688894314)
@PatternTintAndShade = (0)

Everything works perfectly as the relationship between Range and Interior is such that I first need to obtain the handle of Range, then, using that, establish the handle of Interior before doing the modify. The above is merely an example, I have never had any problem establishing the various handles needed before the Modify except for the following, where, no matter how many times I refer to excel.def, I simply cannot establish the relationships and handles required.
Here is the Macro generated code, can anyone advise me how to arrive at the correct handle to apply the Modify statements for Selection.ShapeRange.Fill. and Selection.ShapeRange.Line.? (Dont bother with the .Addcomment and .Comment, those are no problem, I merely left those in so the full Macro generated code could be seen)

Range("D10").Select
Range("D10").AddComment
Range("D10").Comment.Visible = False
Range("D10").Comment.Text Text:="Peter Jennet:" & Chr(10) & ""
Range("D10").Comment.Text Text:="" & Chr(10) & ""
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 225)
Selection.ShapeRange.Fill.UserPicture _
"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Water lilies.jpg"
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

Sorry just to clarify, I dont think I will have a problem with .Line or .Fill, their relationship to ShapeRange looks pretty obvious, it is the ShapeRange itself that I can't seem to relate to anything.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

i miss something in your example like this:

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 610.5, 18#, 102#, 141#). _
Select

and then your part:

Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 225)
Selection.ShapeRange.Fill.UserPicture _
"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Water lilies.jpg"

You create your Shape, select it and then modify it.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Cell manipulations in Excel

I was working purely from the macro generated by 'developer/record macro' which does not list the code you mention, but thank you very much, what you say makes a lot of sense, and I now have a few ideas on how to convert that to the Inquire/Modify statements I need.
I will play around a bit more tomorrow, but I think you have put me on the right path so thanks again
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.