Jim - There is no native OpenInsight function that will read data from Excel. There is, however, functionality within OpenInsight to achieve this. The underlying solution is to use COM to interact with Excel Office Automation.
There are several posts on the WORKS site that have code snippets to show how this is done (most of them provided by Bob Carten). Note that these all show how to export data to Excel, not import data. But all of the objects, properties, and methods are documented to get you there. That said, if you have no experience with this, there will be a learning curve.
I suggest two other solutions, especially if you want a solution relatively soon. The first option is to use AI (Caude, ChatGPT, Gemini, etc.) to create a PowerShell script that will open an Excel document, read the data, and export it to a text or csv file. You can then call PowerShell to run your script from within OpenInsight using the Utility("RUNWIN") service or (better yet) using the SRP_Run_Command utility. From there you obviously know how to read data from a text based file.
The second option is to contact me offline. We already have a full fledged function that allows you to interact with Excel documents, creating, updating, or reading data. It's a commercial product (albeit not advertised on our sites) which is why we can discuss offline.
Or you can get a friendly code snippet from Barry to get you most of the way there!
I haven't tested his code, but he is using the COM/Office Automation solution I mentioned. I imagine you could adapt it to your needs. Note, this is not pulling data back into OI directly. It is saving it as a text file which would require you to import.
My only caution is getting into the bad habit of writing one-off routines rather than building a generalized solution. I see this all too frequently, and before you know it you have several routines that do generally the same thing, but most of the common code has been repeated rather than put into a helpful utility.
For example, using our function (called Excel_Services), this could be done as easy as this:
DocumentPath = 'C:\Temp\TestFile.xlsx'
Excel_Services('OpenDocument', DocumentPath)
Excel_Services('SelectCells', '', 'A1', '', True$, True$)
GridArray = Excel_Services('CopyToClipboard', '', '', '', @FM, @VM, True$)
Excel_Services('CloseExcel') This will open the Excel document, select all cells starting from A1 and go to the right and bottom of a table, copy the results to the clipboard, convert the data to @FM/@VM array, return this into the GridArray variable, and then close excel.
We already have code that reads from a csv file, this will allow me to reuse that code (it has all kinds of error checking bulit in) by simply writing the Excel data to intermediate csv file.
I also used Perplexity to generate a powershell script, AI is getting so clever and useful.
Jim - You ought to be able to use Perplexity to help you with the Basic+ code. Give it Barry's code so it will learn how Basic+ interacts with COM. Also let it know this is for Excel Automation. Armed with those two pieces of information, it should be able to help build additional calls when required.
Another valuable feature in Excel for building automation logic for Excel is to enable the Developer tab and use the Macro recording tool. Then perform an action with recording on and then turn off recording when you are done with your action. This will produce VBA script that does what you just did manually. This VBA script will be the foundation of your Basic+ implementation. This is where Perplexity will shine for you.
Comments
There are several posts on the WORKS site that have code snippets to show how this is done (most of them provided by Bob Carten). Note that these all show how to export data to Excel, not import data. But all of the objects, properties, and methods are documented to get you there. That said, if you have no experience with this, there will be a learning curve.
I suggest two other solutions, especially if you want a solution relatively soon. The first option is to use AI (Caude, ChatGPT, Gemini, etc.) to create a PowerShell script that will open an Excel document, read the data, and export it to a text or csv file. You can then call PowerShell to run your script from within OpenInsight using the Utility("RUNWIN") service or (better yet) using the SRP_Run_Command utility. From there you obviously know how to read data from a text based file.
The second option is to contact me offline. We already have a full fledged function that allows you to interact with Excel documents, creating, updating, or reading data. It's a commercial product (albeit not advertised on our sites) which is why we can discuss offline.
Equ Tab$ To \09\ Equ wdFormatText to -4158 ReadCount=0 WriteCount=0 PhoneCount=0 Stopped=0 SkipCount=0 err=null$ CallCentreInputFile=Field(AllFiles,@rm,1) If not( dir(CallCentreInputFile)<1> gt 0 ) Then Msg(@Window,"CallCentre input file was not found|":CallCentreInputFile) Return 0 End file=CallCentreInputFile ObjExcel = OleCreateInstance("Excel.Application") objWorkBooks = OleGetProperty(objExcel, 'WorkBooks') is_ok = ( oleStatus() eq 0 ) if is_ok else err = 'Unable to start Excel' end if is_ok then objWorkBook = OleCallMethod(objWorkBooks, 'Open', file) is_ok = ( oleStatus() eq 0 ) if is_ok else err = 'Unable to Open |' : quote(file) end End if is_ok then buffer = space(256) ret = GetTempPath(len(buffer), buffer) path = buffer[1,ret] ret = GetTempFilename(path, 'OI', 0, buffer) tNewfilename = buffer[1,\00\] osdelete tNewfilename x = OleCallMethod( objWorkBook,"SaveAs", tNewfilename, wdFormatText) is_ok = ( oleStatus() eq 0 ) if is_ok else err = 'Unable to SaveAs |' : quote(tNewfilename) end end *x = objWorkBook->Close(0) *x = ObjWorkBooks->Close(0) Quit = OleCallMethod(ObjExcel,'QUIT',1) If err Then Msg(@Window,"**ERROR**|":err) return endI haven't tested his code, but he is using the COM/Office Automation solution I mentioned. I imagine you could adapt it to your needs. Note, this is not pulling data back into OI directly. It is saving it as a text file which would require you to import.
My only caution is getting into the bad habit of writing one-off routines rather than building a generalized solution. I see this all too frequently, and before you know it you have several routines that do generally the same thing, but most of the common code has been repeated rather than put into a helpful utility.
For example, using our function (called Excel_Services), this could be done as easy as this:
DocumentPath = 'C:\Temp\TestFile.xlsx' Excel_Services('OpenDocument', DocumentPath) Excel_Services('SelectCells', '', 'A1', '', True$, True$) GridArray = Excel_Services('CopyToClipboard', '', '', '', @FM, @VM, True$) Excel_Services('CloseExcel')This will open the Excel document, select all cells starting from A1 and go to the right and bottom of a table, copy the results to the clipboard, convert the data to @FM/@VM array, return this into the GridArray variable, and then close excel.
I think i can do what I want with Barry's code.
We already have code that reads from a csv file, this will allow me to reuse that code (it has all kinds of error checking bulit in) by simply writing the Excel data to intermediate csv file.
I also used Perplexity to generate a powershell script, AI is getting so clever and useful.
And again many thanks.
Another valuable feature in Excel for building automation logic for Excel is to enable the Developer tab and use the Macro recording tool. Then perform an action with recording on and then turn off recording when you are done with your action. This will produce VBA script that does what you just did manually. This VBA script will be the foundation of your Basic+ implementation. This is where Perplexity will shine for you.