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.

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.

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!