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

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
.