I am trying to send rows and columns of data from excel to mathcad using VBA. The problem is that the array that I send to the mathcad worksheet only appears if I manually type the variable name in the mathcad worksheet. In addition, when i save the file, the data is lost.
What I am trying to do is to take an array from excel, create a new blank mathcad worksheet, send the array to mathcad, save it and close, while retaining the array in the mathcad sheet once it is closed.
Below is the code I have been using. Any help/suggestions would be greatly appreciated.
Public Sub ExportToMathCad_test2()
Dim values(9, 2) As Double
Set mc = CreateObject("Mathcad.Application") mc.Visible = True Path = "C:\Documents and Settings\ladubecc\My Documents\Work\Current Projects\TEDAM\tester.xmcd" Set wk = mc.Worksheets Set ws = wk.Open(Path)
For I = 0 To 9 For J = 0 To 2 values(I, J) = ActiveSheet.Range("A1:B10").Cells(I + 1, J + 1).Value Next J Next I
When you do a normal assignment (i.e. not via the automation interface) you type in the variable name, the assignment operator, and the value you are assigning. You can then evaluate the variable by typing in the variable name and the evaluation operator. The value is then displayed. If you save the worksheet everything you typed in is saved with it, but not, in general, the calculated results.
When you do an assignment via the automation interface using SetValue it creates a variable according to the name you supply, and assigns a value to it. It does this assignment prior to any variable assignments you have typed into the worksheet. If you like, you can think of it as being just above and to the left of anything that's visible on the sheet. It does NOT modify the worksheet in any way, it just creates a variable in the background and assigns the value to it. If you wish to see the value, you must use the evaluation operator. If you save the worksheet, the variable and it's assigned value are not saved, because there is no assignment to that variable on the worksheet.
What you want to do is possible in MC12 and after, by modifying the XML in the worksheet file, but if you want to modify or create any significant amount of the worksheet programmatically it's not trivial. Stuart posted a simple example of this somewhere.
Perhaps we need to back up a little though, and ask "why do you want to do it"? If you don't know what the name of the variable is going to be before it's assigned, then how do you propose to write anything useful in the worksheet that does something with the value passed to it?
What is your end objective? Perhaps with more information we can suggest an alternative approach that achieves that objective.
PS. Eden already said it, but I'll repeat it anyway. Please don't cross post. It can lead to duplicate answers, and/or answers that are mixed up between two threads. Everything then gets really hard to follow.
On 5/18/2007 6:29:35 PM, rijackson wrote: >PS. Eden already said it, but >I'll repeat it anyway. Please >don't cross post. It can lead >to duplicate answers, and/or >answers that are mixed up >between two threads. >Everything then gets really >hard to follow.
If you go to the other post you can delete it to avoid mixed up answers.
Thanks for your reply. The end objective is just to simply store arrays of data in Mathcad. There is no need to do any computation with the data at this point. All I need to to is to send the data from EXCEL to Mathcad, and save the mathcad file.
I assume that when you say "There is no need to do any computation with the data at this point", you mean that in the future there will be, in which case that would be the end objective. If all you truly want to do is save some data this is an incredibly convoluted way to do it, and you will end up with a file that does not store the data particularly efficiently, and can only be read by proprietary software.
The only way to do what you ask is to modify the XML in MC12 or later. As I recall, Stuarts example created a scalar variable assignment in the worksheet. I can't locate that example, but I expect Stuart can either point to it or repost it.