Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Connect to sql server

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone knows how i can get excel to connect to an as400 (sql server)
    i have in column a several productnumbers and i want that when the user pushes a button that the description price etc will be put in the columns b,c etc..
    the buuton should perform a sql statement like select description price from ... where artnumber = "value cell a1"

    help please

  2. #2
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i already have this code but by the line where (f0101.aban8 = x) must x be the value of the activecell but how can i do this

    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=proddta;", _
    Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT F0101.ABALPH" & Chr(13) & "" & Chr(10) & "FROM AS400GOFI.PRODDTA.F0101 F0101" & Chr(13) & "" & _
    Chr(10) & "WHERE (F0101.ABAN8 = X)" _
    )
    .Name = "Query from proddta"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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