OPENROWSET - How to assign a variable instead of the file name to open?

Question

I do this command and I do it right:

SELECT * 
FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=\\00.000.00.0000\Arquivos\TI\Arquivos\252203408.xlsx;HDR=YES',
    'SELECT * FROM [Rateio$]');

How can I put a variable in place of the file '252203408.xlsx' and that I can use openrowset normally?

NOTE: I wish to know this to make this opening several files of a directory.

Answer 1

Use the CONCAT; you would have something like:

declare @nome_arquivo varchar(255)
select @nome_arquivo = '252203408.xlsx'

SELECT * 
FROM OPENROWSET( 
    'Microsoft.ACE.OLEDB.12.0', 
    CONCAT('Excel 12.0 Xml;Database=\\00.000.00.0000\Arquivos\TI\Arquivos\', @nome_arquivo, ';HDR=YES'),
    'SELECT * FROM [Rateio$]');

---

edited

I found that answer which seems to suit what you quoted in the comment. Take a look at how I set up the query:

Declare @nome_arquivo varchar(255)
Declare @sql nvarchar(max)

Set @nome_arquivo = '252203408.xlsx'
Set @sql='SELECT * 
FROM OPENROWSET(
               ''Microsoft.ACE.OLEDB.12.0'',
               ''Excel 12.0 Xml;Database=\\00.000.00.0000\Arquivos\TI\Arquivos\' + @nome_arquivo + ';HDR=YES;'',
               ''SELECT * FROM [Rateio$]'')'

-- Print @sql
Exec(@sql)