Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
SteveEly Absent Member.
Absent Member.
2121 views

Problem with Excel OLE

Jump to solution

In the archived forums there is a great example of controlling excel through ole automation.

I have used the sample program from time to time with out a problem, however I am trying to duplicate a VBA macro that formats a header row nicely. I have most of it working except "AutoFit" and "FreezePanes". AutoFit is more important to me than FreezePanes.

Here is the VB code:

 Rows("1:1").Select
 With Selection.Interior
         .ColorIndex = 37
         .Pattern = xlSolid
 End With
 Selection.Font.Bold = True
 Selection.AutoFilter
 Rows("2:2").Select
 ActiveWindow.FreezePanes = True
 Cells.Select
 Cells.EntireColumn.AutoFit
 Range("A1").Select

Here is the reevant parts of the COBOL code:

INQUIRE EX-WORKBOOK @Worksheets::Item(1) IN EX-WORKSHEET.

INQUIRE EX-WORKSHEET @Range("A3:O3") IN EX-RANGE.
MODIFY EX-RANGE @Font::Bold = 1.
MODIFY EX-RANGE @Interior::ColorIndex = 37.
MODIFY EX-RANGE @Interior::Pattern = @xlSolid.
MODIFY EX-RANGE @AutoFilter = 1.
* MODIFY EX-RANGE @AutoFit.
* MODIFY EX-WORKSHEET @FreezePanes = 1.

DESTROY EX-RANGE.

 It works with AutoFit and FreezePanes remarked out.

Any ideas?

Tags (1)
0 Likes
1 Solution

Accepted Solutions
neidingd Honored Contributor.
Honored Contributor.

RE: Problem with Excel OLE

Jump to solution

Example where we use AutoFit:

          inquire xls-sheet  @CELLS xls-range.

          modify  xls-range  @SELECT().

          modify  xls-range  @COLUMNS::@AUTOFIT().

u have to define what you will AutoFit...

---

Example for FreezePanes:

          inquire xls-sheet  @CELLS::@ITEM(2, "A") xls-range.      

          modify  xls-range @SELECT().

          modify  xls-app   @ActiveWindow::@FreezePanes = 1.

The Freeze must be defined for the Active Window...

View solution in original post

0 Likes
2 Replies
neidingd Honored Contributor.
Honored Contributor.

RE: Problem with Excel OLE

Jump to solution

Example where we use AutoFit:

          inquire xls-sheet  @CELLS xls-range.

          modify  xls-range  @SELECT().

          modify  xls-range  @COLUMNS::@AUTOFIT().

u have to define what you will AutoFit...

---

Example for FreezePanes:

          inquire xls-sheet  @CELLS::@ITEM(2, "A") xls-range.      

          modify  xls-range @SELECT().

          modify  xls-app   @ActiveWindow::@FreezePanes = 1.

The Freeze must be defined for the Active Window...

View solution in original post

0 Likes
SteveEly Absent Member.
Absent Member.

RE: Problem with Excel OLE

Jump to solution

Thanks, that works like a charm!

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.