Problem with Excel SQL query code when it reads a closed workbook
Hi I use the following code (part shown) which works great. Basically I run a SQL query from excel which queries a closed workbook, extracts the info and puts it on the current opened sheet.
The problem is that one of the fields ("To") for some reason it always returns a value of nothing, even if every cell in that column has in fact information so I am not sure why it doesn't get the value. All other columns are read perfectly. Anyway knows why and whether there are some formatting (or other) specifics which affects SQL queries of this type?. There are no errors...simply the value is always nothing.
some code...
objRecordset.开发者_运维问答Open "SELECT * FROM [FILESAVED$] , objConnection, adOpenStatic, adLockOptimistic, adCmdText
for next_row=1 to objRecordset.count
Range("AF" & next_row) = objRecordset.Fields.Item("Validto")
Range("AG" & next_row) = objRecordset.Fields.Item("To")
Range("AO" & next_row) = objRecordset.Fields.Item("User Name")
objRecordset.movenext
next next_row
...more code
Thanks in advance
What kind of data is in the column? When you treat an Excel workbook as a database table, Excel infers the column type by matching it to whatever it infers about the majority of entries. (So if 60% look like numbers and 40% like text, it will treat them all as numbers.) This can cause errors, though it doesn't precisely match what you describe.
精彩评论