![]()
Function esc(txt) 'Will replace the 's with MySQL-friendly characters esc = Trim(Replace(txt, "'", "\'")) End Function Sub upload() '==================================== '= Code made by : = '= Michael Clermont & The Interwebs = '= 04/04/2011 - Version 1.00 = '==================================== 'INSTRUCTIONS = '================================================================================================== ' 1. Make sure you have Microsoft ActiveX Data Objects Library enabled (Tool > References). = ' 2. Download the latest MySQL ODBC Driver (Google search). = ' 3. Change the settings below and select the proper driver in this case "MySQL ODBC 5.1 Driver". = ' 4. Copy the required data into your form and press the button. = '================================================================================================== '------------------------------------------------------------------------------ 'Variable declarations '------------------------------------------------------------------------------ Dim conn As New ADODB.Connection Dim server_name As String Dim database_name As String Dim user_id As String Dim password As String Dim database_table As String Dim count As Integer Dim cell As String Dim cell_1 As String Dim cell_2 As String Dim count_2 As Integer Dim count_3 As Integer '------------------------------------------------------------------------------ 'Settings '------------------------------------------------------------------------------ server_name = "192.168.1.200" 'Enter your server IP here - if running from a local computer use 127.0.0.1 database_name = "wds" 'Enter your database name here user_id = "root" 'Enter your database user here password = "bmx1" 'Enter your database user password here database_table = "tblprod_agr_006" 'Enter database table name '------------------------------------------------------------------------------ 'Initiate database connection '------------------------------------------------------------------------------ 'Specify your driver below Set conn = New ADODB.Connection conn.Open "DRIVER={MySQL ODBC 5.2 Unicode Driver}" _ & ";SERVER=" & server_name _ & ";DATABASE=" & database_name _ & ";UID=" & user_id _ & ";PWD=" & password _ & ";OPTION=16427" ' Option 16427 = Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted ' & ";DSN=" & DSN_ 'MsgBox "Server connection OK" 'Debug '------------------------------------------------------------------------------ 'Assign values to global variables '------------------------------------------------------------------------------ aWidth = WorksheetFunction.CountA(Range("A1:FA1")) 'Finds the width of the table - if you have more columns than that, just extend the range aheight = WorksheetFunction.CountA(Range("A1:A65536")) - 1 'Finds the height of the table, minus the field names - if you have more rows than that, just extend the range count = 0 'Will be used throughout the macro as a counter count_2 = 0 'Will be used throughout the macro as a counter count_3 = 0 'Will be used throughout the macro as a counter '------------------------------------------------------------------------------ 'Populate the table row '------------------------------------------------------------------------------ ReDim array_fields(aWidth) 'This will populate INTO what the VALUES will go for the whole upload Do Until count = aWidth count = count + 1 'Set the count to be used in the array and increment it for the the Do cell = Worksheets("production_data").Cells(1, count).Value array_fields(count) = cell Loop 'This is not necessary, but is done for better code comprehension (and to avoid any problems later on) count = 0 'reset the counter '------------------------------------------------------------------------------ 'Get the data and store it within an array '------------------------------------------------------------------------------ ReDim array_values(aheight) Do Until count = aheight 'repopulate the first cell If IsNumeric(array_fields(count_2)) Then strQuote = vbNullString Else strQuote = "'" End If cell = strQuote & esc(array_fields(count_2)) & strQuote 'Populate the cell value ' Do Until count_3 = aWidth ' If IsNumeric(array_fields(count_2, (count_2 + 1))) Then ' strQuote = vbNullString ' Else ' strQuote = "'" ' End If ' cell_2 = cell_2 & ", " & strQuote & esc(array_fields(count_2, (count_3 + 1))) & strQuote ' count_3 = count_3 + 1 'Loop 'MsgBox (cell) 'debug 'MsgBox (cell_2) 'debug 'Run the query strSQL = "INSERT INTO " & database_table & " (" & cell_1 & ") VALUES (" & cell_2 & ")" 'MsgBox (strSQL) 'debug conn.Execute (strSQL) 'execute the above query 'Reset the variables for the loop count_2 = count_2 + 1 count = count + 1 count_3 = 1 cell_2 = "" Loop 'Close the DB connection conn.Close Set conn = Nothing MsgBox ("Upload finished") 'This is not necessary, but is done for better code comprehension (and to avoid any problems later on) count = 0 'clean up the variables, just in case count_2 = 0 'clean up the variables, just in case End Sub
Bookmarks