Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X
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
What version of Office/Excel?
Note that Mathcad 14 is around for some time, and MS have changed Office significantly since then. Mathcad 14 may not know how to handle.
To be independent: Copy your one-column data to a Notepad file, save as .PRN file and use READPRN to read the data.
Then you can process.
Success!
Luc
The other way 'round is to make sure your excel data is saved as office 2003 or earlier...
Thank you for the answer i will try this tomorrow. I have office 2010,
Regards
Sigurd
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.
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
Stuart,
Why do not you use OpenOffice program (or Google Docs for preview Excel sheet)?
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
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.
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
I didn't try READFILE, although that was next on my list (followed by READCSV if that didn't work). It didn't occur to me to try READEXCEL without specifying the range, because I was specifically interested in seeing when the error occurred, and what the message was. So it seems like there is a bug in both READEXCEL and READFILE, but fortunately one with an easy workaround.
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
True, but only if you know about it.
Isn't that true of all workarounds?
Richard Jackson wrote:
True, but only if you know about it.
Isn't that true of all workarounds?
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(*).
The READEXCEL and READFILE default row limits are a case in point. If Sigurd were to use READEXCEL or READFILE to input his data, then he would *probably* pick up that there were only 65536 rows, but in other circumstances a user might not because there's nothing "in your face" about the error unlike other problems that show up as an error or lack of built-in capability.
Stuart
(*) I wondered if "workaround" was a proper word and whether it had acquired verb status, making "workarounded" legitimate in the eyes of the OED. However, it's still a noun in the OED under the category Computing.
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