Excel vba - Query da Excel con nome variabile

Question

Buongiorno a tutti ,

ho preparato in un foglio nuovo di lavoro una connessione dati da Microsoft Query , seleziono i campi che mi servono e salvo la query, i dati vengono riportati correttamente,

Il tutto dietro ad un codice macro che poi prendo e ne sostituisco la parte fissa (percorso e nome file) con la parte variabile (il file excel di origine viene selezionato ogni volta da un utente tramite una dialogbox)

La mia intenzione sarebbe quella di ricreare tutte le volte dopo aver selezionato il file il codice della query nel foglio3 , cancellando prima i dati precedenti , ma da errore all’istruzione :   .ListObject.DisplayName = "Tabella_Query_da_Excel_Files83"

Probabilmente perché ricreando ogni volta questo nome cambia.

Del resto non posso creare la query e fare un semplice refresh perché il nome file può cambiare. 

Come posso gestire questa cosa secondo voi senza passare da ADO ?

Grazie

Answer 1

Ciao IL CREM,

Scusa ma mi sono espresso da schifo .

In sostanza devo sostituire ogni volta questo codice :

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _

"ODBC;DSN=Excel Files;DBQ=" & file_input _

), Array( _

"LS;DefaultDir=" & percorso_input & ";DriverId=1046;MaxBuf" _

), Array("ferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")). _

QueryTable

.CommandText = Array( _

"SELECT TUTTO.Nome1, TUTTO.Codice Agente, TUTTO.Cliente, TUTTO.Nome, TUTTO.Data documento, TUTTO.Documento originale, TUTTO.Saldo, TUTTO.Cond# pagam#, TUTTO.Md#pag#, TUTT" _

, _

"O.Scade il, TUTTO.Org# comm#" & Chr(13) & "" & Chr(10) & "FROM TUTTO TUTTO" & Chr(13) & "" & Chr(10) & "ORDER BY TUTTO.Nome1, TUTTO.Nome" _

)

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "Tabella_Query_da_Excel_Files83"

.Refresh BackgroundQuery:=False

End With

con le variabili percorso_input e file_input che arrivano dalla open dialog box

ma tempo che siccome prima cancello i dati nel foglio dove eseguo queste istruzioni mi cancella anche i riferimenti alla query e quindi di conseguenza il nome Tabella_Query_da_Excel_Files83 cambia. Quindi non riesco mai a fare un refresh 

Credo che il mio suggerimento sia sempre valido. Quindi, prova qualcosa del genere:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim percorso_input As String

    Dim file_input As String

    Dim ListOBJ As ListObject

    Const sNomeTabella As String = "MyTable"                           '<<=== Modifica

    Set ListOBJ = ActiveSheet.ListObjects.Add( _

    SourceType:=0, _

    Source:=Array(Array("ODBC;DSN=Excel Files;DBQ=" & file_input), _

    Array("LS;DefaultDir=" & percorso_input _

    & ";DriverId=1046;MaxBuf"), _

    Array("ferSize=2048;PageTimeout=5;")), _

    Destination:=Range("$A$1")).QueryTable

    With ListOBJ

        .CommandText = Array( _

                       "SELECT TUTTO.Nome1, TUTTO.Codice Agente, " _

                       & "TUTTO.Cliente, TUTTO.Nome, TUTTO.Data documento, " _

                       & "TUTTO.Documento originale, TUTTO.Saldo, TUTTO.Cond# pagam#, " _

                       & "TUTTO.Md#pag#, TUTTO.Scade il, TUTTO.Org# comm#" _

                       & Chr(13) & "" & Chr(10) & "FROM TUTTO TUTTO" & Chr(13) _

                       & "" & Chr(10) & "ORDER BY TUTTO.Nome1, TUTTO.Nome")

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

        .ListObject.DisplayName = sNomeTabella

        .Refresh BackgroundQuery:=False

    End With

End Sub

'<<=========

Ovviamente, devi aggiungere il tuo codice per assegnare i valori voluti alle tue variabili file_input e percorso_input.

===

Regards,

Norman

Answer 2

Scusa ma mi sono espresso da schifo .

In sostanza devo sostituire ogni volta questo codice :

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _

"ODBC;DSN=Excel Files;DBQ=" & file_input _

), Array( _

"LS;DefaultDir=" & percorso_input & ";DriverId=1046;MaxBuf" _

), Array("ferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")). _

QueryTable

.CommandText = Array( _

"SELECT TUTTO.Nome1, TUTTO.Codice Agente, TUTTO.Cliente, TUTTO.Nome, TUTTO.Data documento, TUTTO.Documento originale, TUTTO.Saldo, TUTTO.Cond# pagam#, TUTTO.Md#pag#, TUTT" _

, _

"O.Scade il, TUTTO.Org# comm#" & Chr(13) & "" & Chr(10) & "FROM TUTTO TUTTO" & Chr(13) & "" & Chr(10) & "ORDER BY TUTTO.Nome1, TUTTO.Nome" _

)

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "Tabella_Query_da_Excel_Files83"

.Refresh BackgroundQuery:=False

End With

con le variabili percorso_input e file_input che arrivano dalla open dialog box

ma tempo che siccome prima cancello i dati nel foglio dove eseguo queste istruzioni mi cancella anche i riferimenti alla query e quindi di conseguenza il nome Tabella_Query_da_Excel_Files83 cambia. Quindi non riesco mai a fare un refresh

Answer 3

CiaoIL CREM,

ho preparato in un foglio nuovo di lavoro una connessione dati da Microsoft Query , seleziono i campi che mi servono e salvo la query, i dati vengono riportati correttamente,

Il tutto dietro ad un codice macro che poi prendo e ne sostituisco la parte fissa (percorso e nome file) con la parte variabile (il file excel di origine viene selezionato ogni volta da un utente tramite una dialogbox)

La mia intenzione sarebbe quella di ricreare tutte le volte dopo aver selezionato il file il codice della query nel foglio3 , cancellando prima i dati precedenti , ma da errore all’istruzione :   .ListObject.DisplayName = "Tabella_Query_da_Excel_Files83"

Probabilmente perché ricreando ogni volta questo nome cambia.

Del resto non posso creare la query e fare un semplice refresh perché il nome file può cambiare. 

Come posso gestire questa cosa secondo voi senza passare da ADO ?

Prova un approccio del genere:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim SH As Worksheet

    Dim ListObj As ListObject

    Dim sDisplayName As String

    Set WB = ThisWorkbook

    Set SH = WB.Sheets("Foglio1")                     '<<==== Modifica

    With SH

        Set ListObj = .ListObjects(.ListObjects.Count)

    End With

    '\ Il nome della tabella non sarebbe di interesse per me dato che

    '\ posso usare la variabile ListObj per eseguire le azioni necessarie,

    '\ ma se ti piacerebbe sapere il nome:

    sDisplayName = ListObj.DisplayName

End Sub

'<<=========

===

Regards,

Norman