You can use below code to get sheet_name from excel document.

DECLARE @tempdata TABLE 
(
   TABLE_CAT varchar(50),
   TABLE_SCHEMA varchar(50),
   TABLE_NAME varchar(50),
   TABLE_TYPE varchar(50),
   TABLE_REMARKS varchar(50)
);
declare @sheet_name nvarchar(100);
declare @linkedServerName sysname = 'TempExcelSpreadsheet'
declare @excelFileUrl nvarchar(1000) = 'D:\opt\Test\Test.xlsx'

if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

exec sp_addlinkedserver
    @server = @linkedServerName,
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = @excelFileUrl,
    @provstr = 'Excel 12.0;HDR=Yes'

declare @suser_sname nvarchar(256) = suser_sname()

exec sp_addlinkedsrvlogin
    @rmtsrvname = @linkedServerName,
    @useself = 'false',
    @locallogin = @suser_sname,
    @rmtuser = null,
    @rmtpassword = null

INSERT INTO @tempdata
exec sp_tables_ex @linkedServerName;

SET @sheet_name=(select top 1 TABLE_NAME from @tempdata)
-- Remove temp linked server
if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

After that you can write your code and use variable '@sheet_name' instead of '[Sales Register$]'.

I needed to find all of the names of Excel tabs from a list of Excel files in a directory rather than just one worksheet.

I used value statements to insert my filenames into a temp table. The filename in this table had to have the full directory path included.

I used the answers in this thread to programmatically create that linked server and combined it with this cursor example answer to create my code:

https://stackoverflow.com/a/5856192

This was the code I came up with:

declare @fnTemp Table (filename varchar(200));
insert into @fnTemp
select * from 

(values
('2015 FileName1 Survey.xlsx'), 
('2016 Filename2 Survey.xlsx'), 
('2017 Filename3 Survey.xlsx'), 
('2018 FileName4 Survey.xlsx'), 
('2019 Special Survey.xlsx'),
('Sample1.xlsx')) FN([FileName] )
;
--Select * from @fnTemp;

DECLARE @tempdata TABLE 
(
   TABLE_CAT varchar(100),
   TABLE_SCHEMA varchar(50),
   TABLE_NAME varchar(50),
   TABLE_TYPE varchar(50),
  TABLE_REMARKS varchar(50)
);

declare @sheet_name nvarchar(100);
declare @linkedServerName sysname = 'TempExcelSpreadsheet';
declare @sourcepath nvarchar(100)='C:\DroppedFiles\';
declare @fullUrl nvarchar(2000);

declare files Cursor Read_Only for select [FileName] from @fnTemp

declare @excelFileUrl nvarchar(1000)
declare @suser_sname nvarchar(256) = suser_sname()

open files
fetch next from files into @excelFileUrl
while (@@FETCH_STATUS = 0)
begin

if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

Set @fullUrl=@sourcepath + @excelFileUrl

exec sp_addlinkedserver
    @server = @linkedServerName,
    @srvproduct = 'ACE 16.0',
    @provider = 'Microsoft.ACE.OLEDB.16.0',
    @datasrc = @fullUrl,
    @provstr = 'Excel 12.0;HDR=Yes'

exec sp_addlinkedsrvlogin
    @rmtsrvname = @linkedServerName,
    @useself = 'false',
    @locallogin = @suser_sname,
    @rmtuser = null,
    @rmtpassword = null

INSERT INTO @tempdata
exec sp_tables_ex @linkedServerName;

Update @tempdata set TABLE_CAT = @excelFileUrl where TABLE_CAT is null;
Delete @tempdata where TABLE_NAME like '%Print_titles'

--SET @sheet_name=(select top 1 TABLE_NAME from @tempdata)
-- Remove temp linked server
if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

Fetch next from files into @excelFileUrl

end
close files
deallocate files

select Table_Name, Table_Cat from @tempdata