Query with return #num! for string columns in excel

0

I made a query in excel that when running it in VBA a result was generated. Among the generated columns, one that was text appeared with #num! in all values that were not empty before.

I think excel understood that column was numeric even though it was not, due to having empty values in the first columns (null).

This macro / query, serves several routines and I did not want to get caught in the code if only for this column, or for that template. Is there any way I can not allow this?

When I ran the query in access I was successful. But earlier, the same query returned the same error as excel. I can not figure it out.

code:

        strSQL = "SELECT * FROM " & "[Excel 12.0 Xml;HDR=YES;typeguessrows=0;DATABASE=" & ThisWorkbook.FullName & "].[" & WS14.Name & "$] as Cadastro WHERE " & strexcelcolumnsNotNullKey
        Set rs2 = New ADODB.Recordset
        strConnectString = "Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDiretorioPadrao & ";"
        Set objConnection = New ADODB.Connection
        rs2.Open strSQL, strConnectString
        With Sheets("sheet1")
            .Cells.ClearContents
            .Range("A" & 2).CopyFromRecordset rs2
        End With
            'Preencher o cabeçalho
            x = 0
            fim = 0
            For Each fld In rs2.Fields
                .Cells(1, 1 + x).Value = fld.Name
                fim = Application.Max(fim, .Cells(Cells.Rows.Count, 1 + x).End(xlUp).Row)
                x = x + 1 'tick iterator
            Next
        End With

XXXXXXXXXXXXXXXXXX

final strsql: SELECT * FROM [Excel 12.0 Xml; HDR = YES; typeguessrows = 0; DATABASE = M: \ Spreadsheets in Development \ Layout - Base - Development_V2_69.25.xlsm]. not null AND REGISTRATION [Strategy ID] is not null AND REGISTRATION [Cashflow ID] is not null AND REGISTRATION [Medium Term] is not null AND REGISTRATION [Days in Delay] is not null AND CADASTRO. [Price Type] is not null AND CADASTRO. [Financial BRL] is not null AND CADASTRO [Financial + 1 BRL] is not null AND CADASTRO. [Financial +2 BRL] is not null AND

Data: (many columns .. I'll put an example)

Date Location ID Strategy Strategy Counterpart Quantity Total Amount Free Amount Warranted Guarantee Price Type Average Term Days in Financial Delay BRL Financial +1 BRL Financial +2 BRL 1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
01/31/2017 54 Go to the moon Client Z 5000 1432 0 0.028 0 61.826,15 0.00 0.00 1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
01/31/2017 54 Go to the moon Client Z 5000 1432 0 0.00 0 0.00 0.00 0.00 1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
01/31/2017 54 Go to the moon Client Z 5000 1432 0 0.00 0 0.00 0.00 0.00 1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
01/31/2017 54 Go to the moon Client Z 5000 1432 0 0.00 0 0.00 0.00 0.00 1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
1/31/2017 54 Go to the moon Client Z 5000 1432 0 0
31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price A 0
01/31/2017 54 Go to the moon Client Z 5000 1432 0 Price B 0,00 0 0.00 0,00 0,00 31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price C 0 31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price D 0
31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price E 0 31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price A 0
01/31/2017 54 Go to the moon Client Z 5000 1432 0 Price B 0,00 0 0.00 0,00 0,00 31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price C 0 31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price D 0
31/01/2017 54 Go to the moon Client Z 5000 1432 0 Price E 0 01/31/2017 54 Go to the moon Client Z 5000 1432 0 Price A 0

    
asked by anonymous 04.07.2017 / 02:59

0 answers