I'm getting a very peculiar result when using string variables to build an SQL string to retrieve a recordset from a spreadsheet....

If I use a variable or activesheet.name then I get a "Not a valid name " error, now if I hard code the active sheet's name ie. "X103878$" it works fine and returns the record set OK.

I've declared the variable beforehand as a string I've even tried it as a variant.

code

sqlstring = "SELECT * FROM [" & Ref & "$] " & _
"WHERE [" & Ref & "$!customer]='" & Customer & "'"

PreferencedRows.Open sqlstring, adodbConnection, adOpenDynamic, adLockOptimistic

/code

The problem occurs with the "Ref" variable, if I assign a literal string to it, the code is fine, but if I assign activesheet.name to it, the code doesn't work, even though the names are identical ??
Any help would be appreciated, thanks in advance.
PS our company doesn't use Access and I have read-only authorisation on the main company database so I use Excel as a local data source, it makes my life easier(ish)