[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - VBA - DAO

Posted on 2007-10-10
12
Medium Priority
?
1,693 Views
Last Modified: 2013-12-25
I need to get a recorset from Oracle into one and only one cell in Excel.  This will show a count of the top 5 incidents

Field 1             Field 2
Blackberry         5
Outlook              2

And so forth until 5 have been reached how do I get it to put this in one cell... For something which should be so easy its caused me nothing but headaches.  

0
Comment
Question by:BPMonk
  • 7
  • 5
12 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20048639
Why just one cell?  This will kill your ability to do analysis on the info.

If you can allow more than one cell to be populated, Excel has a very handy and easy
CopyFromRecordset method for the Range object...
0
 

Author Comment

by:BPMonk
ID: 20048692
Matthew I know how to copy the recordset for some reason they want to see this as a mini list of the top 5 incidents.  and are not really bothered about analysis.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20048762
BPMonk,

IMHO, your users are crazy for asking for this :)

Assuming you already have the top N results in a recordset object:

Dim StrFromRs As String

With rs
    .MoveFirst
    StrFromRs = "Field 1" & Space(8) & "Field 2"
    Do Until .EOF
        StrFromRs = StrFromRs & Chr(10) & rs.Fields(0) & _
            Space(15 - Len(rs.Fields(0)) & rs.Fields(1)
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing

With [a1]
    .Value = StrFromRs
    .WrapText = True
End With
Columns.AutFit
Rows.AutoFit

Regards,

Patrick
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:BPMonk
ID: 20048878
Ive basically got the query to output on specific location on the spreadsheet if thats what u mean, I seem to be getting errors here

StrFromRs = StrFromRs & Chr(10) & rs.Fields(0) & _
            Space(15 - Len(rs.Fields(0)) & rs.Fields(1)

Its showing red

Heres my code for getting the results


'Run the SQL
   
     StrSQL = "SELECT count (DISTINCT PROBSUMMARYM1.NUMBERPRGN) AS Occurance, PROBSUMMARYM1.PROBLEM_TYPE FROM PROBSUMMARYM1, PROBSUMMARYM2 WHERE PROBSUMMARYM1.NUMBERPRGN=PROBSUMMARYM2.NUMBERPRGN AND PROBSUMMARYM2.BARCLAYS_ORIGINATING_TEAM IN ('BDSINC','BDSIR') AND PROBSUMMARYM1.CLOSE_TIME BETWEEN TIMESTAMP '" & strstartdate & "' And TIMESTAMP '" & strdatefinish & "' GROUP BY PROBSUMMARYM1.PROBLEM_TYPE ORDER BY Occurance DESC"
   
    'Open connection to the database
     cnt.Open glob_sConnect
     
    'Open recordset based on Orders table
     rst.Open StrSQL, cnt
     
    'Set the Position on the spreadsheet
     Set posit = Worksheets("BDS+").Cells(50, NCell).Merge
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20049030
Keep the variables consistent; stick with rs or rst, but not both :)
0
 

Author Comment

by:BPMonk
ID: 20049124
I have mate, Im dumb but not that dumb lol, also I may get a data mismatch as one field is numerical the other text.  Just ran it and od get data msimatch herees the code

Dim cnt As New ADODB.Connection

    With cnt
   
    .ConnectionString = glob_sConnect
    .ConnectionTimeout = 20
    .CommandTimeout = 1000
   
    End With
   
    Dim rst As New ADODB.Recordset
    Dim StrSQL As String
    Dim posit As Range
    Dim MyDate As Integer
    Dim MCell As Integer
    Dim SCell As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim NCell As Integer
    Dim StrFromRs As String
   
    StrSQL = "SELECT count (DISTINCT PROBSUMMARYM1.NUMBERPRGN) AS Occurance, PROBSUMMARYM1.PROBLEM_TYPE FROM PROBSUMMARYM1, PROBSUMMARYM2 WHERE PROBSUMMARYM1.NUMBERPRGN=PROBSUMMARYM2.NUMBERPRGN AND PROBSUMMARYM2.BARCLAYS_ORIGINATING_TEAM IN ('BDSINC','BDSIR') AND PROBSUMMARYM1.CLOSE_TIME BETWEEN TIMESTAMP '" & strstartdate & "' And TIMESTAMP '" & strdatefinish & "' GROUP BY PROBSUMMARYM1.PROBLEM_TYPE ORDER BY Occurance DESC"
   
    cnt.Open glob_sConnect
    rst.Open StrSQL, cnt
   

    With rst
     
    .MoveFirst
   
    StrFromRs = "Field 1" & Space(8) & "Field 2"
   
    Do Until .EOF
   
           StrFromRs = StrFromRs & Chr(10) & rst.Fields(0) & Space(15 - Len(rst.Fields(0)) & rst.Fields(1))
           
        .MoveNext
    Loop
    .Close
   
End With

Set rst = Nothing

With [a1]
    .Value = StrFromRs
    .WrapText = True
End With


Columns.AutFit
Rows.AutoFit
   
    ''Set Variables
    MCell = 2
   
    '' Position the cells
   
    i = Day(Now())
    j = Day(strstartdate2)
    k = i - (i - j)
   
    NCell = MCell + k
   
    ''Run the SQL
   
     StrSQL = "SELECT count (DISTINCT PROBSUMMARYM1.NUMBERPRGN) AS Occurance, PROBSUMMARYM1.PROBLEM_TYPE FROM PROBSUMMARYM1, PROBSUMMARYM2 WHERE PROBSUMMARYM1.NUMBERPRGN=PROBSUMMARYM2.NUMBERPRGN AND PROBSUMMARYM2.BARCLAYS_ORIGINATING_TEAM IN ('BDSINC','BDSIR') AND PROBSUMMARYM1.CLOSE_TIME BETWEEN TIMESTAMP '" & strstartdate & "' And TIMESTAMP '" & strdatefinish & "' GROUP BY PROBSUMMARYM1.PROBLEM_TYPE ORDER BY Occurance DESC"
   
    'Open connection to the database
     'cnt.Open glob_sConnect
     
    'Open recordset based on Orders table
     'rst.Open StrSQL, cnt
     
    'Set the Position on the spreadsheet
     Set posit = Worksheets("BDS+").Cells(50, NCell).Merge
     
0
 

Author Comment

by:BPMonk
ID: 20049461
Im getting a type mismatch here
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20049623
BPMonk,

What line does the debugger jumo to?

Regards,

Patrick
0
 

Author Comment

by:BPMonk
ID: 20049645
Ok well I keep getting red with the &_ but when I put

StrFromRst = StrFromRst & Chr(10) & rst.Fields(0) & Space(15 - Len(rst.Fields(0)) & rst.Fields(1)

The red goes but it seems to be this causing the problem am I putting my SQL etc in the right places?

 
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 20049690
BPMonk said:
>>Ok well I keep getting red with the &_

You need a space between the ampersand and the underscore.

I can't see anything obviously wrong with the code...
0
 

Author Comment

by:BPMonk
ID: 20049713
I did that mate and also added a bracket I have it black now but still getting type mismatch.
0
 

Author Comment

by:BPMonk
ID: 20055562
Thanks Matt, I messed about with the String and got it to work...  Also helped me get my head around recordsets a bit which when you do its not so bad after all :)  Thanks A Million...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

834 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