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.
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.