none
Data formatting issue using QueryTable object RRS feed

  • Question

  • Hi Friends,

    I am using the following code to copy data from from one .CSV file to another Excel Spreadsheet:

    Sub ImportCSVFile(fullpath As String)
        
        Dim wbk As Workbook
        Dim sht As Worksheet
        
        Set wbk = ThisWorkbook
        Set sht = wbk.ActiveSheet
        
        With sht.QueryTables.Add("TEXT;" & fullpath, Destination:=sht.Range("A1"))
            '.SaveData = True
            .TextFileParseType = xlDelimited
            .TextFileSemicolonDelimiter = True
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .TextFileTrailingMinusNumbers = True
            .SaveData = True
            .Refresh
        End With
        
    ' delete the querytable if there is one
    On Error GoTo nothingtodelete
        sht.QueryTables(1).SaveData = False
        sht.QueryTables.Item(1).Delete
    nothingtodelete:
        
    End Sub

    However, data in one column in CSV file has "-" sign before the number and when all that data is pasted to Excel Spreadsheet, it gets "=" before the already existing "-" and then the field gets error "#FIELD!".

    Why does it do that and how could I fix the formatting? When I simple mark, copy and paste, I have no issues, but with VBA code and QueryTables,Add I get an issue.

    I hope somebody can help me with that?

    Kind regards

    Gytis

    Thursday, January 17, 2019 1:58 PM