How to set an array value using sp_OASetProperty

Question

I have a stored procedure that manipulates some data in an excel spreadsheet. I am importing data by creating a QueryTable object. I would like to set TextFileColumnDataTypes property for the object but I can't figure out how to pass in an array to the sp_OASetProperty routine.

This is what my code looks like but obviously it doesn't work:

EXECUTE @rc = master..sp_OASetProperty @xlQueryTable, 'TextFileColumnDataTypes', 'Array(xlTextFormat, xlTextFormat, xlGeneralFormat)'

Is there any way to do this?

Answer 1

Where do the values for xlTextFormat and xlGeneralFormat come from? Is it possible this should be one of the following:

DECLARE @command VARCHAR(255);

SET @command = 'Array(' + @xlTextFormat + ', ' + @xlTextFormat + ', ' + @xlGeneralFormat + ')';
-- or
SET @command = 'Array("' + @xlTextFormat + '", "' + @xlTextFormat + '", "' + @xlGeneralFormat + '")';
-- or
SET @command = 'Array("xlTextFormat", "xlTextFormat", "xlGeneralFormat")';

EXEC @rc = master..sp_OASetProperty @xlQueryTable, 'TextFileColumnDataTypes', @command;

Otherwise maybe you should ditch sp_OA and use CLR.