Pass a variable

[suoirotciv]

I need to loop to lots of excel file so i need to pass the file name. But using SET @FileName = 'C:\MyExcelFile.xls' and pass it on the code below is not working.

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyExcelFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

My Code

SET @FileName = 'C:\MyExcelFile.xls'

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database='+ @FileName + ';HDR=YES', 'SELECT * FROM [Sheet1$]')

========================================
I kept my Job because of TEK-TIPS
Thanks a lot to all who keeps on helping others.

 

[suoirotciv]

I got it, its not possible to pass variable to OPENROWSET so I just use EXEC

But my problem now is that my excel file contains phone number and when the data was transfered to my table the phone number becomes like this "3.9522e+006" where my field is already varchar

========================================
I kept my Job because of TEK-TIPS
Thanks a lot to all who keeps on helping others.

 

[simian101]

Change the column to a text field.

Simi