Highlighted
Absent Member.
Absent Member.
533 views

[archive] Excel merge

[Migrated content. Thread originally posted on 02 February 2006]

Hi all,

does someone know, if it's possible using ActiveX to use a function like the mail merge function of Word, to use in Excel?
So I have a Excel document, and in that document I want to merge data of, for example, a csv-file into this Excel document, and each column in this csv-file has to be inserted in a different cell within that document.

Thanks all.
0 Likes
3 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel merge

Laura,

If all you are wanting to do is view the comma delimitted file in Excel where each field appears in a column, you can just open that file in Excel. Something like this:

MODIFY EXCEL-APP
@WORKBOOKS::OPENTEXT(
BY NAME Filename "filename",
BY NAME Origin xlWindows,
BY NAME StartRow 1,
BY NAME DataType xlDelimited,
BY NAME Tab 1,
BY NAME TextQualifier -4142).

Hope this helps.

Rob
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel merge

i had problems with opentext (to define what is numeric and what is text) and solved the problem with QueryTables.


working-storage....

       01  excel-objects.
           03  xls-app       handle of application of excel.
           03  xls-book      handle of workbook    of excel.
           03  xls-sheets    handle of worksheets  of excel.
           03  xls-sheet     handle of worksheet   of excel.
           03  xls-range     handle of range       of excel.
           03  xls-query     handle of querytable  of excel.

       01  myArray.
           03  filler        occurs 4.
               05  myItems   usage handle of variant.


open the file...

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

           modify  xls-sheet QueryTables::Add(
                      by name Connection "TEXT;C:\muffrohr\seq-plz.txt"
                      by name Destination xls-range
                                             )
                   giving xls-query.


define if it is text or numeric... ( 1 = numeric (default); 2 = text)

           call "C$SETVARIANT" using 1, myItems(1).
           call "C$SETVARIANT" using 2, myItems(2).
           call "C$SETVARIANT" using 2, myItems(3).
           call "C$SETVARIANT" using 1, myItems(4).


put it in excel...

           modify xls-query @Name("SEQ-PLZ"),
                            @FieldNames(1),
                            @RowNumbers(0),
                            @FillAdjacentFormulas(0),
                            @PreserveFormatting(1),
                            @RefreshOnFileOpen(0),
                            @RefreshStyle(xlInsertDeleteCells),
                            @SavePassword(0),
                            @SaveData(1),
                            @AdjustColumnWidth(0),
                            @RefreshPeriod(0),
                            @TextFilePromptOnRefresh(0),
                            @TextFilePlatform(850),
                            @TextFileStartRow(1),
                            @TextFileParseType(xlDelimited),
                            @TextFileTextQualifier(xlTextQualifierNone),
                            @TextFileConsecutiveDelimiter(0),
                            @TextFileTabDelimiter(0),
                            @TextFileSemicolonDelimiter(1),
                            @TextFileCommaDelimiter(0),
                            @TextFileSpaceDelimiter(0),
                            @TextFileColumnDataTypes(myArray),
                            @TextFileTrailingMinusNumbers(1),
                            @Refresh(by name BackgroundQuery 0).

           modify  xls-query @Delete().
           destroy xls-query.


we use this to import data in "MS-DOS (PC-8)" Format...

hope this helps 🙂
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel merge

Thank you for a very informative contribution!

Gisle
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.