FlowHeater Demo Video
 
FlowHeater - The Data Import / Export Specialist
 
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

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

Acessing Read and Write Excel adapter data by VBA script 8 years 1 month 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(), _
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 8 years 1 month 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?

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

other Languages

en
GB | US

de
DE | AT | CH

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

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

Follow us on

twitter  facebook

YouTube

 de en