VBA Macro upload data from Excel spreadsheet to phpmyadmin database

shy4x4

New Member
Joined
Oct 11, 2005
Messages
34
Hi all,

I have been searching for an answer on this problem for about a week now but to no avail and I am beginning to think that it might not be possible. So thought I would ask some experts to get their thoughts!

What I am attempting to do is write a macro which could connect to a phpmyadmin database from excel and upload data from excel to the phpmyadmin database. I have seen a lot of content on the reverse of this which is pulling data from the database into excel, however I would like to automate the process of uploading data everyday from the excel spreadsheet to the database.

So far I have been able to write the below VBA which successfully connects and disconnects from the DB however does someone out there have any VBA or guidance as to what the code might be to insert data from the excel spreadsheet into the phpmyadmin database?


Private Sub UserForm_Initialize()
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
con.ConnectionString = "server=test.tesing.com.au" & _
";driver=MySQL ODBC 3.51 Driver;db=online;" & _
"uid=MSmith;pwd=Password;" & _
"Option=16386"
'con.CursorLocation = adUseClient
con.Open

***thinking that the macro insert code will go here but need to see the format it should be written in***

con.Close
End Sub

Thanks all for your assistance here.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

shy4x4

New Member
Joined
Oct 11, 2005
Messages
34
I have worked it out, placing my code here to help others.


Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset

'remove dangerous characters
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
End Function

Private Sub cmdInsertData_Click()
On Error GoTo ErrHandler
Set rs = New ADODB.Recordset
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=YourServerNameHere;" & _
"DATABASE=YourDatabaseNameHere;" & _
"USER=YourUserNameHere;" & _
"PASSWORD=YourPasswordHere;" & _
"Option=3"

'number of rows with records. testingupload is the sheetname.
Dim height As Integer
height = Worksheets("testingupload").UsedRange.Rows.Count

'insert data into SQL table. testingupload is the sheetname.
With Worksheets("testingupload")
Dim rowtable As Integer
Dim strSQL As String
For rowtable = 2 To height
strSQL = "INSERT INTO YourTableNameHere (YourFieldHere, YourFieldHere, YourFieldHere) " & _
"VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 3).Value)) & "')"
rs.Open strSQL, oConn ',adOpenDynamic, adLockOptimistic
Next rowtable
End With

MsgBox "Insert with success " & Trim(Str(rowtable - 2)) & " records", vbInformation, "Verification Data Entry"
ErrHandler:
If Err.Description <> "" And Err.Source <> "" Then
MsgBox Err.Description, vbCritical, Err.Source
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top