FlowHeater Demo Video
FlowHeater - The Data Import / Export Specialist
Welcome, Guest
Username: Password: Remember me
See also the existing examples of using the Excel Adapter , which may answer your question: CSV export with grouping
  • Page:
  • 1


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

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

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 10 years 4 months ago #2063

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

other Languages


FlowHeater Home

de en


Privacy Statement

Copyright © 2009-2021 by FlowHeater GmbH.
All rights reserved.

Follow us on

twitter  facebook


 de en