Dim DSData As New DataSet
Dim SQLConn As New SqlClient.SqlConnection(ConnectionString)
Dim SqlCommand1 As New SqlClient.SqlCommand(SQLData, SQLConn)
Dim DAData As New SqlClient.SqlDataAdapter
DAData.Fill(DSData)
?????
xlWs.Range("A7").CopyFromRecordset(rs)

if object_id('tempdb..#t','U') is not null drop table #t
create table #t (dir nvarchar(255))
declare @dir nvarchar(255)='\\S1\Users\'
insert into #t exec xp_subdirs @dir
SELECT row_number() over(order by dir) as no, dir , @dir+dir path from #t
drop table #t

Try
rs = conn.Execute(StrSQLSelect)
Catch ex As Exception
      MsgBox(ex.Message)
End Try

If rs.eof Then      --Ошибка - Операция не допускается, если объект закрыт.
    rs.close() : conn.close() : Exit Sub
Else
    StrSQLSelect = rs(0).value
End If

conn.Open("Provider=SQLOLEDB;" + ConnectionString)