Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep cell reference when picking up information from another database
I want to pick up information from another database and
maka a SQL question related to a cell in my sheet. At recording the macro it took the information in the cell, but not the reference to the cell itself. I changed that reference to =Range('L1') and that was OK, but then I got a "SQL syntaxproblem" at running the macro. This line was the "problemline" ..Refresh BackgroundQuery:=False How can I solve this problem? Sheets("Art.").Select Range("L5").Select With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=p_bt;DB=p_bt;HOST=tellus;SERV=sqlexec;SR V R=tellus_tli;PRO=onsoctcp;UID=btsln;PWD=dani" _ , Destination:=Range("M4")) .Sql = Array( _ "SELECT art.art_no, art.art_descr" & Chr(13) & "" & Chr(10) & "FROM p_bt:guda.art art" & Chr(13) & "" & Chr (10) & "WHERE (art.art_no=range('L1')" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With End Sub If anyone could help me please reply! /Samuel. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep cell reference when picking up information from another database
I would suggest that you change
"WHERE (art.art_no=range('L1')" to "WHERE (art.art_no=" & range("L1") "Samuel Lindgren" wrote in message ... I want to pick up information from another database and maka a SQL question related to a cell in my sheet. At recording the macro it took the information in the cell, but not the reference to the cell itself. I changed that reference to =Range('L1') and that was OK, but then I got a "SQL syntaxproblem" at running the macro. This line was the "problemline" .Refresh BackgroundQuery:=False How can I solve this problem? Sheets("Art.").Select Range("L5").Select With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=p_bt;DB=p_bt;HOST=tellus;SERV=sqlexec;SR V R=tellus_tli;PRO=onsoctcp;UID=btsln;PWD=dani" _ , Destination:=Range("M4")) .Sql = Array( _ "SELECT art.art_no, art.art_descr" & Chr(13) & "" & Chr(10) & "FROM p_bt:guda.art art" & Chr(13) & "" & Chr (10) & "WHERE (art.art_no=range('L1')" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With End Sub If anyone could help me please reply! /Samuel. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep cell reference when picking up information from another database
Hello!
I tried according to your suggestion (see below) but got a wrong message .Sql = Array( _ "SELECT art.art_no, art.art_descr" & Chr(13) & "" & Chr(10) & "FROM p_bt:guda.art art" & Chr(13) & "" & Chr(10) & "WHERE (art.art_no=& range "H1")" _ ) .FieldNames = True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Picking specific rows from database | Excel Discussion (Misc queries) | |||
Conditional Vlookup - Cherry picking information from the table ar | Excel Discussion (Misc queries) | |||
Is it possible to use a cell reference to define the database? | Excel Worksheet Functions | |||
information function - does cell contain reference | Excel Worksheet Functions | |||
Keep cell reference when picking up information from another database | Excel Programming |