Dynamic data import / export

datenbank

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.
  • Page:
  • 1

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

Acessing Read and Write Excel adapter data by VBA script 6 years 4 months ago #2060

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

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

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

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 248
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?

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.
  • Page:
  • 1
Moderators: FlowHeater-Team
Time to create page: 0.081 seconds