Listando todas as planilhas de uma pasta de trabalho sem abrir o arquivo

Cenário: O usuário seleciona uma pasta de trabalho e precisamos importar alguma das planilhas contidas nela

Além disto,ele também precisa selecionar quais são as planilhas disponíveis.

Opção 1: Abrir a pasta de trabalho, percorrer todas as planilhas e listá-las em algum controle para que ele possa realizar a seleção.

Opção 2: Realizar a leitura de todas as planilhas que estão naquela pasta através do ADO e listá-las no mesmo controle.

A opção 2 será o assunto deste tópico, ela é muito mais rápida que a primeira opção, uma grande vantagem em relação a primeira.

Para isto, construí uma Function que retorna uma string separada por ";", mas cada um pode adaptar ao seu gosto, podendo inclusive retornas arrays ou collections.

Vamos ao código :

Function ListAllSheetsInWb(wbname As String)
'officevb.com
'Listando as planilhas numa pasta de trabalho com o ADO

Dim cn As Object
Dim rs As Object
Dim strCn As String, TmpStr As String

'create objects
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'String connection
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wbname & ";Extended Properties=Excel 8.0"

'open connection
cn.Open strCn

'open SchemaTables of Excel File
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, Empty))

'While exist Sheets
Do While Not rs.EOF
    If InStr(1, rs("Table_name"), "$") > 0 And InStr(1, rs("Table_name"), "FilterDatabase") = 0 Then
       TmpStr = TmpStr & Replace(rs.Fields("table_name").Value, "$", "") & ";"
    End If
   rs.MoveNext
Loop

TmpStr = Replace(TmpStr, "'", "")

'return a string
ListAllSheetsInWb = Left(TmpStr, Len(TmpStr) - 1)

Set cn = Nothing

End Function

Para utilizá-lo é bem simples basta realizar a chamada da function:

Dim Plans as variant, i as integer

Plans=split(ListAllSheetsInWorkbook("\\path\of\your\file"),";")

for i=0 to ubound(Plans)-1
     yourComboBox.AddItem plans(i)
next i

`

Desta maneira podemos facilmente oferecer as opções para a importação dos dados pelo usuário, selecionando a planilha que devemos utilizar na importação.