Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 35 of 35

Thread: Cracking the ice

  1. #21
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Here you are Gert Jan. I have had to change the filenames and I have used a table name

    [vba]


    Const adOpenForwardOnly As Long = 0
    Const adLockReadOnly As Long = 1
    Const adCmdText As Long = 1

    Public Sub GetData()
    Dim oConn As Object
    Dim oRS As Object
    Dim sFilename As String
    Dim sConnect As String
    Dim sSQL As String
    Dim i As Long

    sFilename = "C:\Test\SQL Reading Excel.xls"
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFilename & ";" & _
    "Extended Properties=Excel 8.0;"

    sSQL = "SELECT * FROM StateData WHERE Region='South'"

    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
    adLockReadOnly, adCmdText

    If Not oRS.EOF Then
    For i = 1 To oRS.fields.Count
    ActiveSheet.Cells(1, i).Value = oRS.fields(i - 1).Name
    Next i
    ActiveSheet.Range("A2").CopyFromRecordset oRS
    Else
    MsgBox "No records returned.", vbCritical
    End If

    oRS.Close
    Set oRS = Nothing

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  2. #22
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Gert Jan
    Since the beginning of this week i started working with Hyperion (=Brio?) to get information from our database.
    Brio is my all time favourite app, but I haven't used the version since taken over by Hyperion (yes I know it was years ago). I still use a version from 2002, and it is absolutely great, so handy, it pivots and charts in a way Excel can only dream about.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #23
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    That's it Gert and it works fine. I wonder what the difference was in this file and the one I used. I had not changed the formatting and yours is set to General.

    To use thisworkbook path I am using this:
    [VBA]sFilename = ThisWorkbook.Path & "\SQL Reading Excel.xls"[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #24
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    Works perfect Bob, thanks

    Steve, I copied it into a texteditor, replaced the spaces in the State-names with a underscore and removed
    the comma's (we use it for decimals over here) and finally imported it into Excel.

  5. #25
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    Another question Bob,

    Is it correct that the workbook, on wich one performs a query, always must have the data in a named range?

  6. #26
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No, you can query the whole sheet, or a range within the sheet (your example was the latter).

    A named range is just more flexible because it then acts like a table in a DB, and you can refer to clumn names.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #27
    Hi Guys, sample codes here are great. How about executing a query command records with the same macro file and not from the external file, is it possible?

    Thanks.

  8. #28
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    Hi joprotus,

    I'm not sure what you mean, but it seems that you're looking for the autofilter?

    Gert Jan

  9. #29
    VBAX Regular Gert Jan's Avatar
    Joined
    Oct 2006
    Location
    Houten
    Posts
    62
    Location
    Steve,
    If you're still here...
    Yesterday i tried running the code on a file at my work. I kept on getting a message that the file was an unexpected format.
    It took me a while before i realized that it was an excel generated by Hyperion.
    When i copied it, and pasted values into a new book, the problem was solved.
    Maybe something simular to what you experienced?

    Gert Jan

  10. #30
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I think you're right Gert, I have worked it out for now too....until something new comes along to throw a spanner in the works.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  11. #31
    Hi Gert Jan,

    I have two worksheets in a single workbook. The first worksheet has a Sales Order Header Database and the second has the SO Details. I am doing a macro program in the same file and I want to know if it's possible to execute a join query command linking the two sets of records together.

    Thanks,
    Joprotus

  12. #32
    Quote Originally Posted by malik641
    (especially the Unleashed book...I mean, I turned to like 1300th page and just started reading about clustered indices and I understood what they were talking about fairly well)
    Ok boy genius..... that is like Bob casually whipping out a little Latin to go w/ that burger and fries.....

    Indices, clusters, SQL... HUH????????

    Cheers mate, and great job getting this forum going here!
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  13. #33
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by YellowLabPro
    Ok boy genius..... that is like Bob casually whipping out a little Latin to go w/ that burger and fries.....
    I can't compare with Bob. That guy is on a-whole-nother level that I hope to achieve one day

    I mean, I could whip out my spanish skillz, but then I would just look like a poseaur.

    Quote Originally Posted by YellowLabPro
    Cheers mate, and great job getting this forum going here!
    Thanks Doug.
    I'm just glad there are people here who are as excited about the new forum as I am.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  14. #34
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by malik641
    Thanks Doug.
    I'm just glad there are people here who are as excited about the new forum as I am.
    It has the potential to be a great forum Joseph, one in which people can really extend; you are to be commended for your initiative.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #35
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by xld
    It has the potential to be a great forum Joseph, one in which people can really extend; you are to be commended for your initiative.
    Thanks, Bob. That means a lot to me




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •