confronto (doppio) elenchi in VBA
draleo
semplici non sono.
Dovrei fare 2 confronti su due elenchi di nominativi:
Sul foglio1(“esatti”) ho dei dati disposti nelle seguenti
colonne:CodFisc,cognome,nome,comunenasc,data nasc
Sul foglio2(“errati”) ho la stessa disposizione delle colonne, ma i dati
possono presentare delle diversità
Dovrei fare 2 confronti:
1° confronto): estrarre dal foglio (“errati”) tutti coloro che:
hanno lo stesso cognome,nome,comune,datanasc ma differiscono dal
Foglio(“esatti”) per il CodFisc .
e dovrei riportare sul Foglio3, dalla colonna A alla col E , tutta la riga
dei dati così estratti.
Inoltre sempre nel Foglio3 ,nella colonna F, dovrei riportare il CodFisc
corrispondente, proveniente dal Foglio (“esatti”)
2° confronto): estrarre dal foglio (“errati”) tutti coloro che:
hanno il codFisc uguale aquello del Foglio1 ("esatti"), ma differiscono per
qualcuno degli altri dati (o cognome o nome o comune o datanascita)
e dovrei riportare sul Foglio4, dalla colonna A alla col E , tutta la riga
dei dati così estratti.
Inoltre sempre nel Foglio4 ,dalla colonna F alla colonna J, dovrei riportare
tuuta la riga corrispondente proveniente dal Foglio (“esatti”)
grazie mille
draleo
Norman Jones
=============
[..]
Inoltre sempre nel Foglio4 ,dalla colonna F alla colonna J, dovrei riportare
tuuta la riga corrispondente proveniente dal Foglio (“esatti”)
=============
Se ci fossero piu' di una riga per cui il
codFisc fossero uguali con una differenza
degli altr dati, che cosa dovrebbe
accadere ?
---
Regards.
Norman>
draleo
"Norman Jones" ha scritto:
Si mettono anche queste righe in più
Grazie
draleo
Maurizio Borrelli
news:96193675-E593-41E3...@microsoft.com:
Ciao draleo.
Un modo, (se ho capito tutto giusto e scritto tutto giusto!):
Option Explicit
Public Sub Test()
Dim strCnn As String
Dim sql As Variant
Dim i As Long
Dim wsh As Excel.Worksheet
Dim qry As Excel.QueryTable
With ThisWorkbook
strCnn = "ODBC" _
& ";Driver={Microsoft Excel Driver (*.xls)}" _
& ";DefaultDir=" & .Path _
& ";DBQ=" & .FullName _
& ";"
End With
sql = Array(Sql1, Sql2)
For i = LBound(sql) To UBound(sql)
Set wsh = ThisWorkbook.Worksheets.Add
With wsh
Set qry = .QueryTables.Add(strCnn, .Range("A1"))
End With
With qry
.CommandText = sql(i)
.Refresh
End With
Next
Set qry = Nothing
Set wsh = Nothing
End Sub
Private Function Sql1() As String
Dim sql As String
sql = "SELECT"
sql = sql & " TS.CodFisc AS tsCodFisc"
sql = sql & ", TS.cognome AS tsCognome"
sql = sql & ", TS.nome AS tsNome"
sql = sql & ", TS.comunenasc AS tsComuneNasc"
sql = sql & ", TS.`data nasc` AS tsDataNasc"
sql = sql & ", TG.CodFisc AS tgCodFisc"
sql = sql & " FROM"
sql = sql & " `esatti$` TG"
sql = sql & ", `errati$` TS"
sql = sql & " WHERE"
sql = sql & " TS.CodFisc<>TG.CodFisc"
sql = sql & " AND"
sql = sql & " '|'+TS.cognome" _
& "+'|'+TS.nome" _
& "+'|'+TS.comunenasc" _
& "+'|'+format(TS.`data nasc`,'yyyy-mm-dd')" _
& "+'|'"
sql = sql & "='|'+TG.cognome" _
& "+'|'+TG.nome" _
& "+'|'+TG.comunenasc" _
& "+'|'+format(TG.`data nasc`,'yyyy-mm-dd')" _
& "+'|'"
Sql1 = sql
End Function
Private Function Sql2() As String
Dim sql As String
sql = "SELECT"
sql = sql & " TS.CodFisc AS tsCodFisc"
sql = sql & ", TS.cognome AS tsCognome"
sql = sql & ", TS.nome AS tsNome"
sql = sql & ", TS.comunenasc AS tsComuneNasc"
sql = sql & ", TS.`data nasc` AS tsDataNasc"
sql = sql & ", TG.CodFisc AS tgCodFisc"
sql = sql & ", TG.cognome AS tgCognome"
sql = sql & ", TG.nome AS tgNome"
sql = sql & ", TG.comunenasc AS tgComuneNasc"
sql = sql & ", TG.`data nasc` AS tgDataNasc"
sql = sql & " FROM"
sql = sql & " `esatti$` TG"
sql = sql & ", `errati$` TS"
sql = sql & " WHERE"
sql = sql & " TS.CodFisc=TG.CodFisc"
sql = sql & " AND"
sql = sql & " '|'+TS.cognome" _
& "+'|'+TS.nome" _
& "+'|'+TS.comunenasc" _
& "+'|'+format(TS.`data nasc`,'yyyy-mm-dd')" _
& "+'|'"
sql = sql & "<>"
sql = sql & " '|'+TG.cognome" _
& "+'|'+TG.nome" _
& "+'|'+TG.comunenasc" _
& "+'|'+format(TG.`data nasc`,'yyyy-mm-dd')" _
& "+'|'"
Sql2 = sql
End Function
(Facci sapere se e eventualmente come hai risolto. Grazie!)
--
Maurizio Borrelli [Microsoft MVP Office System]
http://www.riolab.org/
draleo
errore generale di ODBC
in
With qry
.CommandText = sql(i)
.Refresh
End With
alla riga
.Refresh
"Maurizio Borrelli" ha scritto:
Le domande da fare sarebbero tante, ma
per il momento accontentiamoci di farla funzionare
Mi da infatti errore di run time 1004
errore generale di ODBC
in
With qry
.CommandText = sql(i)
.Refresh
End With
alla riga
.Refresh
Se, per prova, commento questa riga cioè
' .Refresh
non estrae nulla
draleo
draleo
Maurizio Borrelli
news:579EC20D-DD85-4134...@microsoft.com:
> "Maurizio Borrelli" ha scritto:
[...]
> Le domande da fare sarebbero tante, ma
> per il momento accontentiamoci di farla funzionare
> Mi da infatti errore di run time 1004
> errore generale di ODBC
> in
> With qry
> .CommandText = sql(i)
> .Refresh
> End With
> alla riga
> .Refresh
> Se, per prova, commento questa riga cioè
> ' .Refresh
> non estrae nulla
Ciao draleo.
Vediamo un po'... Versione di Excel? SP? Sistema operativo? Io ho
testato con Vista-SP1+XL2007-SP1 e WinXP-SP3+XL2003-SP3.
Maurizio Borrelli
news:ulsb#u$vIHA...@TK2MSFTNGP03.phx.gbl:
[...]
> > errore di run time 1004
> > errore generale di ODBC
> > in
> > With qry
> > .CommandText = sql(i)
> > .Refresh
> > End With
> > alla riga
> > .Refresh
> > Se, per prova, commento questa riga cioè
> > ' .Refresh
> > non estrae nulla
> Vediamo un po'... Versione di Excel? SP? Sistema operativo? Io ho
> testato con Vista-SP1+XL2007-SP1 e WinXP-SP3+XL2003-SP3.
Ciao drealeo.
Testato con successo anche con WinXp-SP3+XL97-SR2, previa modifica alla
porzione di codice seguente:
With wsh
Set qry = .QueryTables.Add(strCnn, .Range("A1"))
End With
With qry
.CommandText = sql(i)
.Refresh
End With
sotituita con:
With wsh
Set qry = .QueryTables.Add(strCnn, .Range("A1"), sql(i))
End With
qry.Refresh
Pero' questo a te, draleo, se non hai XL97, non interessa.
Intendo sempre versioni italiane, sia del s.o. che di Excel. Anche se
non credo questo sia rilevante.
Cosa fondamentale invece e` che tu abbia copiato il codice che ho
postato, cosi` come sta, in un Modulo standard del progetto VBA della
stessa Cartella di lavoro contenente i Fogli di lavoro di nome "esatto"
e "errato"; particolare che avrai sicuramente colto vedendo che nel
codice mi riferisco a "ThisWorkbook". Cosa questa che si puo` anche
modificare ma, come giustamente dicevi: "per il momento accontentiamoci
di farla funzionare". ;-)
draleo
"Maurizio Borrelli" ha scritto:
> Ciao draleo.
>
> Vediamo un po'... Versione di Excel? SP? Sistema operativo? Io ho
> testato con Vista-SP1+XL2007-SP1 e WinXP-SP3+XL2003-SP3.
>
> --
> Maurizio Borrelli [Microsoft MVP Office System]
> http://www.riolab.org/
>
>
>
>
Windows XP service Pak 2
con Office Professional 2003 vers 11.56.12.5606 (ignoro quale SP)
comunque Ho visto che ricorri alle query SQL. Per esperienza personale ,
quell'errore POTREBBE (?) essere riferito a qualche errore di sintassi nella
stesura della query (basta una virgola....)
draleo
>
Maurizio Borrelli
news:1BA861E5-287D-4963...@microsoft.com:
> "Maurizio Borrelli" ha scritto:
> > Vediamo un po'... Versione di Excel? SP? Sistema operativo? Io ho
> > testato con Vista-SP1+XL2007-SP1 e WinXP-SP3+XL2003-SP3.
> Windows XP service Pak 2
> con Office Professional 2003 vers 11.56.12.5606 (ignoro quale SP)
> comunque Ho visto che ricorri alle query SQL. Per esperienza personale ,
> quell'errore POTREBBE (?) essere riferito a qualche errore di sintassi nella
> stesura della query (basta una virgola....)
Ciao draleo.
Purtroppo l'errore che segnali non sembra riguardare SQL quanto
piuttosto ODBC. Ne' credo vi siano errori SQL in quanto, come dicevo, ho
testato con successo. Con due diversi file, xlsb (XL2007) e xls (XL2003
e XL97).
Tuttavia, se non leggi "SP", vuol dire... nessun SP! E potrebbe essere
proprio questo il problema.
Nella mia finestra "Informazioni su Microsoft Excel" leggo:
Microsoft(R) Office Excel 2003 (11.8211.8202) SP3
Parte di Microsoft Office Professional Edition 2003
Ripeto, non credo sia un problema di SQL tuttavia, per scrupolo,
controlla che i nomi da me usati dei Fogli di lavoro e dei Campi
corrispondano esattamente ai tuoi.
draleo
"Maurizio Borrelli" ha scritto:
Si ,adopero le versioni italiane, sia del s.o. che di Excel.
Anche sotituendo come tu dici non cambia niente :sempre lo stesso errore.
Ho copiato con copia e incolla tutto il tuo codice la dove tu indichi .
i Fogli di lavoro si chiamano :"esatti" (NON "esatto") e "errati" (NON
"errato"),che però corrispondono ai nomi delle cartelle indicati nel codice
sql = sql & " `esatti$` TG"
sql = sql & ", `errati$` TS"
draleo
Norman Jones
=============
Windows XP service Pak 2
con Office Professional 2003 vers 11.56.12.5606 (ignoro quale SP)
[...]
=============
Microsoft Office Excel 2003 :
Release version (SP1) (SP2) (SP3)
11.0.5612.0 11.0.6355.0 11.0.7969.0 11.0.8173.0
Vedi:
How to check the version of Office 2003 products
http://support.microsoft.com/kb/821549/en-us
---
Regards.
Norman
Maurizio Borrelli
news:8ED9E0C7-18BA-49BD...@microsoft.com:
> "Maurizio Borrelli" ha scritto:
[...]
> > Pero' questo a te, draleo, se non hai XL97, non interessa.
[...]
> Si ,adopero le versioni italiane, sia del s.o. che di Excel.
> Anche sotituendo come tu dici non cambia niente :sempre lo stesso errore.
Ciao draleo.
Momento... Io ho detto di sostituire ***SE*** hai XL97. Ma anche questo
non c'entra, purtroppo.
Per ora sono costretto a pensare che il problema nasca dalla tua
installazione di Excel non aggiornata. Non hai la possibilita` di
verificare con altre versioni di Excel? 2007, 2002, 2000, 97?... O con
installazioni piu` aggiornate di XL2003? Giusto per capire e
circoscrivere il problema.
Hai mai usato il comando:
Dati > Importa dati esterni > Nuova query su database...
Database
File di Microsoft Excel*
?
Prova, con un semplice file, con un solo foglio di lavoro e una
elementare struttura di dati del tipo:
_
_| A | B | C |
-+------+------+------+
1|Campo1|Campo2|Campo3|
2|AAAAAA| 123|XXXXXX|
3|BBBBBB| 456|YYYYYY|
\Foglio1/
Che succede?
draleo
"Maurizio Borrelli" ha scritto:
OK trovato l'inghippo
il campo data nasc nel codice presentava lo spazio separatore, mentre nella
mia intestazione della colonna no. Ora ho corretto e tutto funziona bene
Appena assimilato la procedura, avrò qualche domanda da farti
Ma ora il lavora...chiama
Grazie mille
draleo
Maurizio Borrelli
news:E3F2970C-7CBF-4147...@microsoft.com:
> "Maurizio Borrelli" ha scritto:
[...]
> OK trovato l'inghippo
> il campo data nasc nel codice presentava lo spazio separatore, mentre nella
> mia intestazione della colonna no. Ora ho corretto e tutto funziona bene
> Appena assimilato la procedura, avrò qualche domanda da farti
> Ma ora il lavora...chiama
> Grazie mille
Ciao draleo.
Evviva! Grazie per il riscontro. :-)
draleo
"Maurizio Borrelli" ha scritto:
chiedo troppo se chiedo di personalizzare la procedura in 2 punti?
1)il formato data nasc (format(TS.`data nasc`,'yyyy-mm-dd')) mi mostra nella
relativa cella anche l’orario, che io invece vorrei non vedere. Come
modificare l’istruzione?
2)ogni volta che lancio la procedura mi vengono creati 2 nuovi fogli; e
poichè questa sarà lanciata innumerevole volte, per non ingolfare il WB, ogni
volta dovrei cancellarli a mano (rischiando anche di sbagliare il foglio da
cancellare)
Avrei preferito invece che i risultati delle 2 query andassero SEMPRE
rispettivamente in 2 fogli fissi ( foglio 3 e foglio4 previa cancellazione
ogni volta dei risultati precedenti )
Ho conosciuto comunque questo nuovo modo per estrarre i dati in Excel
(tramite query) che non conoscevo. Pensavo che si usassero solo per importare
dati esterni da data base
Grazie anche per questo
draleo
Maurizio Borrelli
news:43551E6F-E37D-44C2...@microsoft.com:
> "Maurizio Borrelli" ha scritto:
[...]
> chiedo troppo se chiedo di personalizzare la procedura in 2 punti?
> 1)il formato data nasc (format(TS.`data nasc`,'yyyy-mm-dd')) mi mostra nella
> relativa cella anche l’orario, che io invece vorrei non vedere. Come
> modificare l’istruzione?
> 2)ogni volta che lancio la procedura mi vengono creati 2 nuovi fogli; e
> poichè questa sarà lanciata innumerevole volte, per non ingolfare il WB, ogni
> volta dovrei cancellarli a mano (rischiando anche di sbagliare il foglio da
> cancellare)
> Avrei preferito invece che i risultati delle 2 query andassero SEMPRE
> rispettivamente in 2 fogli fissi ( foglio 3 e foglio4 previa cancellazione
> ogni volta dei risultati precedenti )
> Ho conosciuto comunque questo nuovo modo per estrarre i dati in Excel
> (tramite query) che non conoscevo. Pensavo che si usassero solo per importare
> dati esterni da data base
> Grazie anche per questo
Ciao draleo.
1) Nota che:
format(TS.`data nasc`,'yyyy-mm-dd')
e` presente nella clausola WHERE della stringa SQL. Quindi non e`
responsabile del formato di output.
La parte che ne e` responsabile e` invece questa, per Sql1:
sql = "SELECT"
[...]
sql = sql & ", TS.datanasc AS tsDataNasc"
[...]
e, per Sql2, questa:
sql = "SELECT"
[...]
sql = sql & ", TS.datanasc AS tsDataNasc"
[...]
sql = sql & ", TG.datanasc AS tgDataNasc"
[...]
Tuttavia per ottenere il formato che desideri non occorre modificare la
stringa SQL ma e` sufficiente impostare le celle al formato desiderato,
accertandosi che sia impostata l'opzione:
[v] Mantieni formattazione celle
presente nella finestra "Proprieta` intervallo dati esterno",
raggiungibile da:
Dati > Importa dati esterni > Proprieta` intervallo dati...
2) Una volta eseguita la procedura e verificato che tutto va come
desiderato, puoi anche eliminare dal progetto il Modulo standard (e
quindi il suo contenuto).
Ogni volta che vorrai rieseguire una delle query non dovrai far altro
che aggiornarla. Cosi`:
Dati > Aggiorna dati
-oppure-
Puoi aggiornare tutte le query della Cartella di lavoro con il pulsante
"Aggiorna tutti", presente nella Barra degli strumenti "Dati esterni".
Per ottenere tale barra:
Visualizza > Barre degli strumenti > Dati esterni
In conclusione, come vedi, hai i due fogli fissi che desideri, che
potrai rinominare come di consueto.
L'unica attenzione che dovrai avere sara` quella di aggiornare la
stringa di connessione e se userai il file in un diverso computer o se
deciderai di spostarlo in altro percorso. Per esempio usando questa
routine:
Public Sub UpdateConnectionStrings()
Dim strCnn As String
Dim wsh As Excel.Worksheet
Dim qry As Excel.QueryTable
With ThisWorkbook
strCnn = "ODBC" _
& ";Driver={Microsoft Excel Driver (*.xls)}" _
& ";DefaultDir=" & .Path _
& ";DBQ=" & .FullName
For Each wsh In .Worksheets
For Each qry In wsh.QueryTables
qry.Connection = strCnn
Next
Next
End With
Set qry = Nothing
Set wsh = Nothing
End Sub
--