VBA to open .csv input - non-american decimal/comma

[Per Olander]

Hi,

I am trying to write a macro, with input from an online survey tool, which gives the output in .csv format.
I have tried the following two chunks of code:

Sub OpenGPSfile1()
Dim wb As Workbook
Dim ws As Worksheet
Dim Sti As String

Sti = Application.GetOpenFilename(Title:="Vælg fil fra Deltaplan")
If Sti = "False" Then
    Exit Sub
End If

Set wb = Workbooks.Add
Set ws = wb.Worksheets(1)
With ws.QueryTables.Add("TEXT;" & Sti, ws.Cells(1, 1))
    .AdjustColumnWidth = True
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFilePlatform = 65001
    .Refresh
End With

End Sub



Sub OpenGPSfile2()
Dim wb As Workbook
Dim Sti As String

Sti = Application.GetOpenFilename(Title:="Vælg fil fra Deltaplan")
If Sti = "False" Then
    Exit Sub
End If

Set wb = Workbooks.Open(Sti)

End Sub

the problem occurs when some of the open-string answers in the survey includes a line-feed. when using method 1, which I have been told should be most correctly, I get the output in the top half of the attached picture. the words "Med Ny Linje" has been entered as an open-string answer with line-feeds in between each word, but I need them to be in one cell in excel.
if I use method 2, it correctly places all 3 words in one cell (bottom half of attached picture), but then somethinge else goes wrong:
I am danish, and we use the opposite comma/decimal markers as americans, so where you would write one thousand, two hundred and a half like 1,200.50 we write it 1.200,50 - also, the default list separator in danish is ";" and not "," as in american. this can be seen in the right part of the picture.
when using method 2, that would handle line feeds in the desired fashion, it skews up columns F,G,H,I which are GPS coordinates. in method 1 they are correctly displayed as fifty-something in column F, but in method 2 they become fifty millions and something.
this is a problem, since the number of decimals vary from output to output, so I cannot guess where to put the comma. its especially a problem in column I, since thats accuracy in meters, and its very relevant if the GPS spot is accurate within 6,5 meters, 65 meters or sometimes up to several kilometers.

I would prefer not to switch from danish to american decimals, since this is a macro that needs to be run on several other computers as well, and those users should not be forced into using a non-danish system in excel in general.
am I missing something in method 1, specifying how line-feeds should be interpreted?

[Chippy]

If you open the .csv file manually in Excel (via File - Open, running the Text Import Wizard) does the data import correctly? If so, record a macro of you doing those steps and use the Sti string as the file name in the Workbooks.Open code generated by the Macro Recorder (method 2).

[Per Olander]

it doesnt open correctly - rows 4-6 in the picture still shows as in three different lines, where they should be in one row, where cell J4 has a text parsed into three lines.
I have talked to the developer of the survey tool, and whenever I send him an example, it opens just fine on his computer, so the conclusion so far has been that its due to the danish comma/decimal/separator values being different than the standard .csv setting.