Opening/Closing Excel From Access - Undo Document Recovery? 1

[kjv1611]

For one Access database, I am opening at least 1 workbook, possibly several, at a time, and then closing them out when finished. This part works great.

However, what I am finding in testing is that the next time I open Excel, I get a long list of workbboks in the recovery pane which are those that were opened in VBA, and then closed.

Is there any way to ensure that the recovery panel doesn't list workbooks opened in code? I'd prefer to include it in the VBA code, rather than having to change options, so that it can be done in the background, rather than telling others how to do it.

Thanks for any info.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, here's apparently another piece I don't have correct, and I obviously wouldn't see it every time.

When the code closes Excel, I'm getting a message for some workbooks, asking me if I want to save changes... b/c the newer version of Excel updates the formulas for workbooks saved in older versions...

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

My code for opening and closing the workbooks so far:

    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant
[GREEN]'BLA BLA BLA[/GREEN]
    Dim appXl As Object: Set appXl = CreateObject("Excel.Application")
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    [GREEN]'BLA BLA BLA[/GREEN]

    With fd
      .AllowMultiSelect = True
      If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
          If InStr(vrtSelectedItem, ".xls") Then
            If wb Is Nothing Then
            Else
              Set wb = Nothing
            End If
            Set wb = appXl.Workbooks.Open(vrtSelecttedItem,,True,,,,True,,,False,False)
           [GREEN]'BLA BLA BLA[/GREEN]
        End If
      End If
    End With

    If ws Is Nothing Then
    Else
        Set ws = Nothing
    End If
    If wb Is Nothing Then
    Else
        Set wb = Nothing
    End If
    If appXl Is Nothing Then
    Else
        appXl.Quit
        Set appXl = Nothing
    End If

Hopefully that may also shed a little light. I'll tinker around the with appXl arguments and properties for a while to see if I can find a way to take care of the 2 items from that standpoint...

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

FYI, your code dosn't close any workbook ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Good point... I'll add that in, and see if it changes anything.. actually, now that I think about it, that should fix everything. I'll try really quick, and post back.

Thanks, PHV.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Yep, that seems to have fixed it. I set the code to close the currently open workbook, and that fixed it. I've actually got it in a few places "just in case" with the same conditional statement. So the full conditional statement is this:

If wb Is Nothing Then
Else
  wb.Close SaveChanges:=False
  Set wb = Nothing
End If

And I have it in a few locations:
[OL][LI]When I first check to make sure no workbook object is currently open before opening another.[/LI]
[LI]After finished with each workbook.[/LI]
[LI]In the "cleanup" section of the procedure, at the end of the module[/LI][/OL]

I suppose if it all works correctly 100% of the time, I will only need it in one location, but I figure using the conditional statement, it won't make a big difference to have those "just in case" locations.



--

"If to err is human, then I must be some kind of human!" -Me