Returning data from a closed workbook to a variable

[Pauly]

Hi,
I've been using the following code to find a name and return the associated address from a closed file to automatically address email in Outlook. The result of the search is currently placed in a cell on the activeworksheet but I want it to return to a variable or put it straight into the To box in Outlook.
I've found several other methods but none incorporate the 'find' function I require and all my efforts to get it to work have failed.

I want it to work in Excel 97 & 2000 and would prefer not to use ActiveX unless it can be programmatically activated.

Any help greatly appreciated.

Pauly

Here's the current code:

Sub GetClientAddress()

Dim strPath As String
Dim strFile As String
Dim connstring As String
Dim sqlstring As String
Dim strClientName As String

strClientName = ActiveSheet.Range("b4")
strPath = frmSetup.TxtMasterFolder & "/" 'important to have the / added
strFile = "Client Addresses.xls"

connstring = _
"ODBC;DBQ= " & strPath & strFile & ";DefaultDir=" & strPath & ";Driver={Microsoft Excel Driver (*.xls)}"

sqlstring = _
"SELECT Addresses.`Client Address` FROM Addresses Addresses WHERE (Addresses.`Client Name`='" & strClientName & "')"

With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False

End With

End Sub


[ This Message was edited by: pauly on 2002-02-17 13:39 ]

On 2002-02-17 03:42, Pauly wrote:
Hi,
I've been using the following code to find a name and return the associated address from a closed file to automatically address email in Outlook. The result of the search is currently placed in a cell on the activeworksheet but I want it to return to a variable or put it straight into the To box in Outlook.
I've found several other methods but none incorporate the 'find' function I require and all my efforts to get it to work have failed.

Any help greatly appreciated.

Pauly

Here's the current code:

Sub GetClientAddress()

Dim strPath As String
Dim strFile As String
Dim connstring As String
Dim sqlstring As String
Dim strClientName As String

strClientName = ActiveSheet.Range("b4")
strPath = frmSetup.TxtMasterFolder & "" 'important to have the added
strFile = "Client Addresses.xls"

connstring = _
"ODBC;DBQ= " & strPath & strFile & ";DefaultDir=" & strPath & ";Driver={Microsoft Excel Driver (*.xls)}"

sqlstring = _
"SELECT Addresses.`Client Address` FROM Addresses Addresses WHERE (Addresses.`Client Name`='" & strClientName & "')"

With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False

End With

End Sub