Re: Export mySQL to Excel

[Jay Alverson]

I think so, there are two methods: (that I'm familiar with)

1) export MySQL data using the OUTFILE, then import into Excel using a QUERYTABLE to read in the data.

2) use ADO to open a recordset from MySQL and then use Excel's .CopyFromRecordset() to place the data directly into Excel.

I think #2 is probably the way you want to go, especially if you know how to use the Excel object model.

If you go into the Excel VBA Help file look at the CopyFromRecordset Method:

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails.

Example
This example copies the field names from a DAO Recordset object into the first row of a worksheet and formats the names as bold. The example then copies the recordset onto the worksheet, beginning at cell A2.

For iCols = 0 to rs.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs


You can setup a DSN-less connection to MySQL using ADO, it's pretty simple. I can probably put together an example later today using VBSCript which should be pretty close to what you need.


>

Thanks, Jay