Connect to sql server

Herman · Mar 7th, 2002, 03:38 AM

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

Herman · Mar 7th, 2002, 05:31 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 03:38 AM	#1
Herman New Member Join Date: Feb 2002 Posts: 28	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

Mar 7th, 2002, 05:31 AM	#2
Herman New Member Join Date: Feb 2002 Posts: 28	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