The class _bstr_t is a C++ wrapper class around the BSTR datatype, which is defined as WCHAR *. See for example What's the difference between BSTR and _bstr_t?. If you are doing C, then your reference to _bstr_t is probably incorrect and you should be asking for a conversion to BSTR instead.
The following lines of code can do that for you:
DWORD len;
BSTR bstrPtr;
char sheetName[25];
/* Construct the name of your sheet as a regular string */
sprintf(sheetName, "Sheet number %d", i);
/* Count the number of bytes in the WChar version of your name
by doing a dummy conversion */
len = MultiByteToWideChar(CP_ACP, 0, sheetName, -1, 0, 0);
/* Allocate the BSTR with this size */
bstrPtr = SysAllocStringLen(0, len);
/* Do the actual conversion of the sheetName into BSTR */
MultiByteToWideChar(CP_ACP, 0, sheetName, -1, bstrPtr, len);
/* Do your stuff... */
/* Deallocate the BSTR */
SysFreeString(bstrPtr);
If your reference to _bstr_t is correct and this code does not answer your question, then please post a snippet of the header files that you are using, showing the definition of the name
attribute. Also, the statement pSheet->Name = name
is not likely to set the name of an Excel sheet, since that typically involves invoking a function instead of plainly setting an attribute. Understanding that would require more context from you as well.