{"id":11141,"date":"2021-12-06T23:33:54","date_gmt":"2021-12-06T23:33:54","guid":{"rendered":"https:\/\/lgildv5i97.onrocket.site\/answers\/?post_type=question&#038;p=11141"},"modified":"2021-12-06T23:34:15","modified_gmt":"2021-12-06T23:34:15","slug":"vb-script-open-excel-run-macro-save-close","status":"publish","type":"question","link":"https:\/\/computing.net\/answers\/office\/vb-script-open-excel-run-macro-save-close\/12546.html","title":{"rendered":"Vb Script Open Excel, Run Macro, Save, Close"},"content":{"rendered":"<p>Hi All,<\/p>\n<p>I have an Excel macro (see below) that I want to run from a VB script, the macro opens a txt file, formats it.<br \/>\nSub reformat_output()<br \/>\n&#8216;<br \/>\n&#8216; reformat_output Macro<br \/>\n&#8216; Macro recorded 10\/11\/2010 by Windows XP Mode<br \/>\n&#8216;<\/p>\n<p>&#8216;<br \/>\nWorkbooks.OpenText Filename:=&#8221;C:\\scripts\\wri conversion\\output.txt&#8221;, Origin:=437, _<br \/>\nStartRow:=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 _<br \/>\n(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<br \/>\nEnd Sub<\/p>\n<p>This works great.<\/p>\n<p>I need a VB Script that will open the excel file, run the macro, save the document, and close excel.<\/p>\n<p>I have tried to open just the excel file and it doesn&#8217;t open,<\/p>\n<p>Set objFileSystem = CreateObject(&#8220;Scripting.FileSystemObject&#8221;)<br \/>\nSet objExcel = Wscript.CreateObject(&#8220;Excel.Application&#8221;)<br \/>\nSet objFile =<br \/>\nobjFileSystem.GetFile(&#8220;C:\\scripts\\Working scripts\\reformat_output.xlsm&#8221;)<br \/>\nobjExcel.visible = true<br \/>\nobjExcel.Workbooks.Open(objFile.path)<br \/>\nobjExcel.Run &#8220;reformat_output.xlsm&#8221;<br \/>\nobjExcel.Quit<br \/>\nSet objExcel = nothing<\/p>\n<p>Thanks in advance for any help.<\/p>\n<p>Don<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":4},"question-category":[53],"question_tags":[],"class_list":["post-11141","question","type-question","status-publish","hentry","question-category-office"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/11141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=11141"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=11141"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=11141"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=11141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}