excel macro from a batch file?

  • We are mainly a UNIX environment so need processes that we can automate in a non-gui world, even if it has to be in a Microsoft environment. Basically we need to set up an automated process in which we can receive an excel spreadsheet, run a batch file which calls excel with a macro to extract certain data and create an output file that we can then use in our UNIX environment (preferably delimited ASCII).

    The piece I need help with is how to run some macro (if that is what I need to do) from a batch file or command line, that will take as arguments an inputfile and an output file. The input file is an excel spread sheet the output file is ASCII data that I need to extract from the excel spreadsheet.

    Once I have this I can get it back into the UNIX world no problem and be able to further process it as needed.

    Your help is appreciated,

  • every night I run a macro based upon the task scheduler. It launches about 3 am.

    The macro on my spread sheet is an auto open sheet. The macro checks it's last update time and imports the new data from a serialized file name.

    It massages the file into the database and closes down and exits.

    I also have macro's which take excel spread sheets and export them based upon dated files. They start as xls files and are saved as delimited text.

    So if you can serialize your file names and import them by checking current date or missing dates you can do it.

    You may need to take a leap of faith and not use the old icky unix methods though.

    You could also make a xls sheet that pulls all the file names from a directory and put them into a combo box and then you could select them from that list and initiate it semi manually.

    Hope that helps with out the actual app it is kinda what I see you needing.

  • You should be able to include a .vbs or .js Script to be called from within your batch to do what you want. If you want to take that route, post back and we can get it squared away I'm sure.

  • Thanks for the replies. I must I only know enough about Excel to be dangerous. A lot of that stuff I read in the replies sounds promising but please break it down into layman's terms. I just need to get the data out of an excel spreadsheet and get it into UNIX so converting an XLS spreadsheet into a CSV is fine as long as no human has to do it. Running as a task works for me.

    Epidemic: you sound like you are doing much like what I need. Can you elaborate and give me a sample please.


  • Will do.

    I have tied to annotate the file so it makes sense.

    For future people to frame off of.

    I will send you some of the code on mondayish.

  • This has lots of good code in it and is remarked a little.

    I will attach it but it is very specific to my task. You will have to work a bit to figure out how to apply it to your task at hand

  • this file is used to strip a file down to the records only. It rips out all header info and anything that does not pertain to data to be imported into the macro above.

    Like I have said. This will not apply to your task directly but perhaps you can get something out of my code that you can apply

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!