TOPIC: Acessing Read and Write Excel adapter data by VBA script

Acessing Read and Write Excel adapter data by VBA script 9 years 5 months ago #2060

Francis
Topic Author
Offline
Posts: 13

I got another question about conversions/updates,this time between Excel connection from both side.

I have a read excel adapter that group-by some IDs then return row count of group-IDs each time. This is ok, I use the group-by aggregation with addition of a script that returns simply 1.

But, my problem is, I need to go on write side to search in another Excel a column that refers to the group-IDs if the value of addition is > 1. Once he found it, either add "-IsMasterPiece"or String.Empty to this cell. I tried to declare a workbook excel and put the value at the good place using vba's Find method like this.

Rng = .Find(What:=InValues(1).GetString(), _
After:=.Cells(.Cells.Count),_
MatchCase:=False)

If Not Rng Is Nothing Then
....

This works but each record, I must open/close a workbook and it spawns an infinity of Excel process because its much operation at the same time.

I tried to access calling an ExcelAdapter that reffers to write side. But all property are only string ! Tought objAdap.Fields("ID").Value would work, but even with this, how is it possible to locate on the good record on write without opening/close Excel. Lookup heater is not supported either

Is there someway to make what we wish with an excel, format it as we want ? add row then put value directly in it after ? search row without looping like a lookup ?

Maybe Adapter have some hidden function and subtility I didn't see yet.

Thx again, I'll never say enought !

Please Log in or Create an account to join the conversation.

Re:Acessing Read and Write Excel adapter data by VBA script 9 years 5 months ago #2063

FlowHeater-Team
Offline
Posts: 340

Sorry, there’s no more feature in the Excel Adapter to can do this. Also by now the Lookup Heater doesn’t support the Excel Adapter. We planned this soon in a further version.

But you can modify your script code. You just have to create an Excel Object for the first call/row. Simply store the Excel Object in a variable outside the DoWork Function.

For example

Dim excel as Object
Dim bFirstCall as Boolean = true

Public Function DoWork() As Object

	If bFirstCall = true Then
		
		' Create and open Excel only for the first row
		bFirstCall = false
		
		excel = CreateObject("Excel.Application")
		excel.Workbooks.Open("Path and file name to your Excel Workbook", False, True)

		' more script code

	End If

	' more script code	

End Function

Hope this helps?

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in or Create an account to join the conversation.

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Support Forum

Excel Adapter

Acessing Read and Write Excel adapter data by VBA script

TOPIC: Acessing Read and Write Excel adapter data by VBA script

Acessing Read and Write Excel adapter data by VBA script 9 years 5 months ago #2060

Re:Acessing Read and Write Excel adapter data by VBA script 9 years 5 months ago #2063

other Languages

FlowHeater Home

Follow us on

YouTube