/ Published in: Visual Basic

Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
Sub Excel2MySQL() ' Excel2MySQL ' Versions: ' 1.0 - Jeffrey Berthiaume - October 26, 2005 - Initial version ' How to use: ' - name the worksheet the name of the Table you want to import into ' - name each of the columns (row 1) the name of the row ' - run the macro. ' the sql file will be saved to c:\[Tablename].sql Open "c:\" & Sheet1.Name & ".sql" For Output As #1 totalrows = ActiveSheet.UsedRange.Rows.Count totalcols = ActiveSheet.UsedRange.Columns.Count colnames = "" For y = 1 To totalcols colnames = colnames & Cells(1, y) If y < totalcols Then colnames = colnames & "," End If Next y For x = 2 To totalrows s = "INSERT INTO " & Sheet1.Name & " (" & colnames & ") VALUES (" For y = 1 To totalcols s = s & "'" & Replace(Cells(x, y).Value, "'", "\'") & "'" If y < totalcols Then s = s & "," Else s = s & ");" Print #1, s End If Next y Next x Close #1 End Sub
Comments
