GetSheet from XLS File

[nlk10010]

Hi:

I am on AA 5447_5206 and am trying to load a sheet from an Excel XLS file using the following code (edited):

	
        DIM o AS Office::ExcelDocument	
	o.Load("C:\PcTex\XXXShipmentRevised.xls")
	DIM c as Office::Spreadsheet
	c = o.GetSheet(1)

I keep getting an error on the last line, it seems as far as AA is concerned there is no sheet. The reason I am perplexed is that this code works fine (and has worked fine in other modules) with XLSX files, and if I convert the spreadsheet I'm trying to load into an XLSX file then there's no issue. I've also tried using the Office::Excel2003Document class but that doesn't work either. I've tried the code with other XLS files, even created a simple one from scratch and each time I can import an XLSX version fine, not the corresponding XLS format.

I'd appreciate it if anyone had any ideas, I'm sure there's a simple answer but it may be years before I find it. :)


Norman

[madtowng]

Re: GetSheet from XLS File

Try DIM o AS ::Office::Excel2003Document

[nlk10010]

Re: GetSheet from XLS File

Try DIM o AS ::Office::Excel2003Document

I tried that, there seems to be a difference (at least to Alpha Anywhere) between XLS files created with previous versions of Excel and XLS files created with the newer versions. For example, if I created the XLS file with Excel 2007 Alpha opened it fine, if I created it (or converted an XLSX file) with Office 2016 then it wouldn't (with either Excel2003Document or ExcelDocument).

Don't know why, but thanks for the post trying to help.


Norman

[madtowng]

Re: GetSheet from XLS File

I'm one of those people that typically likes to confirm things (not sure why).
The biggest problem I have is that I stopped using M$Office years ago, and when
I do use spreadsheets, I typically save them as xls instead of xlsx.

I'm going to try to get a spreadsheet created by office2016 and/or office365.

[Davidk]

Re: GetSheet from XLS File

I tried that, there seems to be a difference (at least to Alpha Anywhere) between XLS files created with previous versions of Excel and XLS files created with the newer versions. For example, if I created the XLS file with Excel 2007 Alpha opened it fine, if I created it (or converted an XLSX file) with Office 2016 then it wouldn't (with either Excel2003Document or ExcelDocument).

Don't know why, but thanks for the post trying to help.


Norman

Using MS Excel 2016 - 64-bit - I saved an XLSX file as XLS, and was able to access it without issue with this code...

dim o as Office::Excel2003Document
o.load("C:\AlphaProjects\Info\test.xls")
dim c as Office::Spreadsheet
c = o.GetSheet(1);
dim sr1 as c
sr1 = c.read(2,1)

There must be other information missing about this issue. Also, you should zip and post a sample xls file that you cannot access. Maybe it's the file... maybe it's your machine. You never mention where you're trying to do this. Local dev, WAS Server, IIS Server. And you don't post the error you're getting.

[madtowng]

Re: GetSheet from XLS File

Hi Norman,

It took a bit, but I was able to get a file created by office365 (I'm told its the same as office2016).
The image below shows the debug(1) screen showing it recognized the worksheet, and I was able to
read and write to the worksheet.

I can test further, but would ask that you send sample spreadsheets to [email protected].

2018-12-20_11-15-41.png

[nlk10010]

Re: GetSheet from XLS File

Davidk, madtowng: I didn't get any notifications that you had posted replies, that's why I haven't gotten back with more information.

Roughly speaking, the error involved Alpha not being able to find the worksheet (I realize that doesn't help much if at all, but I'm winging it right now).

Let me go back and try to reproduce the issue and give you more specific information.

Thanks both for your time and interest.


Norman

[nlk10010]

Re: GetSheet from XLS File

Well, I just tried duplicating my previous steps and now do not have any issue reading XLS spreadsheets (this code was in an XBASIC function in a UX component).

The error that WAS reported was "An error occurred getting a spreadsheet" on the c = o.GetSheet(1).

So there are two possibilities:

1. The version change from 5447_5206 to 5487_5217 (the one I'm on now) did the trick, or.....
2. I'm getting a lot older a lot faster than I thought.

I understand #1 is the easy out, but I really worked hard on the issue when did the initial tests. I permuted and modified code, did repeated executions, etc. and continually received the "An error occurred getting a spreadsheet" message. The only way I got the module working was to insist that the company sending the spreadsheets for import make sure they were XLSX (their IT department was actually hand-coding the sheets and felt XLS would be easier).

But, again, not only did I get around the issue by making sure the sheets were XLSX, I now can't reproduce the problem with XLS sheets no matter how hard I try.

Oh, well.

Thanks to you both again for taking the time to post.



Norman

[madtowng]

Re: GetSheet from XLS File

Strange things happen often. It wasn't a waste of time as I learned how to do other things while working on this.
Glad it's working for you now.