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