Running VBA macros for Excel via .bat file

chickyguy

New Member
Joined
Mar 27, 2024
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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.
Code:
: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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 1
Solution
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.
 
Upvote 0
It wasn't much, but glad I could help. Thanks for the recognition.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top