Help Required: MySQL query dump to EXCEL stops with binary based columns

[Mach7]

Dear experts,

I am currently working on a project that requires the export of data (various tables) from MySQL Database which consists of Binary, Decimal and Hex in a single table.

The code which i use is a great source and works well until when the columns hold Binary data. The error shown is Error 400, which stops the data extraction process.

I suspect the issue lies with the Range function with Offset.

--------------------------------------------------------------------------------------------------
For H = 0 To Horizon
Range("A8").Offset(0, H).Value = rs.Fields(H).Name 'Move cell from Left to Right

For V = 0 To Verti
Range("A8").Offset(V + 1, H).Value = myArray(H, V) 'Move cell from Up to Down
Next
--------------------------------------------------------------------------------------------------

The pictures for the error 400 and Binary format in MySQL Database table can be seen from the following hyperlink.


Error400.jpg

VBA_Error400.jpg


MySQL_DataBase_Table_with_BinaryFormat_from IMAGE_Column.jpg

MySQL_DataBase_Table_with_BinaryFormat_from IMAGE_Column.jpg


Can you please advice how i can move on?


Thank you very much.

=======================================================================================


Sub ExtractAgentFlowsFromMySQL()

Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
Set rs = CreateObject("ADODB.Recordset")

Range("a8:bb60000").ClearContents

Server_Name = Range("b3").Value ' IP number or servername
Database_Name = Range("b6").Value ' Name of database
User_ID = Range("b4").Value ' id user or username
Password = Range("b5").Value ' Password
Tabellen = Range("e2").Value ' Name of table to write to

SQLStr = "SELECT * FROM " & Tabellen

Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic

Dim myArray() ' Declare array

myArray = rs.GetRows() ' Put into myArray

Horizon = UBound(myArray, 1) ' Returns the highest available subscript for the indicated dimension of an array.
Verti = UBound(myArray, 2) ' Returns the second available subscript for the indicated dimension of an array.

For H = 0 To Horizon
Range("A8").Offset(0, H).Value = rs.Fields(H).Name 'Move cell from Left to Right

For V = 0 To Verti
Range("A8").Offset(V + 1, H).Value = myArray(H, V) 'Move cell from Up to Down
Next
Next

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub


=======================================================================================

[Mach7]

Hi there,

Can anyone help me on this?

If the copy of such columns are not possible, then how can i bypass such columns and proceed to copy to the last column?

Thank you very much.

[mike7952]

Why not just copy the recordset into excel instead of looping thru get rows.

Please Login or Register  to view this content.