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 Sub
This 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 = nothing
Thanks in advance for any help.
Don
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