Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Returning data from a closed workbook to a variable

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Guest

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •