Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search for Excel in different drives (Excel 97 & 2000)

    I have the following macro in my spreadsheet. We start this process within an Outlook form. It goes out and starts Excel and completes the work. If someone that has Excel loaded on their D: drive it will not work. How can I get it to check both c: and d: for the sotware?


    Sub Auto_Open()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:= _
    "securitytest2.xls ", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    Application.DisplayAlerts = True

    Ans = MsgBox("Do you want to continue to get Report?", vbYesNo)
    Select Case Ans
    Case vbYes
    With ActiveSheet.QueryTables.Add(Connection:= _
    "FINDER;J:EdgarSecurityQuery 2 from TestSecurity.dqy", Destination:=Range( _
    "A6"))
    .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

    Range("A1:F5").Select
    With Selection.Interior
    .ColorIndex = 41
    .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 2
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "Termination - 2001"
    With ActiveCell.Characters(Start:=1, Length:=18).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 22
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 2
    End With
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "Name"
    Columns("B:B").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "Hire Date"
    Columns("C:C").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Columns("C:C").EntireColumn.AutoFit
    Selection.ColumnWidth = 10.86
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "Term. Date"
    Columns("D").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Selection.ColumnWidth = 9.71
    Selection.NumberFormat = "mm/dd/yy"
    Columns("C:C").Select
    Selection.NumberFormat = "mm/dd/yy"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "Job Title"
    Columns("E:E").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "Department"
    Columns("F:F").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Rows("6:6").Select
    Selection.Delete Shift:=xlUp
    Range("A6").Select
    Selection.ColumnWidth = 15.29
    Columns("A:A").EntireColumn.AutoFit
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=Range("D6"), Order1:=xlDescending, Key2:=Range("D6") _
    , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
    Range("A6").Select
    Case vbNo
    '
    End Select
    Call Step2
    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    What happens when Excel is loaded on the D: drive? Does Excel fail to start? Does the code in your message fail somehow? If so, how?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    When we start Excel from the Icon buttons it works fine and it will start all other spreadsheets. If we try working through the Outlook form it will just sit there and Excel will not start.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    The problem must lie in the code that starts Excel from the outlook form. Is it possible to post it ?

    Andrew C

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    Then the problem is most likely in the code in Outlook that starts Excel, not in the code in the Excel workbook. You should post that code in the Outlook forum.
    Legare Coleman

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    Hi Daniel,
    This doesn't answer your question (for that, as others have suggested, we'd need the code that starts Excel) but it may speed up this bit of code. As a general rule you should avoid selecting ranges etc. unnecessarily. You might like to try using this code instead (note: keep a copy of your original to make sure thius does exactly the same things!):
    <pre>Sub Auto_Open()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "securitytest2.xls ", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    Application.DisplayAlerts = True

    Ans = MsgBox("Do you want to continue to get Report?", vbYesNo)
    Select Case Ans
    Case vbYes
    With ActiveSheet.QueryTables.Add(Connection:= _
    "FINDER;J:EdgarSecurityQuery 2 from TestSecurity.dqy", _
    Destination:=Range("A6"))
    .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

    With Range("A1:F5")
    With .Interior
    .ColorIndex = 41
    .Pattern = xlSolid
    End With
    .Font.ColorIndex = 2
    End With
    With Range("D3")
    .FormulaR1C1 = "Termination - 2001"
    With .Characters(Start:=1, Length:=18).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 22
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 2
    End With
    End With
    With Range("B5")
    .FormulaR1C1 = "Name"
    End With
    With Columns("B:B")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    With Range("C5")
    .FormulaR1C1 = "Hire Date"
    End With
    With Columns("C:C")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    .NumberFormat = "mm/dd/yy"
    ' .EntireColumn.AutoFit - no point as you're about to set width
    .ColumnWidth = 10.86
    End With
    Range("D5").FormulaR1C1 = "Term. Date"
    With Columns("D")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    .ColumnWidth = 9.71
    .NumberFormat = "mm/dd/yy"
    End With
    Range("E5").FormulaR1C1 = "Job Title"
    With Columns("E:E")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Range("F5").FormulaR1C1 = "Department"
    With Columns("F:F")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Rows("6:6").Delete Shift:=xlUp
    'Range("A6").ColumnWidth = 15.29 - no point as you're about to autofit column A
    Columns("A:A").EntireColumn.AutoFit
    Range("A6").Select
    Range(Selection, Selection.SpecialCells(xlLastCell)).Sort Key1:=Range("D6"), _
    Order1:=xlDescending, Key2:=Range("D6"), _
    Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
    Case vbNo
    '
    End Select
    Call Step2
    End Sub
    </pre>


    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    This is a sample on how I call it up.

    '-=-=-=-=-=-=-=-=-=-
    'Call Excel
    Sub cmdExcel_click()
    Set objWSHShell = CreateObject("WScript.Shell")
    objWSHShell.Run("excel.exe securityMasterTerm.xls")
    end sub
    '-=-=-=-=-=-=-=-=-=-=-=-=-

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Excel in different drives (Excel 97 & 2000)

    I will look at it later today. I am always looking for ways to improve my learning. This was a quick and dirty report that they asked me to look at. Thank you for your help.

    Daniel

Posting Permissions

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