›
Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.

Reading data from Excel spreadsheets using Ole functions

rogercurtishallrogercurtishall
in OpenInsight
Hello. I have a program which reads data from an Excel spreadsheet using the OleCreateInstance, OleGetProperty & OleCallMethod functions. Basically, it copies the data and I retrieve it by an OI Get_Property('CLIPBOARD', 'TEXT') call. This works most of the time, but sometimes it fails with no data found on the clipboard. I have error checking for all the ole function calls, but no errors are being reported. Does anyone any experience with this type of functionality and have any idea what is causing this problem? Thanks.

Comments

  • KevinFournierKevinFournier
    edited August 5
    It's tough because an OLE error is not necessarily the same thing as an Excel error. An OLE call can succeed but Excel could still logically error out. It all depends on how Excel handles it. Looking at the Range.Copy method, it doesn't appear to throw any errors if there's nothing to copy, so there's no way for the caller to know what happened.
  • AusMarkBAusMarkB
    How much data in the excel are you expecting to be dealing with?
    Is the current approach selecting the worksheet and copying that to the clipboard, or are you using a more granular approach and specifying particular ranges, for example, a row or an individual cell?
  • rogercurtishallrogercurtishall
    I have been testing with two excel sheets, one 356 and one 18 rows. My program selects and then copies the entire sheet to the clipboard. I can run the program against these 2 sheets successfully most of the time, but sometimes it fails.
  • DonBakkeDonBakke
    Does it fail occasionally on the same machine? I've had inconsistencies with the Paste method, but I've narrowed this down to the same machine.
  • rogercurtishallrogercurtishall
    Hi Don. Is there any solution when this happens on one machine? Thanks.
  • DonBakkeDonBakke
    HI Roger - No, we never discovered the solution so we created a (much slower) workaround. We know the problem only occurred with Citrix stations.
  • rogercurtishallrogercurtishall
    I am just running my OI directly on a Windows 11 computer.
  • BarryStevensBarryStevens
    @rogercurtishall
    I assume you have checked the windows event viewer when this happens?
  • rogercurtishallrogercurtishall
    I am not seeing anything in the event viewer logs for this issue.
  • AusMarkBAusMarkB
    I have been testing with two excel sheets, one 356 and one 18 rows. My program selects and then copies the entire sheet to the clipboard. I can run the program against these 2 sheets successfully most of the time, but sometimes it fails.

    Neither of those sound too ominous for the copy sheet approach, especially when you say the same sheet works most of the time.
    If you can be sure that there should be data, (specifically if you start using different sheets), perhaps you can have a three try loop, (or some other arbitrary number). It doesn't fix the problem, but if you had a loop, checked the contents of the clipboard and if empty, call the copy again with your fingers crossed, you might reduce the number of instances where a failure is an issue.
  • DonBakkeDonBakke
    @AusMarkB - I'm curious, have you used this form of redundancy and OLE Automation and seen results or are you just offering general advice? I'm not being critical, but genuinely curious. I had tried that with my Copy issues and no amount of repeating the method call made it work. It either worked or it didn't. I became convinced it was an environmental issue. Unfortunately we did not have enough permissions on the desktop to troubleshoot this deeply.
  • AusMarkBAusMarkB
    @DonBakke tbh I don't remember. It's been a while since I've played with it. It was just more of a suggestion of what I'd try next, based on the comment that it works most of the time on the same spreadsheet. I interpreted that as it both worked and didn't work using the same machine, but I guess it could be that it works with most machines and just doesn't on a couple.

    So with your experience, I'd come to the same conclusion, that it was environmental, and your hands are tied, but I understood, (perhaps incorrectly), that @rogercurtishall had a subtly different scenario.
  • DonBakkeDonBakke
    @AusMarkB - Well there was no direct answer to my specific question about whether this occurs only on the same machine so all we can do is surmise.
Sign In or Register to comment.