1

I'm trying to use adodb to query excel files. But some files have nonstandard sheet/column name. 

strQry = "select * from [ sheet1$A1:A50]"

I got an error saying "Invalid bracketing of name". How do I run query on sheet that has an extra space in its name? These are raw files from clients so I don't want to change the name or anything.

Also, I have another problem regarding the column.

strQry2 = "select [column\rA] from [sheet1$E1:E122]"

"\r" is a line break. I got an error "No value given for one or more required parameters."

Any advice on how to deal with these issues?

UPDATE:

Sheet 1 works fine but the sheet I have is named something like Sheet1 (extra space before sheet1.

Some of the column headers have a line break/carriage return within column name, like "column" & vbcrlf & "name". Is there a way to query these?

bananaLeaf
  • 37
  • 1
  • 6
  • what do want to do by saying `[column\rA]`? You can't select `"Column A"` from `E1:E122`...You would use the `WHERE` clause to limit the results like demonstrated in [**THIS ANSWER**](http://stackoverflow.com/a/18640226/2140173) –  Jul 03 '14 at 14:07
  • It's just there's a line break in the column headers. And yes, select from $E1:E122 works. thanks a lot. @me how – bananaLeaf Jul 03 '14 at 14:43

1 Answers1

2

As far as the space goes if Sheet 1 has a space between Sheet and 1 use

"select * from [Sheet 1$A1:A50]"

If there is a space in front of the sheet 1 aka. Chr(32) & Sheet 1 then it's impossible to select anything from that sheet using the [] syntax

however, if you don't want to change the original name of the spreadsheet you can create a temporary named range for the Range you want to pull the data from.

For example:

' add a temporary name
ThisWorkbook.Names.Add name:="tmp", RefersTo:=Sheets(" Sheet 1").Range("A1:C4")

' create your sql including the named range
sql = "SELECT * FROM tmp"

' open recordset
rs.Open sql, cn, adOpenUnspecified, adLockUnspecified

' remove the temporary name
ThisWorkbook.Names.Item("tmp").Delete

' copy rs to spreadsheet
ActiveSheet.Range("F2").CopyFromRecordset rs

The second question I don't understand so can you elaborate and I will update the answer?

  • "sheet 1" works fine but the sheet I have is named something like " sheet1". It has a blank space in front of "sheet1". Some of the column headers have a line break/carriage return within column name, like "column" & vbcrlf & "name". Is there a way to query these? thanks a lot – bananaLeaf Jul 03 '14 at 14:31