Close Excel from Outlook. 1

[PerlIsGood]

Ok, I've searched both this forum and the VBA forum looking for the right syntax to close an Excel workbook from an Outlook form - so far every solution I've tried fails because Outlook is telling me it doesn't support that method { errrgh }.

Here's what I have which currently works:

    Set fso = CreateObject("Scripting.FileSystemObject")
    FileLoc = NetPath & FormFiles & "cand_expense.xls"
    
        If fso.FileExists(FileLoc) Then
            On Error Resume Next
            Set oExcel = GetObject(, "Excel.Application")
            If Err.Number <> 0 Then
                Err.Clear
                Set oExcel = CreateObject(&quot;Excel.Application&quot;)
            End If
        Else
            strErr = FileLoc & &quot; could not be found.&quot;
            MsgBox (strErr), vbCritical, &quot;Error: File Not Found&quot;
            Exit Function
        End If

    oExcel.Workbooks.Open (FileLoc)
    oExcel.Visible = False

    varRow = 1
    Do While oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 1) > &quot;&quot;
        If oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 1) = txtCandName Then
            Exit Do
        End If
        varRow = varRow + 1
    Loop

    oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 1).Value = txtCandName
    oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 2).Value = txtAirCost
    oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 3).Value = txtTrainCost
    oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 4).Value = txtLimoCost
    oExcel.Worksheets(&quot;Sheet1&quot;).Cells(varRow, 5).Value = txtHotelCost

    For Each oWorkbook in oExcel.Workbooks
        oWorkbook.Save
    Next

    oExcel.Quit
    Set oExcel = Nothing

End Function

The last 4 lines work, but I don't want to shut Excel down when people might be using it for other purposes - I only want to close the workbook that I've modified. I've tried oExcel.Close and oExcel.ActiveWorkbook.Close, but both cause an error.

Any ideas would be greatly appreciated

 

[jonscott8]

Change

oExcel.Workbooks.Open (FileLoc)

to

Set oMyBook = oExcel.Workbooks.Open(Fileloc)

to give yourself a definitive reference to the workbook you open.

Change

For Each oWorkbook in oExcel.Workbooks
  oWorkbook.Save
Next

oExcel.Quit
Set oExcel = Nothing[/code]

to

oMyBook.Save
oMyBook.Close
Set oMyBook = Nothing
Set oExcel = Nothing

Also, you're setting Excel's visible property to False - don't forget to set it back to True before you're script ends. If you dont and the user does have Excel open prior to your script executing, Excel will disappear once your script runs leaving your user wondering "where'd it go?". Jon Hawkins

 

[PerlIsGood]

Thx! That was exactly what I needed. Especially the .Visible part - that probably would have taken me a bit to figure out why the program kept disappearing yet stayed in the active program list. heh.