开发者

Problem with Excel SQL query code when it reads a closed workbook

开发者 https://www.devze.com 2023-02-06 08:08 出处:网络
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.

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号