Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[EXCEL] codice VBS per colllegamento e query in MySQL

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 01/08/11 11:21

Ciao a tutti, ho bisogno di creare delle query di download e upload di dati da un foglio Excel a un DB di MySQL.

Sono partito da questo tutorial:
http://www.heritage-tech.net/908/insert ... using-vba/

Lo trovo un po' macchinoso e siccome il codice è intervallato dalle spiegazioni nel "rimontarlo" devo aver sbagliato qualcosa, infatti non mi funziona...

Codice: Seleziona tutto
 Dim oConn As ADODB.Connection
    Dim rs As ADODB.Recordset
   
    Private Sub ConnectDB()

        Set oConn = New ADODB.Connection

        oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & "DATABASE=test;" & "USER=root;" & "PASSWORD=prova;" & "Option=3"

    End Sub

    Function esc(txt As String)

        esc = Trim(Replace(txt, "'", "\'"))

    End Function

    Private Sub InsertData()

        Set rs = New ADODB.Recordset

        ConnectDB

        With wsBooks

            For rowCursor = 2 To 11

                strSQL = "INSERT INTO tutorial (author, title, price) " & "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & "'" & esc(.Cells(rowCursor, 2)) & "', " & esc(.Cells(rowCursor, 3)) & ")"

                rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

            Next

        End With

    End Sub


La prima richiesta è se mi poteste correggere il codice messo tutto insieme. Inoltre, almeno in un primo tempo vorrei togliere la funzione "esc" ma sono sicuro che togliendo parentesi e apici nella strSQL sbaglio qualcosa. Mi aiutate a togliere quella funzione?

Aggiungo che ho trovato utilissimo il documento scaricato da qui:
http://www.mysql.it/why-mysql/white-pap ... _guide.php

In pratica si crea la connessione via ODBC e poi la si richiama da Excel ma con la procedura guidata.
Come si fa invece a richiamare tale connessione via VBS e a fare poi la query?

Grazie delle risposte che mi darete
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Sponsor
 

Postdi archimede » 01/08/11 12:20

Dylan666 ha scritto:non mi funziona...
Un po' vago: dà errore? La ConnectDB hai verificato che funzioni? Hai provato un MsgBox strSQL per controllare l'istruzione che tenta di eseguire?
Dylan666 ha scritto:vorrei togliere la funzione "esc" ma sono sicuro che togliendo parentesi e apici nella strSQL sbaglio qualcosa. Mi aiutate a togliere quella funzione?
Ma che fastidio ti dà? Comunque basta mettere al suo posto la singola istruzione che contiene.

HTH.

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova

Re:

Postdi Dylan666 » 01/08/11 14:04

archimede ha scritto:
Dylan666 ha scritto:non mi funziona...
Un po' vago: dà errore? La ConnectDB hai verificato che funzioni? Hai provato un MsgBox strSQL per controllare l'istruzione che tenta di eseguire?

Non dà segni di vita... non appaiono errori ma nemmeno importa i valori nel DB di MySQL.
I dati di connessione sono giusti, infatti da ODBC funziona.
Il "MsgBox strSQ" dove lo metto per avere un test valido?


Dylan666 ha scritto:vorrei togliere la funzione "esc" ma sono sicuro che togliendo parentesi e apici nella strSQL sbaglio qualcosa. Mi aiutate a togliere quella funzione?
Ma che fastidio ti dà? Comunque basta mettere al suo posto la singola istruzione che contiene.

Mi è più comodo perché posso esportare la connessione ODBC e poi visualmente mi è più comodo.
Inoltre volendo in futuro creare più file XLS che si connettano al DB se dovrò cambiare la password del DB basterà aggiornare il file ODBC, altrimenti mi toccherebbe modificare la connection string di tutti i file Excel ;)
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Postdi archimede » 01/08/11 14:19

