current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I got some problems to read binary data when I wish to import data from MySQL by using excel-vba. There is only one column in Table, and the data type is binary. Following is my code 

Private Sub DataUpdate()

Dim oConn As ADODB.Connection
Dim myRS As ADODB.Recordset
Dim mySQL As String
Dim LastR As Long

Set oConn = New ADODB.Connection
Set myRS = New ADODB.Recordset
oConn.Open "DRIVER={MySQL ODBC 5.2a Driver};" _
           & "SERVER=localhost;" & "DATABASE=test;" & "USER=root;" _
           & "PASSWORD=XXXXXXX;" & "Option=3"

mySQL = "select * from `test` "
myRS.Open mySQL, oConn

With Worksheets(1)

    LastR = .Range("A65536").End(xlUp).Row

    Do While Not myRS.EOF        
        .Cells(LastR + 1, 1) = myRS.Fields(0)
        myRS.MoveNext
        LastR = LastR + 1
    Loop

End With

Set oConn = Nothing
Set myRS = Nothing

End Sub

the result shows nothing but blank. please help me！！

share|improve this question
add comment

1 Answer

up vote 0 down vote

I'd replace mySQL = "select * fromtest" with mySQL = "SELECT * FROM [test]
and
Replace this code 

With Worksheets(1)

    LastR = .Range("A65536").End(xlUp).Row

    Do While Not myRS.EOF        
        .Cells(LastR + 1, 1) = myRS.Fields(0)
        myRS.MoveNext
        LastR = LastR + 1
    Loop

End With

Set oConn = Nothing
Set myRS = Nothing

End Sub

with this

Sheets(1).Range("A65536").CopyFromRecordset myRS

myRs.Close
Set myRs = Nothing
oConn.Close
Set oConn = Nothing
End Sub
share|improve this answer
1  
Thank you, mehow! But, I still can't import the binary data from MySQL into EXCEL!! –  sasho Jun 19 '13 at 15:22
 
@sasho youre welcome. if this answers your question please accept the answer ( green tick box left hand side ) –  mehow Jun 19 '13 at 15:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.