Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQLRequest to MSSQL server - macro
Hello all,
I am looking for some help with my first real macro. I'm afraid I nee to jump right into it for a work project. I used a tutorial online an an example and came up with the following code which I have on a shee called "Test" Private querystring As Variant Private returnArray As Variant Sub Worksheet_Activate() querystring = "select * from tblusers" returnArray = SqlRequest("DSN=MDC", querystring Worksheets("Test").Range("A1"), 2, False) For i = LBound(returnArray, 1) To UBound(returnArray, 1) For j = LBound(returnArray, 2) To UBound(returnArray, 2) Worksheets("Test").Cells(i, j).Formula = returnArray(i j) Next j Next i End Sub With that code I get that the SqlRequest is not defined. IF I change i to SQL.Request I get "Runtime error 424, Object required". The erro does come up when stepping past that line. IN other words I hit F8 til the arrow gets to that returnArray line and all is well. The next tim I hit it I get the errors mentioned above. MDC is setup as a User DSN connection to a SQL Server. The querystrin is one of the simplest queries I could think of with the smalles result. Any help would be much appreciated, especially if I am headed i completely the wrong direction -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQLRequest to MSSQL server - macro
Turn on the macro recorder (Tools/Macro/Record New Macro) and do a manual
query (Data/Get External Data/New Database Query) to see what the code should look like for your conditions. HTH, Greg "chuckiej " wrote in message ... Hello all, I am looking for some help with my first real macro. I'm afraid I need to jump right into it for a work project. I used a tutorial online and an example and came up with the following code which I have on a sheet called "Test" Private querystring As Variant Private returnArray As Variant Sub Worksheet_Activate() querystring = "select * from tblusers" returnArray = SqlRequest("DSN=MDC", querystring, Worksheets("Test").Range("A1"), 2, False) For i = LBound(returnArray, 1) To UBound(returnArray, 1) For j = LBound(returnArray, 2) To UBound(returnArray, 2) Worksheets("Test").Cells(i, j).Formula = returnArray(i, j) Next j Next i End Sub With that code I get that the SqlRequest is not defined. IF I change it to SQL.Request I get "Runtime error 424, Object required". The error does come up when stepping past that line. IN other words I hit F8 till the arrow gets to that returnArray line and all is well. The next time I hit it I get the errors mentioned above. MDC is setup as a User DSN connection to a SQL Server. The querystring is one of the simplest queries I could think of with the smallest result. Any help would be much appreciated, especially if I am headed in completely the wrong direction. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQLRequest to MSSQL server - macro
Greg Koppel wrote:
*Turn on the macro recorder (Tools/Macro/Record New Macro) and do manual query (Data/Get External Data/New Database Query) to see what th code should look like for your conditions. HTH, Greg * That's exactly the starting point I needed. I was not sure if th recorder was that sophisticated. It is! Thanks Greg -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002, MS Query and MSSQL View with periods | Excel Discussion (Misc queries) | |||
Sqlrequest only return one value | Excel Worksheet Functions | |||
sqlrequest in excel 2003 | Excel Worksheet Functions | |||
SQL.REQUEST query omitting rows in MSSQL DB | Excel Programming | |||
Excel - SQLREQUEST - #N/A | Excel Programming |