0

I am getting the error in the title when trying to update a row in Excel with blank values in these positions [BI6:DP6].

I have counted and it appears there are 60 spots in these cells to update, so I am stuck.

Any ideas? String to update is below:

UPDATE [My Sheet$BI6:DP6] SET F1=1, F2=0, F3=0, F4=0, F5=0, F6=1, F7=0, F8=1, F9=1, F10=1, F11=1, F12=1, F13=0, F14=0, F15=1, F16=1, F17=1, F18=1, F19=1, F20=1, F21=1, F22=0, F23=0, F24=0, F25=0, F26=0, F27=0, F28=1, F29=1, F30=0, F31=0, F32=0, F33=1, F34=0, F35=1, F36=1, F37=1, F38=1, F39=0, F40=0, F41=0, F42=1, F43=1, F44=0, F45=1, F46=0, F47=0, F48=1, F49=0, F50=0, F51=0, F52=1, F53=1, F54=0, F55=1, F56=0, F57=0, F58=1, F59=0, F60=0
Techgration
  • 516
  • 4
  • 16

1 Answers1

0

I was using an update statement for a macro enabled spreadsheet, which required us to use the 'HDR=NO' value for Extended Properties attribute of the spreadsheet. Along with that, every cell in the spreadsheet needed to be formatted to "Text" instead of "General" (or numeric, currency) in this case in order to get the values into the cells correctly.

Excel apparently attempts to covert these values with little to no information returning back upon error in the OLEDB connection, and so it makes this issue very difficult to fix.

Techgration
  • 516
  • 4
  • 16