List:General Discussion« Previous MessageNext Message »
From:Arjan Hulshoff Date:September 2 2005 7:42am
Subject:RE: Populate values in an Excel sheet from MySQL
View as plain text  
Hello Nick,

This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:

<--Begin Code-->
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet

Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet

cn.ConnectionString = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;"
cn.Open

sSQL = "SELECT * FROM database"

rs.Open sSQL, cn

If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
	Cells(1, 1) = rs.Fields(<index>)	' This line you can
adjust with your own code
	rs.MoveNext
Loop

On Error Resume Next	' This is my solution to make sure that the
recordset is always closed, _
					without the errorhandling there
occurs an error when you use a query _
					that doesn't return results
('INSERT' e.g.). If there is a better way _
					to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close

Set rs = Nothing
Set cn = nothing
<--End Code-->>

HTH,
Arjan.

-----Original Message-----
From: Nick Jones [mailto:nickj6282@stripped] 
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@stripped
Subject: Populate values in an Excel sheet from MySQL

Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).

Thanks
-Nick

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1



-- 
The information contained in this communication and any attachments is confidential and
may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the intended
recipient, please notify the sender immediately by replying to this message and destroy
all copies of this message and any attachments. ASML is neither liable for the proper and
complete transmission of the information contained in this communication, nor for any
delay in its receipt.
Thread
Populate values in an Excel sheet from MySQLNick Jones1 Sep
  • RE: Populate values in an Excel sheet from MySQLJ.R. Bullington1 Sep
    • RE: Populate values in an Excel sheet from MySQLNick Jones1 Sep
      • RE: Populate values in an Excel sheet from MySQLSGreen1 Sep
      • Re: Populate values in an Excel sheet from MySQLPartha Dutta1 Sep
        • Re: Populate values in an Excel sheet from MySQLNick Jones1 Sep
      • RE: Populate values in an Excel sheet from MySQLJ.R. Bullington1 Sep
  • Re: Populate values in an Excel sheet from MySQLJamesDR1 Sep
    • how to design junction table?Siegfried Heintze3 Sep
      • Re: how to design junction table?Gleb Paharenko5 Sep
RE: Populate values in an Excel sheet from MySQLArjan Hulshoff2 Sep
  • Re: Populate values in an Excel sheet from MySQLinferno2 Sep
    • Re: Populate values in an Excel sheet from MySQL [SOLVED]Nick Jones3 Sep