excelvbaexcel-import

QueryTables problem with scientific notation


I'm trying to import data from a text file, there is no problem with that, the code runs and the data is imported but not correctly. I have text files with this type of numbers: 1.3309884e-13

When I'm importing the values, Excel is not taking the correct negative exponent and is returning 1.3309884e-5 and similar. The code is this:

Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Worksheets("DEPOSITION BC")

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select Black Carbon Dry Deposition bin 01 file (BCDP01)")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, _
Destination:=ws.Range("C5"))
    .TextFileParseType = xlDelimited
    .TextFileDecimalSeparator = "."
    .TextFileCommaDelimiter = True
    .TextFileStartRow = 23
    .RefreshStyle = xlOverwriteCells
    .Refresh
End With 

I don't know how to avoid this problem.


Solution

  • I've already found the conflict when importing. If I also set the thousands separator, the problem is solved:

    Dim ws As Worksheet, strFile As String
    Set ws = ActiveWorkbook.Worksheets("DEPOSITION BC")
    
    strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select Black Carbon Dry Deposition bin 01 file (BCDP01)")
    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, _
    Destination:=ws.Range("C5"))
        .TextFileParseType = xlDelimited
        .TextFileDecimalSeparator = "."
        .TextFileCommaDelimiter = True
        .TextFileStartRow = 23
        .TextFileThousandsSeparator = False
        .RefreshStyle = xlOverwriteCells
        .Refresh
    End With