Neste artigo, buscaremos dados da pasta de trabalho fechada para a caixa de listagem no formulário do usuário usando o VBA.
Os dados brutos para este exemplo estão no intervalo A2: B10 na pasta de trabalho “23SampleData.xls”, que é colocada no caminho do arquivo “D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \”.
Criamos dois botões de comando na planilha principal para executar dois formulários de usuário diferentes. Cada botão de comando está vinculado a diferentes formulários de usuário.
Explicação lógica
Neste exemplo, duas maneiras diferentes são usadas para buscar dados da pasta de trabalho fechada. Estes são:-
-
Abra a pasta de trabalho fechada e obtenha os dados
-
Usando conexão ADODB
Abra a pasta de trabalho fechada e obtenha os dados
É possível definir a propriedade OrigemDaLinha de um controle ListBox para obter dados de outra pasta de trabalho atribuindo um valor à propriedade OrigemDaLinha da seguinte maneira:
‘[Filename.xls] Folha1?! $ B $ 1: $ B $ 15
O controle ListBox exibirá valores apenas se a outra pasta de trabalho estiver aberta.
Portanto, para buscar os dados da pasta de trabalho fechada, criaremos uma macro para abrir a outra pasta de trabalho sem que o usuário perceba e buscar dados da pasta de trabalho para adicionar itens na caixa de listagem e fechar a pasta de trabalho.
Clicar no botão “Selecionar” irá ativar o formulário do usuário “UserForm1”. O evento Initialize do formulário do usuário é usado para adicionar itens na caixa de listagem. Este evento primeiro abre a pasta de trabalho fechada e, em seguida, atribui o valor no intervalo à variante “ListItems”. Depois de atribuir o valor, a pasta de trabalho é fechada e os itens são adicionados à caixa de listagem.
A caixa de listagem é usada para selecionar o nome dos valores de lista existentes. Pressionar o botão “OK” exibirá o nome selecionado.
Usando conexão ADODB
ActiveX Data Objects (ADO) é uma interface de alto nível e fácil de usar para conexão OLE DB. É uma interface de programação para acessar e manipular dados em um banco de dados.
Para criar a conexão ADODB, precisaremos adicionar a biblioteca ADO ao projeto.
Para adicionar referência, escolha no menu Ferramentas> Referência.
Clicar no botão “Conexão ADODB” na planilha irá ativar o formulário de usuário “UFADODB”. No evento de inicialização deste formulário de usuário, usamos a conexão ADODB para buscar dados da pasta de trabalho fechada. Criamos uma função definida pelo usuário (UDF) personalizada “ReadDataFromWorkbook” para estabelecer a conexão e buscar os dados da pasta de trabalho fechada para a matriz.
Usamos outro UDF “FillListBox” para adicionar itens na caixa de listagem durante a inicialização do formulário do usuário. A caixa de listagem exibirá os dados em duas colunas, uma coluna contém o nome e a segunda coluna contém a idade.
Pressionar o botão “OK” após selecionar o item na caixa Lista exibirá a mensagem de informação sobre o item selecionado.
Siga abaixo para o código
Option Explicit Sub running () UserForm1.Show End Sub ADODBrunning () UFADODB.Show End Sub 'Adicionar código abaixo no formulário de usuário UFADODB Option Explicit Private Sub CommandButton1_Click () Dim name1 As String Dim age1 As Integer Dim i As Integer' Atribuir o selecionado valor na caixa de listagem para a variável nome1 e idade1 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected (i) Then name1 = ListBox1.Value age1 = ListBox1.List (ListBox1.ListIndex, 1) Exit For End If Next ' Descarregue o formulário do usuário Unload Me 'Exibindo a saída MsgBox "Você selecionou" & name1 & ". Sua idade é" & age1 & "yrs." End Sub Private Sub UserForm_Initialize () 'Preenchendo ListBox1 com dados de uma pasta de trabalho fechada Dim tArray As Variant' Chamando a função ReadDataFromWorkbook para obter dados do intervalo especificado para a matriz 'Altere o caminho de acordo com sua necessidade, "Sample_data" é nomeado intervalo definido tArray = ReadDataFromWorkbook ("D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \ 23SampleData.xls", "Sample_Data") 'Função de chamada FillListBox para adicionar itens na List Box' Atribuir objeto List box e tarray como parâmetro FillListBox Me .ListBox1, tArray 'Liberando variáveis de array e desalocando a memória usada para seus elementos. Apagar tArray End Sub Sub Private Sub FillListBox (lb As MSForms.ListBox, RecordSetArray As Variant) 'Preenchendo a caixa de listagem lb com dados de RecordSetArray Dim r As Long, c As Long With lb .Clear' Atribuindo valor à caixa de listagem Para r = LBound (RecordSetArray , 2) To UBound (RecordSetArray, 2) .AddItem For c = LBound (RecordSetArray, 1) To UBound (RecordSetArray, 1) .List (r, c) = RecordSetArray (c, r) Next c Next r 'Não selecionar nenhum item na caixa de listagem por padrão .ListIndex = -1 End With End Sub Função privada ReadDataFromWorkbook (SourceFile As String, _ SourceRange As String) As Variant 'requer uma referência à biblioteca Microsoft ActiveX Data Objects' (menu Ferramentas> Referências no VBE ) Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String 'Declarar uma string de conexão e o driver requer para estabelecer a conexão dbConnectionString = "DRIVER = {Driver do Microsoft Excel (* .xls)}; Somente leitura = 1; DBQ = "& SourceFile 'Criando uma nova conexão ADODB Definir dbConnection = Novo ADODB.Connection On Error GoTo InvalidInput 'Abra a conexão de banco de dados dbConnection.Open dbConnectionString' Obtendo o conjunto de registros do intervalo nomeado definido Set rs = dbConnection.Execute ("[" & SourceRange & "]") On Error GoTo 0 'Retorna um dois array dimensional com todos os registros em rs ReadDataFromWorkbook = rs.GetRows 'Fechar o conjunto de registros e a conexão do banco de dados rs.Close dbConnection.Close Set rs = Nothing Set dbConnection = Nothing Exit Function' Código para lidar com o erro InvalidInput: MsgBox "O arquivo de origem ou intervalo de origem é inválido! ", _ vbExclamation," Obter dados da pasta de trabalho fechada "End Function 'Adicionar o código abaixo em UserForm1 Opção Explicit Private Sub CommandButton1_Click () Dim name1 As String Dim i As Integer' Atribuir o valor selecionado à variável name1 Para i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected (i) Then name1 = ListBox1.Value Exit For End If Next 'Unload the userform Unload Me' Exibe o nome selecionado MsgBox "Você selecionou" & name1 & "." End Sub Private Sub UserForm_Initialize () Dim ListItems As Variant, i As Integer Dim SourceWB As Workbook 'Desativando as atualizações de tela Application.ScreenUpdating = False With Me.ListBox1' Remover entradas existentes da caixa de listagem .Clear 'Abrir a pasta de trabalho de origem como ReadOnly Set SourceWB = Workbooks.Open ("D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \ 23SampleData.xls", _ False, True) 'Obtenha o intervalo de valores que deseja ListItems = SourceWB.Worksheets (1 ) .Range ("A2: A10"). Value 'Fechar a pasta de trabalho de origem sem salvar as alterações SourceWB.Close False Set SourceWB = Nothing Application.ScreenUpdating = True' Converter valores para uma matriz vertical ListItems = Application.WorksheetFunction.Transpose (ListItems) For i = 1 To UBound (ListItems) 'Preencher a caixa de listagem .AddItem ListItems (i) Next i' Selecionando nenhum item por padrão, defina como 0 para selecionar o primeiro item .ListIndex = -1 End With End Sub
Se você gostou deste blog, compartilhe com seus amigos no Facebook. Além disso, você pode nos seguir no Twitter e no Facebook.
Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail