Using Replace function when Querying Workbook/sheets ADODB (ACEODBC driver)

[Shreedhar]

Hi Gurus, Need suggestion on alternative to REPLACE function, ODBC excel driver errors
Undefined function REPLACE when it is used in a SQL QUERY statement.

Can anyone suggest any alternative to this issue, also what are the valid functions that are supported in sql statements.

Thanks for your time.

Regards, Shreedhar

 

[Chihiro]

ODBC has limited set of functions that you can use depending on engine used. Can't recall if there was comprehensive list for querying Excel.

Use "Case When x Then y Else z End" type of statement.

 

[p45cal]

Let's have a look at some of the code you're using and where it errors.
If you're trying to use the vba REPLACE function directly in a SQL query I cant see it working, but with the right combination of quote marks and variables it's usually possible to create a SQL query on the fly.

 

[Shreedhar]

Let's have a look at some of the code you're using and where it errors.
If you're trying to use the vba REPLACE function directly in a SQL query I cant see it working, but with the right combination of quote marks and variables it's usually possible to create a SQL query on the fly.

I use the REPLACE function in the SQL query itself, My earlier workaround was to using replace range on the cell ranges after the RecordSet is published.

*** Using Single quote instead of double solves the issue. ***

e.g. code
Conn.Open sconnect

sSQLSting = "SELECT replace([TOPPATH], '/', '%') as TOPPATH1, ASSIGNMENT_NAME From [Sheet1$]"


Thanks

 

[Shreedhar]

ODBC has limited set of functions that you can use depending on engine used. Can't recall if there was comprehensive list for querying Excel.

Use "Case When x Then y Else z End" type of statement.

Thanks Chihiro...

*** Using Single quote instead of double solves the issue. ***

e.g. code
Conn.Open sconnect

sSQLSting = "SELECT replace([TOPPATH], '/', '%') as TOPPATH1, ASSIGNMENT_NAME From [Sheet1$]"

I use the REPLACE function in the SQL query itself, My earlier workaround was to using replace range on the cell ranges after the RecordSet is published.