Using SQL with VBA

[eoinymc]

Hi,

I was wondering if anyone out there can help me!

Currently, when I want to pull data from my Oracle database, I use Microsoft Query.

I click on Data -> From Other sources -> From Microsoft Query. I choose my data source (Microsoft ODBC for Oracle Connect) and then enter they SQL query using the Microsoft Query editor. This pulls the required data into my spreadsheet.

Can anyone help me recreate this using VBA? I know it's possible, but just can't seem to find how to do it by googling!

My connections string is: DSN=CATL1;UID=CATL1_user;PWD=abc123;SERVER=LIVE01;

A simple sample query that I would use is:

select fname, lname, sum(total_spend) from user_spend
where spend_month <= 3
and account_num in ('TL234', 'TL245', 'TL567')

Any help would be greatly appreciated.

If you need anymore information, please let me know.

Thanks,

Eoin

 

[Gary McMaster]

Just record a macro while creating a query by hand. The recorded macro should give you a good start.

Gary

 

[ogo]

Hi you can actually try it using OracleInProcessServer. Example below

Public objSession As Object
Public objDataBase As Object
Sub ConnectToOracle()
'Create a reference to the OO4O dll
Set objSession = CreateObject("OracleInProcServer.XOraSession")
'Create a reference to database
Set objDataBase = objSession.OpenDatabase("DATABASENAME", "USERNAME/PASSWORD", 0&)
'Note: to get DATABASENAME execute the query select * from global_name
Dim OraDynaSet As Object
'Write the query. Here i am selecting only 2 columns
strSQL = "SELECT NAME,SALARY FROM EMPLOYEE"
Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)
'To know the number of rows returned by the query
RowCount = OraDynaSet.RecordCount
        
        'If there are records retrieved
        OraDynaSet1.MoveFirst
        
        'Loop the recordset for returned rows
        For i = 1 To OraDynaSet1.RecordCount
            'Put the results in different columns in Sheet1
            'Since i am selecting only two columns two field value will be returned
            Sheet1.Cells(i, 1) = OraDynaSet.Fields(0).Value
            Sheet1.Cells(i, 2) = OraDynaSet.Fields(1).Value
        OraDynaSet1.MoveNext
        Next i
End Sub

Thanks,

Ogo

 

[ogo]

Oracle tutorial:

http://download.oracle.com/docs/cd/B28359_01/win.111/b28378/quicktour.htm


Thanks,
Ogo

 

[eoinymc]

Thanks guys...

Ogo, I am getting a messagebox saying "Object required" when I run your code substituting in for my db, etc... Unfortunately, I don't really know what it's looking for. Any ideas?

Gary, I have recorded a macro and it works fine. However, when I want to create a new macro using parameters, I can't seem to do it..

So, when I create it the query string is:

.CommandText = Array( _
        "SELECT n.fname, n.fname, sum(total_spend)" & Chr(13) & "" & Chr(10) & "FROM user_spend n" & Chr(13) & "" & Chr(10) & "WHERE (n.spend_month <= 3) AND (n.account_num In (('TL234', 'TL245', 'TL567'))" _
        )

But, when I try to use a parameter in Sheet2, cell A1, I insert it into the code and it doesn't work:

.CommandText = Array( _
        "SELECT n.fname, n.fname, sum(total_spend)" & Chr(13) & "" & Chr(10) & "FROM user_spend n" & Chr(13) & "" & Chr(10) & "WHERE (n.spend_month <= 3) AND (n.account_num In (" & Worksheets("Sheet2").Range("A1") & "))" _
        )

Any idea what I'm doing wrong?

Cheers,

Eoin

 

[Gary McMaster]

Eoin,

The parameters in your first statement have a leading and trailing apostrophe ('TL234'). Your 2nd statement does not.

I guess the first thing to try would be:

& "'" & Worksheets("Sheet2").Range("A1") & "'"

Also, you may not be accessing the right sheet if you have more than 1 workbook open. You might also try further qualifying the worksheet with "ThisworkBook.Worksheets".

One other thing to try if both the above fails is to use Worksheets("Sheet2").Range("A1").Text (using "Text" property). Maybe formulas or formatting are causing a problem.


Gary

 

[eoinymc]

Thanks Gary,

Unfortunately, none of those options worked.

I'm still getting the same error each time.

Any other ideas?

Do you think I should be able to reference cells like I'm doing in the middle of this query or should I be doing something else?

It's really annoying me that I can't get it to work!!!

Cheers,

Eoin

 

[Ruddles]

<deleted>

 

[RoryA]

You can't simply put an In list in one cell and pass that. You need to put the individual values into separate cells, then use functions like this:

Function GetStringInList(rngCriteria As Range) As String
   Dim rngCell As Range
   Dim strTemp As String
   For Each rngCell In rngCriteria.Cells
      If Len(rngCell.Value) > 0 Then strTemp = strTemp & ",'" & DoubleQuotes(rngCell.Value) & "'"
   Next rngCell
   ' now strip off leading comma
   GetStringInList = Mid$(strTemp, 2)
End Function
Function DoubleQuotes(strIn As String) As String
   DoubleQuotes = Replace(strIn, "'", "''")
End Function

then your code becomes (I removed the Array as I find it annoying to work with):

.CommandText = "SELECT n.fname, n.fname, sum(total_spend) FROM user_spend n WHERE (n.spend_month <= 3) AND (n.account_num In ((" & GetStringInList(Sheets("Sheet2").Range("A1:A3") & "))"

for example.

 

[Gary McMaster]

Maybe you don't need any code at all. Below is a link that shows how to create a "Parameter Query". You can force the parameter query to get the filter data from cells that you assign without any code. There is also some info on parameter queries in MSQuery help.

http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP005199548.aspx

Also google "Excel Parameter Query".

Gary