Hi all!
I'm trying to embed an excel file in mathcad 14 using the insert--> component--> excel file--> "create from file" (display as icon).
All I want is data from A1:A166053 (i know it is a lot of data 
), this data represent the significant wave height since 1957.
After I "create from file" I select 0 input and 1 output and then define the range i need. When this is done the icon appers in mathcad and I define the
name. In this case "Hs". Then the error message "The range value specified is invalid" appears...What am i missing??
Regards
Sigurd Næss
Richard Jackson wrote:
It's a Mathcad problem. Excel used to have a limit of 65536 rows. Mathcad has this number hard coded in as a limit, even though Excel can now have many more rows than that. This is a problem even in Mathcad 15 M030. If you enter 65537 as the upper limit, you get the same error message.
Wow. I didn't know this. I tried the READEXCEL function in M15 M040 and that has the same limitation (uses same underlying code?). The only thing I can suggest immediately is to split the data into columns of length < 65535 and do multiple reads, or write a component to open an Excel worksheet and get the data that way. Unfortunately, I don't have Excel so I can't check the attached code (it's just my best guess) and I'm sure somebody (
) will have a better way.
Sub TextBoxEvent_Exec(Inputs,Outputs)
filename = Inputs(0).Value
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filename)
'// count the rows
intRow = 2 '// allows for header
Do Until objExcel.Cells(intRow,1).Value = ""
intRow = intRow + 1
Loop
Dim data()
ReDim data(intRow)
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
data(intRow-2) = objExcel.Cells(intRow, 1).Value
intRow = intRow + 1
Loop
objExcel.Quit
Outputs(0).Value = data
TextBox.Text = "First Value = " & data(0)
End Sub
Stuart
VladimirN. wrote:
Stuart,
Why do not you use OpenOffice program (or Google Docs for preview Excel sheet)?
I do Vladimir - I created an xlsx worksheet with 160000 rows using LibreOffice Calc to test the READEXCEL function. Unfortunately, I don't know how make the Excel component use Calc instead of Excel and the data is in binary form in Sigurd's xmcd file, so I can't export it or cut/paste it into Calc.
I suppose I could have tested the component by opening my test worksheet in Calc, but I'm not familiar with the LibreOffice API, and, as I'm busy trying to wrap my brain cell around Python, Octave and Haskell at the moment, I don't feel desperate or bored enough to learn it!
... Well, OK. I couldn't get past first base trying to open Calc from within a component - - I hang at the opening the LibreOffice ServiceManager. This seems to be the recommended way of starting the SM
| Set oManager = CreateObject("com.sun.star.ServiceManager") |
but I usually get a lot of waiting followed by a 'Null' error, occasionally an "ActiveX component can't create object: 'com.sun.star.ServiceManager'" message. Unfortunately, I haven't been able to resolve this problem. 
Stuart
Richard Jackson wrote:
I tried the READEXCEL function in M15 M040 and that has the same limitation
I just tried READEXCEL in M15 M030 and it read in 75000 rows with no problem.
Interesting. I thought I'd recheck in case I'd been careless or suffered from finger trouble. File created in LibreOffice 5 Calc and checked in Excel Mobile on a Win 10 PC.
Just for good measure ...
M15 M040
Prime 3.1
So, both M15 M040 and Prime 3.1 will read in more than 65536 rows but only if you tell them to ... a bit dangerous and seemingly in conflict with the documentation in the case of M15's READFILE?
Stuart
Richard Jackson wrote:
... So it seems like there is a bug in both READEXCEL and READFILE, but fortunately one with an easy workaround.
True, but only if you know about it. And only if you know that you've got to know either the exact number of rows to read in or can put some appropriate upper bound on the number of rows and remember to get rid of the NaNs (or whatever you've chosen as the emptyfill).
Stuart
Indeed, it is, but the problem with some categories of workaround is that it' not necessarily obvious that a problem exists that needs to be worked around(*).
When the bridge falls down, it becomes obvious that there was a problem 
Hello again!
First! Thank you all for contributing to my question, things got a bit too advanced for me towards the end, but all is appreciated 
I managed to import the data, I included a new column which represents the year the wave height was collected. Is there a way to find out the max value for each year, and have that returned in a nice table?
I tired a bit myself, extracting submatrix for a year and finding the max value for that submatrix. But I couldn't make Mathcad do all the steps from 1957-2014.
Hope I expressed myself correct and I appreciate all the help I can get
Regards
Sigurd
Sigurd Næss wrote:
Hello again!
First! Thank you all for contributing to my question, things got a bit too advanced for me towards the end, but all is appreciated
I managed to import the data, I included a new column which represents the year the wave height was collected. Is there a way to find out the max value for each year, and have that returned in a nice table?
I tired a bit myself, extracting submatrix for a year and finding the max value for that submatrix. But I couldn't make Mathcad do all the steps from 1957-2014.
Hope I expressed myself correct and I appreciate all the help I can get
Regards
Sigurd
Hi Sigurd,
Is this of any help? There's another approach in the worksheet that uses a few of my utility functions to extract the data.
Could you please post the data file as well? It will help verify the solution. Thanks,
Stuart
