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

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/

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