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

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

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

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 :)

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

Thank you for a very informative contribution!

Gisle

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

Thank you for a very informative contribution!

Gisle