variables in SQL string-building problem
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.
sqlstring = "SELECT * FROM [" & Ref & "$] " & _
"WHERE [" & Ref & "$!customer]='" & Customer & "'"
PreferencedRows.Open sqlstring, adodbConnection, adOpenDynamic, adLockOptimistic
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)