Hi All, I have an Excel macro (see below) that I want to run from a VB script, the macro opens a txt file, formats it.

Sub reformat_output()

'

' reformat_output Macro

' Macro recorded 10/11/2010 by Windows XP Mode

''

Workbooks.OpenText Filename:="C:\scripts\wri conversion\output.txt", Origin:=437, _

StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(3, 1), Array(9, 9), Array(10, 1), Array(11, 9), Array(33, 3), Array(43, 9), Array(44, 1), Array _

(49, 9), Array(51, 1), Array(55, 9), Array(57, 1), Array(62, 9), Array(63, 1), Array(67, 9), Array(69, 1), Array(74, 9), Array(75, 1), Array(81, 9), Array(88, 1), Array(94, 9), Array(95, 1), Array(101, 9), Array(102, 1), Array(108, 9), Array(111, 1), Array(116, 9), Array(117, 1), Array(123, 9), Array(124, 1), Array(129, 9), Array(130, 1), Array(132, 9)), TrailingMinusNumbers:=True

End SubThis works great.

I need a VB Script that will open the excel file, run the macro, save the document, and close excel.I have tried to open just the excel file and it doesn't open,

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Set objExcel = Wscript.CreateObject("Excel.Application")

Set objFile =

objFileSystem.GetFile("C:\scripts\Working scripts\reformat_output.xlsm")

objExcel.visible = true

objExcel.Workbooks.Open(objFile.path)

objExcel.Run "reformat_output.xlsm"

objExcel.Quit

Set objExcel = nothingThanks in advance for any help.

Don

Ok, I was able to get it to open and run the macro using the script at the bottom of this message. What it does, is ask if I want to save the file when it tries to quit. The file has already been created, so I want to disable/bypass the save file window, or automatically enter no to save, so Excel closes without user interaction.

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\scripts\WRI Conversion\reformat_output.xlsm")

objExcel.visible = true

objExcel.Run "reformat_output"

objExcel.Quit

Ok, nevermind I got it, so for anyone else looking for that answer here it is I added this to the end of the Excel Macro

activeworkbook.Close SaveChanges:=False

Good luck and happy scripting.

Don

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History