Dynamic data import / export


simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

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

Acessing Read and Write Excel adapter data by VBA script 6 years 1 month ago #2060

  • Francis
  • Francis's Avatar
  • Offline
  • New Member
  • 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(), _

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

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 !
The administrator has disabled public write access.

Re:Acessing Read and Write Excel adapter data by VBA script 6 years 1 month ago #2063

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.076 seconds