0

I have an excel workbook that uses a hotkey that launches a batch file, which launches a Node script, which updates a CSV file. Technical details on that are further below.

The workbook uses the CSV file as a data source. I can manually update the Workbook with the data from the CSV file by going to Data > Refresh All > Refresh All.

Is there any way to trigger an update in the workbook once there is new data in the CSV file, or when the batch file finishes? Conceptually, I'm asking how an external event can trigger something in Excel.

Here are fine details on the process:

Flochart

When a hotkey is pressed in the Excel workbook, it launches MS console ("cmd.exe") and passes the location of a batch file to be ran and the value of the selected cell. The reason the batch file is run this way is probably not relevant to this question but I know it will be asked, so I'll explain: The batch file is to be located in the same directory as the workbook, which is not to be a hard-coded location. The problem is that launching a batch-file/cmd.exe directly will default to a working directory of C:\users\name\documents. So to launch the batch file in the same directory as the workbook, the path of the workbook is passed along to cmd.exe like so: CD [path] which is then concatenated inline with another command to launch the batch file with the value of the selected cell as an argument like so: CD [path] & batch.bat cellValue
Still with me?

The batch file then launches a Node script, again with the selected cell value as an argument.

The Node script pulls data from the web and dumps it in to a CSV file.

At this point, the workbook still has outdated data, and needs to be Refreshed. How can this be automatic?

I could just start a static timer in VBA after the batch file is launched, which then runs ActiveWorkbook.RefreshAll, but if the batch file takes too long, there will be issues.

Improve this question
9
  • Did you try to create a macro for this activity? Commented Oct 29, 2019 at 3:35
  • @DavidGarcíaBodego - I'm asking how to have an external event trigger something in Excel. I'll create something once I figure out if it's even possible. Commented Oct 29, 2019 at 12:19
  • Forget the downvote :-) Regarding your question... I am not understanding why to use a batch to introduce the information on excel datasheets when you can do it through Macros. If you can give some more details as input sample and expected output sample, I will help you. Commented Oct 29, 2019 at 12:24
  • @DavidGarcíaBodego - I've updated the question, hopefully this makes it easier to understand. Commented Oct 29, 2019 at 22:21
  • So just create a VBA Macro that begins calling your batch file, so when it ends, it takes the control again. Commented Oct 30, 2019 at 2:58

1 Answer 1

Reset to default
0

I found a solution, although it may not be the most efficient way.

Right now, after Excel launches the batch file, I have it set to loop and repeatedly check the date modified of the CSV file via FileDateTime("filename.csv")

At first, this looping was an issue because I was worried about Excel excessively checking the date modified of the CSV. I thought it may cause issues with resources while it checks however many hundred or thousands of times a second. I could add a 1 second delay with the sleep or wait functions, but those cause Excel to hang. It would be frozen until the CSV files were updated, if at all. The user would have to use CTRL+BREAK in an emergency.

I was able to use a solution that just loops and performs DoEvents while checking until a certain amount of time has passed. This way, Excel is still functional during the wait. More info on that here: https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.