cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - You can change your system assigned username to something more personal in your community settings. X

Excel file in Mathcad 14

snã¦ss
5-Regular Member

Excel file in Mathcad 14

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

17 REPLIES 17
LucMeekes
23-Emerald III
(To:snã¦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

LucMeekes
23-Emerald III
(To:LucMeekes)

The other way 'round is to make sure your excel data is saved as office 2003 or earlier...

snã¦ss
5-Regular Member
(To:LucMeekes)

Thank you for the answer i will try this tomorrow.  I have office 2010,

Regards

Sigurd

RichardJ
19-Tanzanite
(To:snã¦ss)

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.

StuartBruff
23-Emerald II
(To:RichardJ)

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)?

StuartBruff
23-Emerald II
(To:VladimirN)

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

RichardJ
19-Tanzanite
(To:StuartBruff)

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.

StuartBruff
23-Emerald II
(To:RichardJ)

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

RichardJ
19-Tanzanite
(To:StuartBruff)

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.

StuartBruff
23-Emerald II
(To:RichardJ)

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

RichardJ
19-Tanzanite
(To:StuartBruff)

True, but only if you know about it. 

Isn't that true of all workarounds?

StuartBruff
23-Emerald II
(To:RichardJ)

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.

RichardJ
19-Tanzanite
(To:StuartBruff)

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

snã¦ss
5-Regular Member
(To:snã¦ss)

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

StuartBruff
23-Emerald II
(To:snã¦ss)

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

snã¦ss
5-Regular Member
(To:StuartBruff)

Wow! That looks great!

Sigurd

Top Tags