OLE Excel data handling from VB

[Faiz]

Dear Experts,

We have developed a scientific application using VB6. The output,
which contains several columns having numeric data delimited by <tab>
char, is displayed in a multiline text box control. We wish to copy
the data of a certain column (like, Block selected mode in TextPad)
only. The text box control does not allow this feature at all.

We decided to use Excel as an OLE container(embedded interface of
Excel) to display output in columns. I am using the following code to
populate the Excel.

OLE1.CreateEmbed "", "Excel.Sheet.8"

Set objExcel = OLE1.object.Parent 'Now you can use the type lib
Set objWB = OLE1.object 'will return a workbook object
Set objSheet = objWB.ActiveSheet 'will return a worksheet object

'objSheet.Cells(1, 1) = "Data 1"

Can anybody please tell the following things:

1). A more better way to send data to Excel from VB in a junk or
atleast for the whole row instead of cell by cell.

2). How to allow users to edit the data in the cells without opening
the separate instance of the Excel interface(perhaps called in-line
editing).

3). Allow user to select any row/col to perform cut, copy or paste
operation.

Any help in this regard will be highly appreciated.

Regards,

Faiz

 

[Jon Peltier]

Faiz -

I'm only slowly coming up to speed in VB, but isn't there a FlexGrid
control that would do the same thing as an embedded Excel object?
Without knowing any better, I'd assume that would be easier to use.

- Jon

 

[Tom Ogilvy]

assume your data is in a single column - two dimenional array

dim myarray(1 to numrows, 1 to 1)


so for example

if myarray is 1 x 10, 1 x 1

Cells(1,1).Resize(10,1).Value = myarray

Assuming your enter in each cell is something like

Data1 tab data2 tab data2 tab

you can go into excel and do Data=>Text to column, select delimited, set Tab
as the delimiter and your data will be parsed into separate columns.

If you turn the macro recorder on while you do this, you will get the code
to run the command (the interactive dialog will not be displayed when run in
code).

Regards,
Tom Ogilvy

 

[jaf]

Hi Faiz,
Can't say I tried this, but if your multi-line textbox contains linefeeds or
crlf then.

clipboard.copy
objSheet.ActiveSheet.Paste Destination:=objWB.Worksheets(objSheet).Cells(1,
1)

Should paste into x rows as does Excel's native copy will paste a multi-cell
range or multipul lines into seperate rows.

If you want to fill a row instead of a column use...

objSheet.Cells(1, 1).PasteSpecial xlPasteAll, xlPasteSpecialOperationNone,
True, True