Dylan666 ha scritto:da ODBC funziona.
Ok, ma la tua sub funziona? Dall'articolo da cui hai preso spunto:
Test your ConnectDB Sub procedure by putting the cursor anywhere between the Sub statement and press F5 (this runs the Sub procedure). If an error popup appears, check that your MySQL service is running and that you’ve referenced to the Microsoft ActiveX Data Objects 2.8 Library.
Dylan666 ha scritto:Il "MsgBox strSQ" dove lo metto per avere un test valido?
Dopo aver valorizzato la variabile, quindi prima dell'istruzione rs.Open.
Dylan666 ha scritto:Mi è più comodo perché posso esportare la connessione ODBC ... se dovrò cambiare la password del DB basterà aggiornare il file ODBC, altrimenti mi toccherebbe modificare la connection string di tutti i file Excel ;)
:eeh: Ma cosa c'entra la funzione esc con la connessione al db? Quella serve unicamente a manipolare i dati (le stringhe) che invii al db per renderle "digeribili" all'SQL.

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 01/08/11 14:50

Scusa, il secondo "quote" lo avevo letto male, avevo letto "che male ti fa la connection string" :P :oops: .
La funziona la voglio togliere per arrivare al "minimo" del codice, in modo da capirlo.
Poi ci metterò i vari trim e replace, ma solo quando sarò arrivato a far funzionare la base (e a capirla).

La mia sub con F5 restituisce "errore 424 necessario oggetto", togliendo la function non vedo errori ma comunque non aggiorna i dati in SQL

Per eliminare l'errore ho tolto la function e tentato di modificare la query levandone i riferimenti:

Codice: Seleziona tutto
strSQL = "INSERT INTO tutorial (author, title, price) " & "VALUES ('" & (rowCursor) & "', " & "'" & (rowCursor) & "', " & (rowCursor) & ")"
MsgBox strSQ


La finestra è vuota, cioè non riporta alcun testo, e subito dopo o un errore di automazione numero -2147217887
La Microsoft ActiveX Data Objects 2.8 Library è correttamente presente tra i riferimenti, ho ricontrollato
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Postdi archimede » 01/08/11 15:07

Dylan666 ha scritto:La mia sub con F5 restituisce "errore 424 necessario oggetto"
Quale Sub? Se è la ConnectDB allora significa che non si connette a MySql; prima di fare il debug del resto devi capire perché (ved. mia citazione dal tuo articolo).

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 01/08/11 15:20

Da una più accurata anlisi sembra sia la "InsertData"
Se riduco il comando a strSQL = "INSERT INTO tutorial funziona, cioè il MasBox appare con la stringa.
È quindi un errore di virgolette? Mi aiutate a ridurre la stringa al minimo senza la funzione?
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 01/08/11 17:26

Ok, sono arrivato a questo punto:

Codice: Seleziona tutto
    Dim oConn As ADODB.Connection
    Dim rs As ADODB.Recordset
   
    Private Sub ConnectDB()

        Set oConn = New ADODB.Connection

        oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & "DATABASE=test;" & "USER=root;" & "PASSWORD=prova;" & "Option=3"

    End Sub

    Private Sub InsertData()

        Set rs = New ADODB.Recordset

        ConnectDB

        With wsBooks

            For rowCursor = 2 To 11

                strSQL = "INSERT INTO tutorial (author, title, price) " & "VALUES ('" & Cells(rowCursor, 1).Value & "', " & "'" & Cells(rowCursor, 2).Value & "', " & "'" & Cells(rowCursor, 3).Value & "')"
MsgBox strSQL
                rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

            Next

        End With

    End Sub


Funzione tolta e virgolette aggiustate.
Come fare la stessa cosa senza connection string e con connessione ODBC?
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Postdi archimede » 01/08/11 17:35

