Results 1 to 9 of 9
  1. #1
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17

    Question Answered: Data Select (selprof.exe)

    Just wondering - is anyone else familiar with Data Select? I use it in my current job as a quick and dirty interface from the iSeries to Office applications. I was trying to automate a process that involves passing parameters to a query, but all my attempts failed. (Either it passed the parameters but did not export the data, or it would export data but only from a version of the query that didn't require parameters.)

    If anyone has any experience of controlling it via VBA, please let me know!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  2. Best Answer
    Posted by Davej100

    "This is a simple example of the code that I use to pass parameters to a Dataselect query and have it pass the data back to Excel.
    The parameter for the Start Date in Dataselect is #STARTDATE# but in the code below this parameter (QQPARM1) is #STARTDATE
    I am using Dataselect version 6.31. I find Dataselect does not like long file names, even in the title of the Excel file that you are bringing the data into.
    I hope you have success. If not then please get back to me.

    Sub GetData()
    Worksheets("Data").Range("A6:H500").Value = ""
    StartDate = (Worksheets("Data").Range("D2"))
    If StartDate = "" Then
    Exit Sub
    End If
    On Error Resume Next
    MyAppID = Shell("C:\SELPROF6\SELPROF /F", vbMinimizedNoFocus)
    On Error GoTo 0
    channel1 = Application.DDEInitiate(app:="Selprof", topic:="System")
    QQWorkbook = "MixUsage.xlsm"
    QQSheet = "Data"
    QQPARM1 = "#STARTDATE"
    QQPATH = "U:\Share\Queries\Public\planVuse.wfs"
    QQCELL = "A6"
    QQDDECall = "[dest(dde,excel,[" & QQWorkbook & "]"""
    QQDDECall = QQDDECall & QQSheet & """)]["
    QQDDECall = QQDDECall & QQPARM1 & "="
    QQDDECall = QQDDECall & StartDate & "]["
    QQDDECall = QQDDECall & "exec(" & QQPATH & "," & QQCELL & ")]"
    Application.DDEExecute channel1, QQDDECall
    Application.DDETerminate channel1
    End Sub"


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    Didn't think so!

    It was for a "nice to have" piece of work, so never mind.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Dec 2016
    Posts
    3
    Provided Answers: 1
    Hi
    I don't think my first reply submitted correctly as it's not showing up. Anyway I use Dataselect extensively to query our AS400 system using Excel and VBA. I will help you if I can. I hope you haven't given up on this thread
    Regards
    Dave

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    Wow!

    Yes, please - any help will be gratefully received! What information do you need to start with?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #5
    Join Date
    Dec 2016
    Posts
    3
    Provided Answers: 1
    This is a simple example of the code that I use to pass parameters to a Dataselect query and have it pass the data back to Excel.
    The parameter for the Start Date in Dataselect is #STARTDATE# but in the code below this parameter (QQPARM1) is #STARTDATE
    I am using Dataselect version 6.31. I find Dataselect does not like long file names, even in the title of the Excel file that you are bringing the data into.
    I hope you have success. If not then please get back to me.

    Sub GetData()
    Worksheets("Data").Range("A6:H500").Value = ""
    StartDate = (Worksheets("Data").Range("D2"))
    If StartDate = "" Then
    Exit Sub
    End If
    On Error Resume Next
    MyAppID = Shell("C:\SELPROF6\SELPROF /F", vbMinimizedNoFocus)
    On Error GoTo 0
    channel1 = Application.DDEInitiate(app:="Selprof", topic:="System")
    QQWorkbook = "MixUsage.xlsm"
    QQSheet = "Data"
    QQPARM1 = "#STARTDATE"
    QQPATH = "U:\Share\Queries\Public\planVuse.wfs"
    QQCELL = "A6"
    QQDDECall = "[dest(dde,excel,[" & QQWorkbook & "]"""
    QQDDECall = QQDDECall & QQSheet & """)]["
    QQDDECall = QQDDECall & QQPARM1 & "="
    QQDDECall = QQDDECall & StartDate & "]["
    QQDDECall = QQDDECall & "exec(" & QQPATH & "," & QQCELL & ")]"
    Application.DDEExecute channel1, QQDDECall
    Application.DDETerminate channel1
    End Sub

  7. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    This looks subtly different to the code example that I was following. I'll give it a try and report back.

    Thanks again!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  8. #7
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    Thank you so much!

    I realised when comparing your code to mine that I had got hung up on using a Data Select macro as a function, and had created too many DS variables. Stepping through the section of your code that builds up QQDDECall, I worked out how to plug my variables from Excel into Data Select.

    I've now automated a colleague's weekly task (running 102 queries to Excel) and set it up so that he and both colleagues can create the report, and it completes in a couple of minutes!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  9. #8
    Join Date
    Dec 2016
    Posts
    3
    Provided Answers: 1
    I'm glad my code was of some help and you managed to sort it.

  10. #9
    Join Date
    Jun 2017
    Location
    Ukraine
    Posts
    11

    Data Select selprof exe

    can you possibly make an exe/downloadable version of GT and the Evil Factory?

Posting Permissions

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