Get data into EXCEL from an ODBC data source

[webasp3]

Guys, I am new to Excel VBA and desperately in need of help.
I do not know if it is possible to do in Excel/VBA but this is what I want to do.
"Create a template in excel for users with sales data with subtotals for a week, month etc.I want them to just open the file where it refreshes/connects to the database (SQL Server)behind the scenes, retrieves data through a query, loops thorugh the data and throws it into appropriate cells in the spreadsheets and create hyperlinks from each and every number/data(sales figure). On clicking the hyperlink/sales figure a small window pops up giving the detailed information (drill down) about the number.

1. Can some one tell me if that is possible.
2. what am I doing wrong in this script that it gives me "Compile Error: User Defined Type not defined." for "Set db_data = New Connection" in the following code:

Sub Autpen()
Set db_data = New Connection
db_data.CursorLocation = adUseClient
db_data.Open "PROVIDER=MSDASQL;dsn=SQLConn;uid=sa;pwd=sa;database=PSTEST;"

Set ado_data = New Recordset

' Assume we retrieve two columns of data that are to go into column A and B respectively
' of the spreadsheet

ado_data.Open "select * from PSTEST.dbo.PS_AHP_CORP_SUM_VW", adOpenStatic, adLockOptimistic

If ado_data.RecordCount <= 0 Then
MsgBox ("No data found")
End Sub
End If

ado_data.movefirst

For I = 0 To ado_data.RecordCount - 1
Book1.Cells(I + 1, 1) = (ado_data.Fields.Item(0).Value)
Book1.Cells(I + 1, 2) = (ado_data.Fields.Item(1).Value)
ado_data.MoveNext
Next

End Sub

 

[taupirho]

Hi, what you're trying to acheive is entirely possible.

The reason you're getting an error on that line is, I suspect, because you have not included the Microsoft Data Access component in your list of references.

In the VBA editor click on Tools-> References. Scroll down till you see something like Microsoft ActiveX Data Objects 2.7 Library. Check the checkbox beside it and try re-compiling

 

[webasp3]

Hi Tomreid
I am really thankful for your help and finding time to answer my question. I have done what you had suggested but now another message pops up that says:

Runtime Error: '-2147467259(80004005)':
[Microsoft][ODBC Driver Manger] Data Source name not found and found no default driver specified.

I have DSN name as SQLconn and I have tested it too. It connects successfully to the database. I have this in "Control Manger/Administrative Tools/Data Source(ODBC)/System DSN".Can you use the same code and connect to one of your databases on SQL Servers? I have Excel 2000 and VB 6.3.

Thank you very much

 

[IncredibleVolk]

I have a problem similar to the one listed above. I currently push data from SQL Server to a Microsoft Excel spreadsheet. The data is summarized. I was wondering how to create hyperlinks from each and every number/data where a small window pops up giving the detailed information (drill down) about the numbers in each cell. In other words, I'm assuming I'd have to run an additional query to retrieve the drilled down data and I want to know how I can do it once the user clicks on the hyperlink.

 

[xlbo]

To be honest - there are entire applications built along this kind of premis - whilst it may technically be pssible in excel, I would have no doubt that it would take extensive coding - IMHO, if your business needs to do something like this, they need to invest in a Business Intelligence tool like Business Objects, Crystal Reports, MS Reporting Services etc etc

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[taupirho]

I don't know if this helps you but one way might be to use something like the worksheet_beforerightclick event. The target of this tells you which cell you just right-clicked in. How you tie this up with the drill-down data you want to display is up to you but you could use a small userform as your popup or more crudely just a message box. Here's a quick example of using the event.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
cancel=true
MsgBox ("You just clicked on cell " & Target.Address)
End Sub