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" 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=daniel1" _ , 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
Try something like:
ActiveSheet.Range("L1").Value Make sure that the value in the cell is of correct type though (it's not a date or stuff like that). Skip -----Original 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" 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=sqlexe c;S RV R=tellus_tli;PRO=onsoctcp;UID=btsln;PWD=daniel1 " _ , 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 . |
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 | |||
How do I reference Cell information in my Headers? | Excel Discussion (Misc queries) |