{"id":1407,"date":"2010-12-15T11:14:33","date_gmt":"2010-12-15T11:14:33","guid":{"rendered":"http:\/\/www.mjtnet.com\/blog\/?p=1407"},"modified":"2010-12-15T11:14:33","modified_gmt":"2010-12-15T11:14:33","slug":"mixing-the-native-excel-functions-with-vbscript","status":"publish","type":"post","link":"https:\/\/www.mjtnet.com\/blog\/2010\/12\/15\/mixing-the-native-excel-functions-with-vbscript\/","title":{"rendered":"Mixing the Native Excel Functions with VBScript"},"content":{"rendered":"<p><a href=\"http:\/\/www.mjtnet.com\/macro_scheduler.htm\">Macro Scheduler<\/a> comes complete with some native functions for controlling Excel, such as <a href=\"http:\/\/www.mjtnet.com\/OnlineHelp\/xlopen.htm\">XLOpen<\/a>, <a href=\"http:\/\/www.mjtnet.com\/OnlineHelp\/xlgetcell.htm\">XLGetCell<\/a>, <a href=\"http:\/\/www.mjtnet.com\/OnlineHelp\/xlsetcell.htm\">XLSetCell<\/a> and <a href=\"http:\/\/www.mjtnet.com\/OnlineHelp\/msched12_ftsearch.html?zoom_query=Excel\">others<\/a>.  Obviously, although we intend to add more functions over time, not every possible Excel function has been duplicated.  So sometimes you may want to utilise COM via VBScript which allows you to access the entire Excel API.  There are <a href='http:\/\/www.mjtnet.com\/search.htm?q=\"excel.application\"'>plenty of examples<\/a> of this here in the blog and on the forums.<\/p>\n<p>But what if you want to use a combination of both?  You might already have a script which uses the native XL functions to open a sheet and get or set some data.  Let&#8217;s say you now want to augment this with an Excel method which is not exposed by the native functions.  Rather than re-writing your entire script to use VBScript, is there a way we can let VBScript take over?<\/p>\n<p>While it&#8217;s not possible to share native XL references with VBScript object references, what we can do is have VBScript attach to an open instance of Excel using the <a href=\"http:\/\/support.microsoft.com\/kb\/288902\">GetObject<\/a> function.  So sometime after running XLOpen we could then run a VBScript function which does a GetObject to get an object reference to Excel and then after that we are able to utlise any Excel function we like via VBScript.  <\/p>\n<p>The following script demonstrates:<\/p>\n<pre name=\"code\" class=\"macroscript\">VBSTART\r\n  Dim xlApp\r\n  Dim xlBook\r\n  Sub GetXL\r\n    Set xlApp = GetObject(,\"Excel.Application\")\r\n    Set xlBook = xlApp.ActiveWorkbook\r\n  End Sub\r\n\r\n  Function FindCell(Sheet,Data)\r\n    Dim theCell\r\n    Dim xlValues\r\n    xlValues = -4163\r\n\r\n    Dim xlSheet\r\n    Set xlSheet = xlBook.Worksheets(Sheet)\r\n    xlSheet.Range(\"A1\").Select\r\n    Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)\r\n    FindCell = CStr(theCell.Row) & \":\" & CStr(theCell.Column)\r\n  End Function\r\nVBEND\r\n\r\n\/\/Open an XLS file natively\r\nXLOpen>%SCRIPT_DIR%\\example.xls,1,xlH\r\n\r\n\/\/Call GetXL to give VBScript a reference to the XL instance\r\nVBRun>GetXL\r\n\r\n\/\/now we can access any XL function via VBScript\r\nVBEval>FindCell(\"Sheet1\",\"Price\"),res<\/pre>\n<p>The only thing to be careful of is that there are no existing copies of Excel open before the one opened by XLOpen because according to the <a href=\"http:\/\/support.microsoft.com\/kb\/288902\">Microsoft docs<\/a> GetObject will attach to the first opened instance.  You could of course make the script check for this.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Macro Scheduler comes complete with some native functions for controlling Excel, such as XLOpen, XLGetCell, XLSetCell and others. Obviously, although we intend to add more functions over time, not every possible Excel function has been duplicated. So sometimes you may want to utilise COM via VBScript which allows you to access the entire Excel API. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/1407"}],"collection":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/comments?post=1407"}],"version-history":[{"count":7,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/1407\/revisions"}],"predecessor-version":[{"id":1414,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/1407\/revisions\/1414"}],"wp:attachment":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/media?parent=1407"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/categories?post=1407"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/tags?post=1407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}