Change from static values to parameters

[spowel4]

I have this query as part of a stored procedure, and this query as it is listed here works:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 8.0;Database=G:\KF\GBSData.xls;',
    'SELECT * FROM [Sheet1$]')
                SELECT * FROM [KWF_GBSData].[dbo].[tblKWF_WorkTable2]
                WHERE invoiceNumber IN (
                                SELECT invoiceNumber 
                                FROM [KWF_GBSData].[dbo].[tblKWF_WorkTable2]
                                WHERE stockNumberShipped LIKE 'CCCOMBO%'
                )

I would like to change the 'SELECT * FROM [Sheet1$]' to something a little more dynamic so that I can use a parameter for the Excel worksheet name rather than it always using Sheet1$. I would also like to use a parameter for the WHERE stockNumberShipped LIKE 'CCCOMBO%' so that I can pass in the stockNumberShipped value. Simply put, I want to pass in the worksheet name and stock number via parameters.

I have tried this but it keeps giving syntax errors:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 8.0;Database=G:\KF\GBSData.xls;',
    'SELECT * FROM [@pWorksheetName$]')
                SELECT * FROM [KWF_GBSData].[dbo].[tblKWF_WorkTable2]
                WHERE invoiceNumber IN (
                                SELECT invoiceNumber 
                                FROM [KWF_GBSData].[dbo].[tblKWF_WorkTable2]
                                WHERE stockNumberShipped LIKE @pItemNumber%
                )

I have also tried different variations on that, it just keeps giving syntax errors. So I am asking for hints & suggestions, if anyone can help.

[LastMitch]

I have tried this but it keeps giving syntax errors:

What is the error? I don't see any issue with you query.

Maybe the error message will make more sense if you have post it.

[spowel4]

Here is the exact error message:

Msg 7357, Level 16, State 2, Procedure sp_KWFGBS_ExportToExcel, Line 29
Cannot process the object "SELECT * FROM [@pWorksheetName$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

[LastMitch]

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

What is your table struture? Apparently, you missing an column or that column doesn't exist.

I don't think it's a permission issue but maybe it is but not sure because you haven't answer my question about your table struture.

This is the query from your first statement:

SELECT * FROM [Sheet1$]

This is the query from your second statementL:

SELECT * FROM [@pWorksheetName$]

Is there a table called : pWorksheetName and also columns in that table?

[askme_1]

Cannot process the object "SELECT * FROM [sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

am geting dis type of errors in sql wat should i do