PROCV Excel - Connection Usage

Question

PROCV Excel - Connection Usage

Navigation

#1 by (1 votes)

1

Is it possible to use connections with other worksheets in a PROCV?

Ex:

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

excel

asked by anonymous 25.09.2017 / 22:12

1 answer

Help with simply linked list of java Display div after sending emails via POST Ajax PHP

score 1 · Accepted Answer

Since you are using connections, I suggest using Microsoft Query as follows:

I have a worksheet (data.xlsx) with your data, eg:

Inyourresultsheet(result.xlsx),whereyouwantyoursearchtoappear,dothefollowingsteps:

EnteraQueryasbelow:

SelecttheExcelFilesoptionandclick'Ok':

Selectyourdatafile:

Ifthetab/spreadsheetnamedoesnotappear,clickOptionsandcheckthe"System Tables" option. Select the worksheet and click the right arrow, as shown in the image below:

Afterclicking"Next", select the column to be filtered and select the text / number for the filter, clicking "Next" will be able to sort your result by a certain column:

Advancingagainyoucanchoosewhichworksheetandwhichcelltoputthedatain:

"Ok" and your data will be on your result sheet. When you insert a new data into your Data worksheet, go to your Results worksheet and click Refresh to search for new data:

Toautomatethisprocess,youcantryto"save" a macro and then edit to meet your demand by creating new automated connections.

EDITION 1

To enter a criterion you could use the following function ( Sub ) 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 put all the variables (FILE, PATH, COLUMNS, CRITERION, DESTINATION etc) dynamically, to have a more complete function.

I hope I have helped!