Resolved Help with VBA Excel

[carlos.valentini]

Good morning to all of the forum.
I need a little help.
I have the following situation:
- An Excel spreadsheet, in which I should use the progress command, search for a certain string for example "& 1" and replace with another one.
How do I do that? Which VBA command can I use?
Does anyone have an example?

 

[Rob Fitzpatrick]

A simple way to start with VBA is to manually do what you are trying to accomplish in Excel, while running the Macro Recorder. Then examine the resulting VBA code and edit it to suit your needs. It may not be the best code but it will get you started. It's helpful for learning new methods and properties.

 

[Osborne]

Rob is correct, and the Macro Recorder gives you something like this:

Cells.Replace What:="& 1", Replacement:="& 2", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

So a simple Progress example to open a workbook and replace a string on the first sheet is something like this:

DEFINE VARIABLE chExcelApp AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.

CREATE "Excel.Application" chExcelApp.
chWorkbook = chExcelApp:Workbooks:Open("C:\Temp\Excel.xlsx").
chWorksheet = chWorkBook:Worksheets(1).
chWorksheet:Cells:Replace("& 1","& 2").
chExcelApp:Visible = YES.

RELEASE OBJECT chExcelApp.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chWorksheet.

 

[carlos.valentini]

Thank you very much.

 

[Marco Aurelio Soares]

Rob is correct, and the Macro Recorder gives you something like this:

Cells.Replace What:="& 1", Replacement:="& 2", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

So a simple Progress example to open a workbook and replace a string on the first sheet is something like this:

DEFINE VARIABLE chExcelApp AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.

CREATE "Excel.Application" chExcelApp.
chWorkbook = chExcelApp:Workbooks:Open("C:\Temp\Excel.xlsx").
chWorksheet = chWorkBook:Worksheets(1).
chWorksheet:Cells:Replace("& 1","& 2").
chExcelApp:Visible = YES.

RELEASE OBJECT chExcelApp.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chWorksheet.

Hello Osborn,
with this code every time the Excel file open in ReadOnly mode. Another issus is that even no replacement was done, the excel will ask to save. Do you know how to solve?

 

[Rob Fitzpatrick]

It can also be helpful to use the Progress COM Object viewer (%DLC%\bin\proobjvw.exe) and open the type library for the COM server you want to use. For some applications (like Word), you would open a .tlb file or .olb file. In the case of Excel, open execel.exe.



You can view the objects that are exposed, and the signatures of their methods, properties, and events from an ABL perspective. It isn't perfect but it is definitely helpful when programming in ABL with COM automation objects.

Here is the full Open() method; note that all parameters except the filename are optional:

 Com-Handle-Var = ] <com-handle>: Open ( 
      Character-Filename,
      <anytype>-UpdateLinks,
      <anytype>-ReadOnly,
      <anytype>-Format,
      <anytype>-Password,
      <anytype>-WriteResPassword,
      <anytype>-IgnoreReadOnlyRecommended,
      <anytype>-Origin,
      <anytype>-Delimiter,
      <anytype>-Editable,
      <anytype>-Notify,
      <anytype>-Converter,
      <anytype>-AddToMru,
      <anytype>-Local,
      <anytype>-CorruptLoad ).

Note that the third parameter is "ReadOnly", so you would want to set that to false. When you want to specify a parameter that has optional parameters before it, add extra commas as needed, e.g.:
chWorkbook = chExcelApp:Workbooks:Open( "C:\Temp\Excel.xlsx", , false ).

Here is the corresponding Microsoft documentation for Open():
https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open

If you want to get rid of the prompt to save changes when you close the application, you could add a call to the Save method of the Workbook object, e.g.:
chWorkbook:Save().