data pushed from application into closed excel does not trigger macros

[LeanneBlakemore1972]

Hi. I have an external application that pushes data into a designated excel without first opening that excel (it must be closed or the data does not push over). The excel has a small macro in it that recognizes when data has been moved into a certain cell and then moves it into the location in a new sheet that I have designated. This works if I manually populate the cells, but not when the external application pushes the data over. I assume this is because it is not actually opening the excel workbook. There are no features in the external application that can trigger this or open the file before data is pushed over. Is there are way to make a closed excel run that macro when the data is received?

 

[Yongle]

Would running the "small macro" automatically when the workbook opens do what you require ?

If so place code in ThisWorbook code window (NOT in a module)

Private Sub Workbook_Open()
    Call NameOfSmallMacro
End Sub

 

[LeanneBlakemore1972]

Hi and thank you for the response. Unfortunately no. What we are doing is analyzing data from a flow cytometer. Most of the batches include file sets from multiple donors. So we run file 1, 2, 3 from Donor 1.. then again for donor 2, etc. The way the external application works is that is just keeps adding columns of the data no matter how many donors we analyze. What the small macro does is tells the excel to move the data we designate from Sheet 1 into Sheet Results once we have analyzed file 1, 2, 3 from Donor 1, then clear the contents of Sheet 1. Then the next donor data can populate those cells in Sheet 1. We analyze files 1, 2, 3 of Donor 2 and the macro moves those. Repeat until all donors are analyzed and all specific data is moved. We normally do this in real time in our current analysis software with the excel open, so no issue. We are being forced to move to a new software that does not allow the excel to be open when we are running a batch of donors.

 

[Yongle]

If you cannot open the workbook then you cannot amend any of its values - so that seems to be a dead end

Could you use a 2nd workbook to get the values from the closed workbook and manipulate the values in the 2nd workbook ?

 

[Fazza]

hello & welcome Leanne
[Edit: Leanne, I re-read the question & realised I have not answered the question asked. Still I'll leave the comments here & they may still be helpful.]
I suspect, based on my reading, that what is needed isn't sufficiently clear.
Consider how you would manually get the required result if you opened the file - after it has been updated by the external application.
The logic that applies, the data that changes, and all the steps done. I expect if that is described sufficiently then VBA code can be written to do that automatically.
(And conversely, if it isn't or can't be described, likely no-one on the forum can write the VBA to do it.)
OK?
Regards, Fazza

 

[Fazza]

I see your comments, Yongle, about if you cannot open the workbook you cannot amend its values
In general, values can be amended without opening workbooks - like updates to databases, queries can change closed Excel files.