?
Solved

GEt SQL data from Excel VBA

Posted on 2010-01-05
5
Medium Priority
?
434 Views
Last Modified: 2012-11-18
I've done a bit of googling but i can't find anything that seems usable.

We have a SQL Database and i'm trying to get Table Information from it.

Server Name:      QCSVR02
Password ect.:    Windows Authentication
DataBase Name: QCLIVE
Table Name:        Quick Corporate$Salesperson_Purchaser

If its any help we also use MS Dynamics Nav (Navision)
There are several Columns that i would need to filter by, but that would be optional.

Any starting points?

I'm reasonably good with VBA, not so much in SQL
0
Comment
Question by:bromy2004
  • 3
  • 2
5 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 2000 total points
ID: 26187374
You said youre home at VBA

So here goes, create a new module, paste my code, and call it in any way you prefere.

I paste from row 2 to allow for VBA macro to write column names (hard coded).

/Marten
Sub DataExtract()

' Create a connection object.
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=QCSVR02;INITIAL CATALOG=QCLIVE;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cn.Open strConn

' Create a recordset object.
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

With rs
    ' Assign the Connection object.
    .ActiveConnection = cn
    ' Extract the required records.
    .Open "SELECT * FROM [Quick Corporate$Salesperson_Purchaser]"
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A2").CopyFromRecordset rs
    
    ' Tidy up
    .Close
End With

cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Open in new window

0
 
LVL 10

Author Comment

by:bromy2004
ID: 26187399
Thanks Martin,

However it didn't populate any data.
There is definitely data in the table (28 Rows)
and the Field Names are populated correctly.

RecordCount=-1
0
 
LVL 10

Author Comment

by:bromy2004
ID: 26187439
I should clarify.
the field names in the rs variable are visible and are correct.
nothing was populated in Excel
0
 
LVL 10

Author Comment

by:bromy2004
ID: 26187733
Got it.

RecordCount doesn't populate for some reason.
i used rs.MoveNext

For anyone else who wants to know.
See attached Code.
Function DataExtract(Optional Location As Location) As SalesRep
Dim cn        As ADODB.Connection
Dim i         As Integer
Dim strConn   As String
Dim ws        As Worksheet
Dim rs        As ADODB.Recordset
Dim MyArray() As SalesRep
Dim strSQL    As String


strConn = "PROVIDER=SQLOLEDB;PERSIST SECURITY INFO=True;DATA SOURCE=QCSVR02;INITIAL CATALOG=QCLIVE;Workstation ID=SALES010;Use Encryption for Data=False;INTEGRATED SECURITY=sspi;"

strSQL = "SELECT [CODE],[NAME],[GLOBAL DIMENSION 1 CODE] FROM [Quick Corporate$Salesperson_Purchaser] Where [Global Dimension 2 Code] <>''"
If Location = Nothing Then
strSQL = strSQL & " and [GLOBAL DIMENSION 1 CODE] = " & Location
End If


'Create Connection
Set cn = New ADODB.Connection
'Open Connection
cn.Open strConn


'New Worksheet
Set ws = ActiveWorkbook.Worksheets.Add

'Load Records
Set rs = New ADODB.Recordset

'Populate Array
With rs
  .ActiveConnection = cn
  .Open "SELECT * FROM [Quick Corporate$Salesperson_Purchaser]"
  While Not rs.EOF
  i = i + 1
  ReDim Preserve MyArray(1 To i)
    MyArray(i).Location = LocationEnum(.Fields("Global Dimension 1 Code").Value)
    MyArray(i).Num = .Fields("Code").Value
    MyArray(i).Name = .Fields("Name").Value
  .MoveNext
  Wend
End With

CleanUp:
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function

Open in new window

0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 26188338
Glad to help

Good you figured it out.

/Marten
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

571 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