# Excel Macro / vbs vs Batch



## Cypinate (May 14, 2012)

Hi gang
So I have a CAD software then can output a .csv file
Unfortunitly the file is very basic in nature
So I recorded a Macro with a hot key ctrl~**** b
This macro cleans up the file nicely
I can manually run this and it works everytime on differnt .csv files

Now I want to build a vbs scirpt or batch file, open the .csv file and run the macro
The batch file does'nt seem to have the switches capable of running a script

call "C:\Program Files (x86)\Microsoft Office\Office14\excel.exe" /e "D:\Users\Jobs\BOM\bom.csv"

The batch file will open the file and I can manually run the macro ctrl~shift b
*****************
Test.vbs script

Option Explicit
Dim objXL
Set objXL = WScript.CreateObject("Excel.Application")
objXL.WorkBooks.Open "D:\Users\Jobs\BOM\bom.csv"
objXL.Visible = TRUE ' Optional
Set objXL = Nothing

This will open the file and to my disbelief there are no macros within the file, so I can not run one
This to me does not make sense, but then again I really don't know alot about scripting 

Any suggestions will be very helpfull, Thanks!

(Cypinate)


----------



## Squashman (Apr 4, 2003)

What does the Macro do to the file? Maybe we can manipulate the CSV with a VBscript or a batch file and ditch the Macro.


----------



## Cypinate (May 14, 2012)

The Macro removes columns and rows, merges the top header and spaces things out
The csv file is a bill of materials with extra data not required for the purchasing dept.
I added a Zip file to the original thread, .bas file and .csv file included

Thanks


----------



## Squashman (Apr 4, 2003)

Probably should have told you that I am horrible with excel. I have no idea what to do with that BAS file. If you could should me the input CSV and the output CSV I may be able to extrapolate from there.


----------



## Squashman (Apr 4, 2003)

Nevermind. Don't think this could be done with batch. You have hard coded CR\LF in your headers. Detail Num and Spare Qty. Batch is going to think that is a new line. If you open it in excel it is fine but batch can't process that.


----------



## Cypinate (May 14, 2012)

This is a by product of the table in the cad software, however I can change this to accommodate
I will post a new csv with just Detail and Spares


----------



## Ent (Apr 11, 2009)

Cypinate said:


> This will open the file and to my disbelief there are no macros within the file, so I can not run one This to me does not make sense, but then again I really don't know alot about scripting


CSV files aren't Excel files (xls) so they can't hold macros. It's analogous to saving a word document as a text file. All that's there is the content; formatting, document metadata, any macros, and everything else is lost.


----------



## Cypinate (May 14, 2012)

My comment
"This will open the file and to my disbelief there are no macros within the file, so I can not run one This to me does not make sense, but then again I really don't know alot about scripting"
If I open this file up using Excel my Personal Marcos are available, If i use a scipt to call Excel to open the file no maros are present, this is why i'm confused, why the difference?
Must be a way to tell Excel open the file using my start file
"IE. C:\Users\cypinate\AppData\Roaming\Microsoft\Excel\XLSTART" \Personal.xlsb
Another scratch my head challange..


----------



## Keebellah (Mar 27, 2008)

Hi, I'll take a look at the module and if you did it correctly it should run from your Excel Personal macro workbook.
You didn't mention it but you are probably using Excel 2010, at least that's Office14
I'll let you know.


----------



## Keebellah (Mar 27, 2008)

I took a look at it.
I placed the module in the Personal macro book (Personal.xlb), opened the csv file and just ran the bom macro, no problem, this is the result in and excel file.


----------



## Squashman (Apr 4, 2003)

Keebellah said:


> I took a look at it.
> I placed the module in the Personal macro book (Personal.xlb), opened the csv file and just ran the bom macro, no problem, this is the result in and excel file.


Hi Keebellah, he is aware that it works when you manually open the CSV and run the macro. The problem he is having is when he opens the CSV with a batch file there is no Macro there to run.


----------



## Keebellah (Mar 27, 2008)

What does the batch file look like?


----------



## Squashman (Apr 4, 2003)

Keebellah said:


> What does the batch file look like?


Look at his first post. He has one line of code for batch and a VBscript as well.


----------



## Keebellah (Mar 27, 2008)

Okay, missed that one.
I just tested it and the file is opened and when I look at view macros it's there to use so either the personal.xlb is not correct or else try saving a personal.xlsm in the xlstart folder


----------



## Cypinate (May 14, 2012)

Keeblash, intresting that you can run the batch file and the macros are available..
I will look into saving a personal.xlsm to my Start dir.
Now the real trick is to run the batch file and auto run the macro, the user can then save as required


----------



## Keebellah (Mar 27, 2008)

All you put in that macro will run


----------

