VBA - excel forgets comma when pasting external data
I am trying to write vba code using DDE method. The code is for copying a set of excel table columns and pasting it into a parametric table in EES (Engineering Equation Solver) software. Then the EES code is run to solve the table, generating the output columns. This data is then copied and pasted back into the excel file containing the input data.
Since I am new to vba, I used the example provided by EES (Executing EES Macro Commands from EXCEL) as a guide.
The problem occurs when data is inserted back into the excel spreadsheet: the code seems to ignore the decimal separator! Both my excel and EES are set to work with a comma as a decimal separator and when I manually copy the results from EES and paste to beat the number pasted normally with a comma (also the numbers from excel are correctly pasted into ESS).
However, when I install the code to accomplish this task, numbers like "15.47" are inserted into excel as "1.55E + 12" or "1547421377050". The code is shown below:
Private Sub cmdDDE_Click()
Dim ChNumber As Integer
Dim myShell As String
ChNumber = -1
myShell = frmEESDDE.txtApp.Text
On Error Resume Next
'Copy selected rows into clipboard
Range("B2:G1401").Select
Selection.Copy
Shell_R = Shell(myShell, 1)
If Shell_R <> "" Then
'Initiate DDE
ChNumber = Application.DDEInitiate(app:="ees", topic:="")
If ChNumber <> -1 Then
'Open EES
Application.DDEExecute ChannelNumber, "[Open C:\EES\Tablesolve.ees]"
'Paste data
Application.DDEExecute ChannelNumber, "[Paste Parametric 'Table 1' R1 C1]"
'Solve parametrictable
Application.DDEExecute ChannelNumber, "[SOLVETABLE 'TABLE 1' Rows=1..1400]"
'Copy results
Application.DDEExecute ChannelNumber, "[COPY ParametricTable 'Table 1' R1 C7:R1400 C14]"
'Choose separators
Application.DecimalSeparator = ","
Application.ThousandsSeparator = "."
Application.UseSystemSeparators = False
'Paste results from EES into EXCEL
Application.Paste Destination:=Worksheets("Sheet1").Range("H2:O1440")
Application.UseSystemSeparators = True
'Quit EES and Terminate DDE
DDEExecute ChNumber, "QUIT"
Application.DDETerminate ChNumber
Else
MsgBox "Unable to initiate connection to EES", vbExclamation, "EES DDE"
End If
frmEESDDE.Hide
Else
MsgBox "The application, " & myShell & ", was not found", vbExclamation, "EES DDE"
End If
PS = As you can see, I tried to set the decimal separator to "," as suggested in this link: Insert decimal numbers in excel separator / comma and decimal point but that didn't work either!
I appreciate your help!
source to share
Problem solved! I also posted a question on the stackoverflow Portuguese community and got a very helpful answer. With some minor adjustments, it solved my problem! The link to the solution in Portuguese follows:
But for those who would prefer the English version, I'll try to summarize what was done to fix the code:
1- declare range variables:
Dim interval As Range 'represent the cells in which info was pasted
Dim Cell As Range 'to allow cell format to be changed
2- after copying the results from the eternal program and before pasting:
Set interval = Worksheets("Sheet1").Range("H2:O1440") 'set interval to paste the results
interval.NumberFormat = "@" 'set format to text
3- after pasting:
interval.NumberFormat = "General" 'set format to general
For Each Cell In interval
Cell.Value = FormatNumber(CDbl(Cell.Value), 2) 'set only 2 decimal places
Cell.Value = CDbl(Cell.Value) 'set to double
Next
The rest of the code remains as it is.
Special thanks to Cantoni who helped with the solution in the pt.
source to share