PROCV Excel - Use of connections

Question

Connections with other worksheets can be used in a PROCV ?

Ex:

I added a connection to another worksheet I use. There are columns. I can apply a PROCV or some other formula to consume data ?

Answer 1

Since you’re using connections, I suggest you use the Microsoft Query, as follows:

I have a spreadsheet (.xlsx data) with your data, example:

In your result sheet (result.xlsx), where you want your search to appear do the following steps:

1. Insert a Query as below:

2. Select the option Excel Files and click 'OK':

3. Select your data file:

4. If the name of the tab/sheet does not appear, click on Options and check the option "System Tables". Select the spreadsheet and click the right arrow as sequence in the image below:

5. After clicking on "Next", select the column that should be filtered and select the text/number for the filter, clicking on "Next" will have the possibility to sort your result by certain column:

6. Moving forward again you will be able to choose which sheet and in which cell to put the data:

8. "OK" and your data will be in your result sheet. When entering a new data into your Data sheet, go to your Results sheet and click Update to search for new data:

To automate this process, you can try to "write" a macro and then edit to meet your demand by creating new automated connections.

EDITION 1

To enter a criterion could use the following function (Sub) down below:

Sub busca_dados_externos()

Dim CRITERIO As String

    CRITERIO = Range("A2").Text

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\Users\m362168\OneDrive\Projetos\Budhi\Excel\Query\dados.xlsx;DefaultDir=C:\Users\m362168\OneDrive\Projet" _
        ), Array("os\Budhi\Excel\Query;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;") _
        ), Destination:=Range("$B$2")).QueryTable
    .CommandText = Array( _
        "SELECT `Plan1$`.teste, `Plan1$`.descricao, `Plan1$`.valor" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\m362168\OneDrive\Projetos\Budhi\Excel\Query\dados.xlsx`.`Plan1$` `Plan1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Plan1$`.teste='" & CRITERIO & "')" & Chr(13) & "" & Chr(10) & "ORDER BY `Plan1$`.d" _
        , "escricao, `Plan1$`.valor")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabela_Consulta_de_Excel_Files_1"
        .Refresh BackgroundQuery:=False
    End With
    Range("C10").Select
End Sub

Of course you can place all variables (FILE, PATH, COLUMNS, CRITERIA, DESTINATION etc) dynamically, to have a more complete function.

I hope I’ve helped!