Results 1 to 9 of 9
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Dial a telephone number (Excel 2000)

    I have been asked to look into getting Excel to dial telephone numbers.

    A worksheet will have a list of customers down one column, and their phone numbers in the next column.

    I would like an operator to be able to work down the column ringing each customer in turn.
    Regards
    John



  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dial a telephone number (Excel 2000)

    John,

    Are you tele-marketing?

    Jim Cone
    San Francisco, CA

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dial a telephone number (Excel 2000)

    It shuld NOT have to be in a normal module and won't really do anything there. It is a code that is triggered by a dbl-click event of the workbook and ONLY needs to be in the thisworkbook module.

    It assumes you have a program called DIALER. You might have to include the path to the dialer program if the computer can not find a path to it.

    Steve

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dial a telephone number (Excel 2000)

    No, I am not a telemarketer.

    My client is actually a baker, who takes daily orders from a lot of supermarkets.
    Each day they ring each supermarket to get their order, and put the figures into Excel.
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dial a telephone number (Excel 2000)

    Thanks Steve

    Dialer is a Communications Accessory that is included with Win98. For me it works OK without the path.

    Do you know if later versions of Windows include it?

    It seems that I need to close Dialer after each call. If I leave it running, and double-click the next phone number it doesn't work.

    By including this code I can tell if it is already running. What I would now like is some code that will close it.
    <pre>Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Function IsOpen(strCaption As String) As Boolean
    IsOpen = FindWindow(vbNullString, strCaption)
    End Function
    </pre>


    I then need this in my doubleclick event:
    <pre>If IsOpen("Phone Dialer") Then
    MsgBox ("Open")
    End If
    </pre>


    What I need now is to replace msgbox("Open") with some code that will close it.
    Regards
    John



  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dial a telephone number (Excel 2000)

    I found an answer to my question at http://www.excel-center.com/mailing/133901h.htm .

    <pre>
    'Place the code below into the worksheet module
    Private Sub Worksheet_BeforeDoubleClick( _
    ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 1 Then Exit Sub
    Cancel = True
    Application.SendKeys ActiveCell.Value & "~"
    Shell "Dialer", vbNormalFocus
    End Sub

    'Place the code below into the standard module
    Private Sub Worksheet_BeforeDoubleClick( _
    ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 1 Then Exit Sub
    Cancel = True
    Application.SendKeys ActiveCell.Value & "~"
    Shell "Dialer", vbNormalFocus
    End Sub
    </pre>


    This code assumes that the phone numbers are in column A.

    It is not clear to me why I should put the code into both the worksheet and standard module.
    Regards
    John



  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dial a telephone number (Excel 2000)

    John,

    As Steve pointed out, I had to add the file path to your code, but it did work.

    Below is some similiar code I modified and have used and it might be a little more suited for a production environment.
    You must add a "Button" to the sheet with the telephone numbers and attach the code to it.
    It worked for me on Windows NT and on WindowsXP, but note that the file path to the 'dialer' is different for each OS.
    Also, I have both the NT 'dialer' and XP 'dialer' on my system and the NT version worked best.
    You may have to experiment with the send keys code...
    '------------------------------------------------------
    Sub CellToDialer() 'John Walkenbach
    ' Transfers active cell contents to Dialer
    ' And then dials the phone
    ' Modified by Jim Cone on Jan 18, 2002 and Apr 19, 2003

    Dim CellContents As String
    Dim AppName As String
    Dim AppFile As String
    Dim TaskID As Variant

    ' Get the phone number
    CellContents = ActiveCell.Value
    If Len(CellContents) < 7 Then
    MsgBox "Select a cell that contains a phone number.", vbInformation
    Exit Sub
    End If

    ' Activate (or start) Dialer
    AppName = "Dialer"
    ' USE THE FULL FILE PATH
    ' AppFile = "C:Program FilesWindows NTdialer.exe"
    AppFile = "C:Old Telephone Dialerdialer.exe"
    On Error Resume Next
    ' This activates the dialer if it showing on the desktop
    AppActivate (AppName)

    If Err.Number <> 0 Then
    Err = 0
    ' This activates the dialer by opening(running) the file
    TaskID = Shell(AppFile, vbNormalFocus)

    If Err <> 0 Then
    MsgBox "Can't start " & AppFile, vbExclamation
    Exit Sub
    End If

    End If

    ' Transfer cell contents to Dialer
    Application.SendKeys "%n" & CellContents, True

    ' Click Dial button
    Application.SendKeys "%d"


    ' Selects the next telephone number in the column.
    ActiveCell(2, 1).Select
    ' The macro button must now be clicked twice to dial the next
    ' number in the column.
    End Sub
    '---------------------------------------------------------------------------------

    Regards,
    Jim Cone
    San Francisco, CA

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dial a telephone number (Excel 2000)

    If you send the Alt F4 with sendkeys it should shut it down:

    SendKeys "%{F4}", True

    If it needs something other than Alt-F4, send that to the program.

    Steve

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Dial a telephone number (Excel 2000)

    Thanks to Jim and Steve

    I am sure I now have enough to get this to work.

    I think the use of AppActivate (AppName) removes the need to close down dialer after each call, but I will experiment with both approaches.

    I am also not yet sure whether it is best to click a command button , or double click a cell.
    Regards
    John



Posting Permissions

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