VBA - excel does not recognize external data comma

Question

VBA - excel does not recognize external data comma

Navigation

#1 by (0 votes)

1

Good morning everyone!

I'm trying to integrate excel with other software (the EES - Engineering Equation Solver) through vba code.

My program copies certain columns from a table, transfers to the other program, performs the calculations, copies the results, and pastes the corresponding columns of my table into excel. The EES help site itself provides a sample program for operation, which I am using as a basis.

The problem is that my program returns decimal values and excel is not recognizing the comma of the pasted data! The strange thing is that decimal data copied from excel to EES is interpreted correctly and if I copy and paste the EES data manually into the spreadsheet the number comes with the comma correctly!

My excel is already set to recognize comma as decimal separator, equal to EES. The code is as follows:

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

End Sub

PS = Note that I already tried using the Application.DecimalSeparator="," command but it did not work. My result of, for example, "15,47" appears in excel as "1.55E + 12" or as "1547421377050".

Thanks in advance for the help!

excel vba

asked by anonymous 15.07.2015 / 15:58

1 answer

RadioButton Grouping in Windows Form Export from SQL to Excel and perform SyntaxError error: unexpected ',', expecting end-of-input

score 0 · Accepted Answer

I do not know about this application (ESS), so it's difficult to help you in a more specific way.

However, there is something generic that you can do that will most likely solve your problem, you will include this in your VBA in such a way that the process is automatic.

As you can see, what is happening is what Excel is waiting for, "" as a decimal separator, but it is being pasted. " The 5 steps below are summarized as follows:

Change the type of all cells that will receive number to text
Paste the data from the EES
Replace, for each cell, "." by ","
Change the type of all cells to General
Force each cell to be recognized as Double

See the steps detailed below:

Declare the range to use as a range, to avoid repeating code:

Dim Range As Range

Set Range = Worksheets ("Sheet1") Range ("H2: O1440")

Before step of pasting the data into Excel, change all cells that will receive numbers to the Text type. This can be done with the following statement:

range.NumberFormat="@"

Following the above step, you should execute the statement that pastes the data into Excel, and then run the code snippet below. This section replaces, for each cell in the range, the "." by the character ",".

Dim Cell as Range

For Each Cell In Range Cell.Value = Replace (Cell.Value, ",", ".") Next

Now return all the cells in the range to the general format:

range.NumberFormat="General"

As the final step, run the code snippet below, which will convert the value of each cell to a Double. This should be done because Excel will recognize each value as text and you will not be able to apply accounts and formulas to them.

Dim Cell as Range

For Each Cell In Range Cell.Value = CDbl (Cell.Value) Next

Some Comments

Step 3 could be done using Replace from the Range itself. This would eliminate the need to loop. However, embore works when you record a macro, it does not work when you run the macro. People have reported this (see: link )

Depending on the number of cells that are to be traversed by the two for loops, the solution may be slow.

Both step 3 and step 5 are performed by some kind of Excel bug and these are the slowest steps as they require the loop.