Excel is open do you want to save changes?

  • I have the code shown below. this code is an in excel via a command in access. Access does not keep the formatting i want when pasting into a word table, so I have used excel as a "middle man". Everything works fine but the excel app is showing as closed on my computer. However; If i attempt to open this workbook it says its already in use by me. Or if i try to close the PC it says i need to save changes in this workbook.


    How can i properly close it via code once its completed the copy/paste task?


    thanks, Andy



  • Hi,


    For starters looking at your code you have the ActiveWorkbook.Close statement commented out ???



    Code
    'ActiveWorkbook.Close



    Also I prefer using ThisWorkbook.Close as that will always close the workbook where the macros are running.


    Regards,


    Tom Rowe

  • Okay. thanks a lot for your suggestion. We now have a new problem. *note the code below is at the end of a very long piece of code. I have narrowed it down to juist this part as it's the only part dealing with excel. (also used breakpoints to clarify this section is causing issue).
    In acces when I run this code (in excel):

    Code
    xl.Run "ThisWorkbook.BetterExcelDataToWord"
    Exit Sub
    Error_Handle:
    MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description
    End Sub


    I get the error 440 automation error. All the desired outcomes from the code are achieved and the error doesnt seem to be causing problem, but still I think it needs resolving..


    Any ideas on this one? ( I am researching online also).


    Thanks, Andy.


    Edit: Ive ran the code within excel seperatly and there is no error. So the error is within the small piece of code shown here.


    Apparently its a problem with the registry. Does this seem likely? Im doing this on a works network so it may be difficult to resolve if it is.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!