Solved

How to fetch the data from SQLSERVER in an array in VBA EXCEL instead to display it in worksheet

Posted on 2014-03-26
6
655 Views
Last Modified: 2014-03-27
Hi,



My question is simple for EE used to EXCEL VBA
I want to get the data from SQLSERVER in an array instead to display
it in the worksheet
My code works to select the data and display it in the EXCEL worksheet
it is done via this command

 ThisWorkbook.Sheets("my_sheet").Range("D7").CopyFromRecordset rst

Open in new window



Can Someone tell me how to fetch the rst in an array inside the macro VBA please?

Thanks Dave
 Sub load_users()


    Dim cnn As ADODB.Connection

    Dim connectionString As String   Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
 
   Dim strSQL As String
     
    Dim User_ID As String
    Dim User_PWD As String
    Dim Initial_Catalog As String 'Nom de la base de données
    Dim Workstation_ID As String
    Dim Data_Source As String
    
    Application.ScreenUpdating = False
    
    On Error GoTo Verif_Connexion
    User_ID = "my login " '"SYGES"   
    User_PWD = "my password" ' "SYGES2014"  
    Initial_Catalog = "DATABASE"  
    Data_Source = "url"  
    Workstation_ID = "url"  '
      
    Set cnn = New ADODB.Connection
    connectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & User_ID & ";PWD=" & User_PWD & ";Initial Catalog=" & Initial_Catalog & ";Data Source=" & Data_Source & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & Workstation_ID & ";Use Encryption for Data=False;Tag with column collation when possible=False;"
    
    ThisWorkbook.Sheets("my_sheet").Range("A2:AZ65536").Value = ""
    
    strSQL = "select login, password from MYTABLE  "
   
    cnn.Open connectionString

    Set rst = cnn.Execute(strSQL)
    

    ThisWorkbook.Sheets("my_sheet").Activate


    ' HERE IS MY QUESTION
    'This put the result in the worksheet
    'I would like to have it in an array inside the code instead to displa it in the worksheet

    ThisWorkbook.Sheets("my_sheet").Range("D7").CopyFromRecordset rst
    

     
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
     
end sub

Open in new window

0
Comment
Question by:DavidInLove
  • 3
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 39955521
Hi,

pls try

arrRecordArray = rst.GetRows

Open in new window

Regards
0
 

Author Comment

by:DavidInLove
ID: 39955608
Hi,


I've tried to code something but it doesn't work.


' I declare the array as Variant
  Dim arrRecordArray As Variant


    Set rst = cnn.Execute(strSQL)
    
 arrRecordArray = rst.GetRows

MsgBox (arrRecordArray(0))

Open in new window



I just would like to do the same as the following working in PHP


    $res_query  = mssql_query ($req);			
		$row_donTot=mssql_num_rows($res_query);	
  	  while($tab=mssql_fetch_array($res_query)){		
  	   $niveau1[$cpt++]=$tab['niveau1']; 		
  	  }		

Open in new window


So if SO could give me the code to fetch

Thanks Dave
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 39955628
The returned array will be two dimensional with fields as 'rows' and records as 'columns' so you have to supply both indices.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DavidInLove
ID: 39955640
Still doesn't work:

Since I am not good at VBA maybe the way to fetch the data in array I did is wrong:




 Dim arrRecordArray As Variant
    Set rst = cnn.Execute(strSQL)
 arrRecordArray = rst.GetRows

MsgBox (arrRecordArray(1)(1))

Open in new window

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 39955646
MsgBox arrRecordArray(1, 1)

Open in new window

is how you specify both indices.
0
 

Author Closing Comment

by:DavidInLove
ID: 39958344
Ok Thanks to you.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now