Populating multiple excel cells from a Query

[aGrimm]

Using .vbs is there a way to populate multiple excel cells at one time using results from a query?

ExcelSheet.Cells(row, col).Value = cellValue

That is what I'm using now but is there anyway to pass in an array of data and have it put in a range of columns and rows? 

Populating each cell one by one still takes quite a while.  If it could grab large portions of data and populate multiple cells at once it would speed up the process greatly.

That is essentially what I want it to do.

Thanks in advance.

[Twigeater]

Hello!

Yes there is a way. Not verry obvious and in the past a little bit cumbersome but today with DIAdem 11 quite simple.

The trick is to use the clipboard and convert the values to text. Lets start with a simple example

Option Explicit

Dim oExcel
Dim oWorkbook
Dim oSheet

Set oExcel = CreateObject("Excel.Application")

oExcel.Visible        = true
oExcel.ScreenUpdating = false

Set oWorkbook = oExcel.Workbooks.Add
Set oSheet = oWorkbook.Sheets.Add

TextToClipboard("A"   & vbTAB & "B" & vbCRLF  & _
                "1.1" & vbTAB & "2.1"  & vbCRLF & _
                "1.2" & vbTAB & "2.2"  & vbCRLF & _
                "1.3" & vbTAB & "2.3"  & vbCRLF )

Call oSheet.Paste()

oExcel.ScreenUpdating = true

Now we can add a DIAdem function to copy 4 complete channels realy quick:

Option Explicit

Dim oExcel
Dim oWorkbook
Dim oSheet

Set oExcel = CreateObject("Excel.Application")

oExcel.Visible        = true
oExcel.ScreenUpdating = false

Set oWorkbook = oExcel.Workbooks.Add
Set oSheet = oWorkbook.Sheets.Add

Call ChnClpCopy("1-4")

Call oSheet.Paste()

oExcel.ScreenUpdating = true

Depending on the country settings you will run into trouble with the decimal separator. If Excel doesn't recognice the values as numbers you have to get the text from the clipborad replace the separator with the right one and paste it back to the clipboard.

If you want to fill a range in Excel you should select it first and paste than. See here for details

Matthias

[aGrimm]

Hmm I think this is close to what I need but not quite there yet.  All of my values are in multiple channel groups with multiple channels.  I'd like to grab for example the value of the third channel for each channel group and put all of those values in excel at once.

[aGrimm]

or take the values and put them into an array and then print that array in a range of cells.  This is more of what I want to accomplish. 

[Twigeater]

Absolut right. If you cannot define a channel desciptor for ChnClpCopy to get the values you need or for all other cases you have to build a string in the right syntax and paste it at once in Excel. This will significant faster than value by value.

Matthias

[aGrimm]

Can you give me an example of how I would go about doing that or where to look at how?

Sorry I am very new to DIAdem.

[aGrimm]

So I changed my query so now I have 6 values in a channel. 

I tried the ChnClpCopy but it's not working on the right group.  The help menu says to use something like this, Call ChnClpCopy("'Group1/Channel1'-'Group1/Channel4'") but that's not working either.

Is there a way to select which group and which channel inside that group gets copied? 

Once again sorry for all of the questions.

[Twigeater]

Hello!

Yes, many questions

Let's start with the ChnClpCopy parameter. This string is verry flexible. You can define channel number, channel by name with or without group, a range of channels, etc. Assuming you have at least on channel 'MyChannel' in 'MyGroup' and this channel has the channel number 1 you can copy its data with this call.

ChnClpCopy("1")
ChnClpCopy("'MyChannel'")
ChnClpCopy("'MyGroup/MyChannel'")

Every one delviers the same result. You can choose which you like. Try this first one by one to see how it works.

Matthias

[aGrimm]

Awesome that worked perfectly.

Thank you for all of your help.