vb script open Excel, run macro, save, close

November 2, 2010 at 10:47:12
Specs: Windows XP Pro, E7500 2.92 GHz 4 GB
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.Run "reformat_output.xlsm"
Set objExcel = nothing

Thanks in advance for any help.


See More: vb script open Excel, run macro, save, close

Report •

November 4, 2010 at 12:28:55
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"

Report •

November 4, 2010 at 12:40:03
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.


Report •

Related Solutions

Ask Question