Can I pass arguments to my formatted empty spreadsheet file?

[Paul H]

Now that I have my macro running (to import a .CSV file) when I push the
button I added to the tool bar, I need a way to pass the file to be imported
into my formatted empty spreadsheet, and run the macro. Is this possible?
Can I pass arguments, maybe from a .BAT file, to eliminate the user from
being involved except to start some process, this being one of many steps to
run at night. Excel 2003 and 2007. I cannot find anything appropriate
about arguments in my very good Excel 2007 book by Walkenbach. TIA, Paul

 

[Patrick Molloy]

Please search this group and read the thread :

subject: Schedule a macro on a specific time

 

[Paul H]

I searched Google and found the reference. It required me to know VB, which
I do not. I am hoping to find an example of a .Bat file or some similar
method to pass argument(s) and run a specific excel spreadsheet, using a
built-in macro, which I will have have recorded. I suppose I could rename
the current day's .CVS file to the name the macro has built-in, then rename
the resultant .XLS file to today's date as a subsequent step, so I'd just
need to start the spreadsheet as one of many steps in the night job.

 

[Patrick Molloy]

if you use the workbook OPEN event to fire the macro, then you can use
Windows Task Manager to run the spreadsheet automatically

open the spreadsheet
go to the IDE (development environment) by pressing ALT+F11
go to the Project Explorer (Ctrl+R)
lookfor VBA(project(your workbook), then right click on ThisWorkbook and
select View Code
where you see (General) change to Workbook (its a dropdown)

you will see this:

Private Sub Workbook_Open()

End Sub

now add the name of your macro......

for example:
Private Sub Workbook_Open()
macro1
End Sub


whenever the workbook opens, the Open event fires and this in turns kicks
off your macro.

save the workbook

Open Task Manager on the PC that will run the spreadsheet .... setting up a
new task should be self explanatory. the program to run is NOT excel.exe but
it is the full name of your workbook