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 Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History