Running VBA macros for Excel via .bat file

[chickyguy]

Hey all,

It's not exactly related to VBA and excel, but I hope this has enough relation to it.

I wrote a .bat file. The purpose of this .bat is to watch a folder for new files, and if so, call a VBA command.

:loop
    dir /b "%folder%" > nul 2>&1
    if errorLevel 1 (
        timeout /t 30 /nobreak >nul
    ) else (
        call :execute_vba
    )
    
:execute_vba
   ...callVbaFunction

There was an initial implementation where I would replace callVbaFunction to open the excel sheet and then run the macro. However, this doesn't suit my purpose, so I scrap it.

The excel sheet is to meant to remain open, and any new files in the folder would appear on the sheet. Kind of like a watch list, so it can't run the macro on "open".

Would appreciate any advice, be it to contain within VBA exclusively or to still have it in a .bat file. Working in w10, so unable to have a CRON job and would think a .bat would work as a substitute.

 

[Micron]

Why not store the last file count on a sheet, then when wb opens and every x minutes, poll the folder with VBA and compare the count to the stored value? If there are more files in the folder than the count, do whatever. Not sure how you are/were distinguishing between new vs what's on your sheet, but that part would probably be the same.

 

[chickyguy]

You are right... haven't considered VBA to do that.

I'm currently having it run a function every 30 seconds, and for each file in the watched folder, run the process. Then shift the file out to another folder.

Thanks for the tip.

 

[Micron]

It wasn't much, but glad I could help. Thanks for the recognition.