11 Replies - 908 Views - Last Post: 05 June 2016 - 09:50 AM

#1 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 07:39 AM

Hello again,

So, i have only one question:
How can i get the data from Connection.Execute?
My Prof give me this mission.
Get the data from the excel sheet without using recordset.
Only use the Connection Object ??????????
My Code:
Sub Aufgabe3_OLEDB()
    Dim cnn As New ADODB.Connection
    Dim strMsg As String
    
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & CurrentProject.Path & "\Bestellungen.xlsx;" & "Extended Properties=""EXCEL 12.0; HDR=Yes;"";"
        .Open
        .Execute "SELECT * FROM [Bestellliste$]", , adCmdText
    End With
End Sub



I have no idea, how can i get the data from the connection object.
i shall post the data via msgbox, i tested everything that i know, but nothing helps.

Kindly Regards

Is This A Good Question/Topic? 0
  • +

Replies To: Connection.Execute reading Excel Sheet

#2 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,584
  • Joined: 12-December 12

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 08:36 AM

Is this an assignment question? Are you being assessed for it?
Was This Post Helpful? 0
  • +
  • -

#3 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 08:41 AM

No No, only exercises.

What if i use
 Set rs = .execute()
to read the excel data.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,584
  • Joined: 12-December 12

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 08:43 AM

I thought you weren't supposed to use a recordset?
Was This Post Helpful? 0
  • +
  • -

#5 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 08:45 AM

I called my prof and i misunderstood him, i can use recordset, but i shall use the connection object for reading data from the excel sheet.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,584
  • Joined: 12-December 12

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 08:51 AM

Ah well, I can post my results then:

Public Sub TestImportInto_Excel()
    Dim conn As ADODB.Connection
    
    Set conn = CurrentProject.Connection
    
    'conn.Execute "INSERT INTO tblStaffData SELECT * FROM " _
    '    & "[Excel 12.0;DATABASE=C:\Users\Andrew\Documents\AndysData.xlsx].[staffData]"
    '(staffData is a named range, or use the [sheet name$])
    
    'or SELECT * INTO for a new table
    conn.Execute "SELECT * INTO tblNewStaffData FROM " _
        & "[Excel 12.0;DATABASE=C:\Users\Andrew\Documents\AndysData.xlsx].[staffData]"
    
    Set conn = Nothing
End Sub

These populate a table though, they don't (immediately) allow output in a MsgBox, that would require a recordset (or a few more steps).

This doesn't answer your revised version of the question though ;), but you already know how to obtain a recordset, you just need to populate it with Excel data. Good luck. (Bookmark connectionstrings.com.)
Was This Post Helpful? 0
  • +
  • -

#7 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 09:04 AM

Hello again,

So my Result is working, but it looks very bad. The Msgbox is totally unsorted.

Sub Aufgabe3_OLEDB()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strHeader As String
    
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & CurrentProject.Path & "\Bestellungen.xlsx;" & "Extended Properties=""EXCEL 12.0; HDR=Yes;"";"
        .Open
    Set rs = .Execute("SELECT Kategoriename, Count([Anzahl]) AS Anzahl, Sum([Einzelpreis]) AS Summe FROM [Bestellliste$] GROUP BY Kategoriename ", , adCmdText)
    With rs
        If Not .EOF Then
            strHeader = strHeader & "Kategoriename:" & vbTab & "Anzahl:" & vbTab & "Summe:"
        End If
        While Not .EOF
            strHeader = strHeader & !Kategoriename & !anzahl & !Summe
            .MoveNext
        Wend
    End With
    End With
    
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    
    MsgBox strHeader, vbInformation
End Sub


Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,584
  • Joined: 12-December 12

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 09:08 AM

You haven't specified a sort order, so there is no guarantee about what order the records will be returned in. Your book or tutorial will tell you how to order records.
Was This Post Helpful? 0
  • +
  • -

#9 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 09:32 AM

My mistake, i mean this:Attached Image

This post has been edited by Bonekit: 05 June 2016 - 09:33 AM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,584
  • Joined: 12-December 12

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 09:41 AM

Do you mean it is not aligned neatly, in columns? You could try using functions like Space(), Left(), Right(), Format() to try and format each line but it still won't align accurately because the MsgBox doesn't use a monospaced font. If you need this feature then you'll have to create your own UserForm (or Access Form) version of a MsgBox so that you could use a monospaced font.
Was This Post Helpful? 1
  • +
  • -

#11 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 09:48 AM

Perfect, you are the best!
With Space(5) it looks great, not the best solution i find, but better than nothing i think.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,584
  • Joined: 12-December 12

Re: Connection.Execute reading Excel Sheet

Posted 05 June 2016 - 09:50 AM

Not the best, maybe second best ;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1