Dylan666 ha scritto:Funzione tolta e virgolette aggiustate.
E che succede se in una cella metti un apostrofo ( ' )?

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 01/08/11 17:41

Sì, ho capito che va in errore... ovviamente gli apici della cella vanno a infilarsi nella connection string...
Una cosa per volta però! Adesso mi servirebbe di richiamare il mio DSN su File chiamato MIO_MySQL dal codice VBS sopra riportato.

Poi comicerò a giocare di inport e di export e valutare tutte le ottimizzazioni del caso
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Postdi archimede » 01/08/11 19:25

Dylan666 ha scritto:Adesso mi servirebbe di richiamare il mio DSN su File chiamato MIO_MySQL dal codice VBS sopra riportato.
Boh, tiro a indovinare:
Codice: Seleziona tutto
oConn.Open "DSN=MIO_MySQL"
?

HTH.

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 02/08/11 08:06

In rete ho trovato questo:
Codice: Seleziona tutto
oConn.Open "FILEDSN=c:\MIO_MySQL.dsn;" & "Uid=root;" & "Pwd=prova"


Errori non ne dà ma nemmeno inserisce i dati.
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 02/08/11 08:53

Lascio un attimo in standby l'uso dell'ODBC per la richiesta del procedimento inverso al precedente.
Se il VBS di prima serve a INSERIRE i dati da Excel in MySQL come si fa il procedimento inverso?
Cioè ammesso che abbia la stessa tabella DB di prima, con gli stessi campi, come li importo in un foglio di lavoro?

Qui c'è il procedimento tramite procedura guidata:
http://www.heritage-tech.net/267/interf ... via-excel/

Ipotizziamo che vogli utilizzare la connection string di prima che è già pronta.
Come prendo i dati e li metto nel foglio partendo da A1?

Grazie
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Postdi archimede » 02/08/11 09:53

Dylan666 ha scritto:ammesso che abbia la stessa tabella DB di prima, con gli stessi campi, come li importo in un foglio di lavoro?
Mai provato personalmente, ma dovrebbe essere fattibile. Ma non faresti prima a esportare da MySql in Excel? O forse puoi creare una macro per automatizzare la procedura guidata; qualcuno degli esperti di Excel qui potrà forse confermare quest'ultima ipotesi.

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 02/08/11 10:08

La base dati già esiste, io dovrei in pratica "aggiornarla" con alcuni dati prelevati da Excel.

Se fossi in SQL la mia query sarebbe così:
Codice: Seleziona tutto
UPDATE campoExcel1=table1.valoreA, campoExcel2=table1.valoreB, campoExcel3=table1.valoreC WHERE...


Il resto dei dati del foglio resta invariato. Ad esso sono collegati viste, filtri, pivot e tanta altra bella roba pre-esistente

Lo so che in teoria l'integrazione Excel/RDBMS è fattibile ma essendo poco pratico di VBS mi servirebbero un paio di righe di codice per partire ;)
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Re: [EXCEL] codice VBS per colllegamento e query in MySQL

Postdi Dylan666 » 02/08/11 10:59

Mamma Microsoft è stata provvidenziale, almeno per partire:
http://support.microsoft.com/kb/306125

La connection string lì presente è stata sostituita con questa:

Codice: Seleziona tutto
   
 ' Provide the connection string.
    Dim strConn As String

    'Use the SQL Server OLE DB Provider.
    strConn = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & "DATABASE=test;" & "USER=root;" & "PASSWORD=prova;" & "Option=3"

    'Now open the connection.
    cnPubs.Open strConn


Ora devo trovare il codice (probabilmente un ciclo) per passare le righe trovate una per una e piazzarne i dati nelle celle che mi servono.
Mi fate per capire come poter mettere in variabile le singole righe e i singoli valori invece di avere tutto in blocco come fa CopyFromRecordset?

Grazie :)
Avatar utente
Dylan666
Moderatore
 
Post: 38040
Iscritto il: 18/11/03 16:46

Postdi archimede » 02/08/11 14:31

Questo parla di Access, ma dovrebbe funzionare anche in Excel, se non come sintassi sicuramente come principi generali.

Per popolare le singole celle coi valori del recordset lascio la parola a chi conosce Excel meglio di me.

HTH.

Alessandro
archimede
Moderatore
 
Post: 2755
Iscritto il: 07/11/02 12:41
Località: Genova


Torna a Applicazioni Office Windows


Topic correlati a "[EXCEL] codice VBS per colllegamento e query in MySQL":


Chi c’è in linea

Visitano il forum: fabrizio2001, Marius44 e 6 ospiti