VBA - excel neglects comma when pasting external data

Question

I'm trying to write a vba code using the DDE method. The code aims to copy a set of columns of an excel table and paste it in a parametric table in the EES (Engineering Equation Solver) software. Then an EES code is run to solve de table, generating columns of output data. This data is then copied and pasted back in the excel file that contains the input data.

Since I'm new to vba, I've used the example provided by EES (Executing EES Macro Commands from EXCEL) as a guideline.

The problem occurs when the data is pasted back in the excel spreadsheet: the code seems to be neglecting the decimal separator! Both my excel and EES are set to work with the comma as the decimal separator and when I manually copy the results from EES and paste then to excel the number is pasted normally, with the comma (also the numbers from excel are correctly pasted into ESS).

However, when I set the code to perform this task numbers such as "15,47" are pasted in 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've tried to set the decimal separator to "," as suggested in this link: Pasting decimal numbers in excel / comma and point decimal separator but it didn't work either!

I appreciate your help!

Mariana Mariana · Accepted Answer · 2015-07-16T15:02:49

Problem solved! I also posted the question in the portuguese speaking community of stackoverflow and got a very helpful answer. With little adjustments it solved my problem! The link to the solution in portuguese follows:

https://pt.stackoverflow.com/questions/74860/vba-excel-n%C3%A3o-reconhece-v%C3%ADrgula-de-dados-externos

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 esternal 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 stays as it is.

Special thanks to Cantoni who helped with the solution in the pt version.

VBA - excel neglects comma when pasting external data

3 Answers