I have no idea about excel macro. I need to get the data from MySQL and display it in excel. Can i get any links or code examples for this.
-
Same question like that one stackoverflow.com/questions/8816918/…Muhammad Umar– Muhammad Umar2012-11-12 07:37:38 +00:00Commented Nov 12, 2012 at 7:37
-
i need to use macro to get data from mysql to excel.user1665707– user16657072012-11-12 08:04:22 +00:00Commented Nov 12, 2012 at 8:04
-
check this if not helpful then use google. excelguru.ca/…Muhammad Umar– Muhammad Umar2012-11-12 13:45:36 +00:00Commented Nov 12, 2012 at 13:45
-
Tats too complicate. Anyways i found the ans :)user1665707– user16657072012-11-14 10:37:21 +00:00Commented Nov 14, 2012 at 10:37
Add a comment
|
2 Answers
Sub ADOExcelSQLServer()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "" ' Enter your server name here
Database_Name = "" ' Enter your database name here
User_ID = "" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "select Period, Secured, Unsecured from cmisa.credit_facts"
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("1.Overview").Range("AI7:AK19") ' Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With
' Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
Comments
if want to view data, you can use myODBC to connect exel, read http://searchenterpriselinux.techtarget.com/tip/Using-Excel-to-analyze-MySQL-data
register member before read fully