Trouble having Access to save an Excel spreadsheet

[lawdy]

I have Access open an Excel spreadsheet, then Access makes some changes to the spreadsheet, and then I need Access to save the spreadsheet. I can do this, but when it saves the file, I get the message "A file named 'P&L 2024.xls' already exists in this location. Do you want to replace it?". I just want to save the file - YES, but not as a new file. I hope this makes sense. The code is as follows:

Dim objXLApp As Object    Dim objXLBook As Object
    Dim objWorkSheet As Object
    pubStrYrSpecial = "2024"
    
    '   open p&l
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("L:\aaTS\Data\P&L 2024.xls")
    Set objWorkSheet = objXLBook.Worksheets(pubStrYrSpecial)
        
    '   Access makes some changes to the workbook
    
    '   save and close the workbook
    objXLBook.Save
    objXLBook.Close
    objXLApp.Quit
    Set objXLApp = Nothing

I appreciate any help.

[June7]

Works for me without prompt. Must be something in the changes causing issue. Post all the code or provide files.

[lawdy]

That is the whole code. I have not yet added the changes Access is to make except that I have it in a function as below.

Public Function modPnL_sumRevenue1()   '   sum the revenue values putting it in 'TOT REVENUE'    Dim objXLApp As Object
    Dim objXLBook As Object
    Dim objWorkSheet As Object
    pubStrYrSpecial = "2024"
    
    '   open p&l
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("L:\aaTS\Data\P&L 2024.xls")
    Set objWorkSheet = objXLBook.Worksheets(pubStrYrSpecial)
        
    '   Access makes some changes to the workbook
    
    '   save and close the workbook
    objXLBook.Save
    objXLBook.Close
    objXLApp.Quit
    Set objXLApp = Nothing
    
End Function

I get the dialog box everytime.

[June7]

I used your code with my Excel file and do not get prompt.

What kind of changes do you want to make? It is possible to set a link to Excel sheet and edit data (but not delete rows).

[CJ_London]

May depend on the windows or office version. Not tested but try commenting out the save line and modifying the close line to

objXLBook.Close SaveChanges:=True

[Welshgasman]

Works for me also in 2007. However, only after dimming missing variables?

Why are you not using Option Explicit at the top of every module?

[lawdy]

Ok, I got several responses and I am trying to answer all of them. I do have 'Option Compare Database Option Explicit' on top of the module. I just did not copy that part. I also tried objXLBook.Close SaveChanges:=True. I still get the prompt. As far as the changes, I will only change the data. I guess I will have to live withe the prompt. Thanks for trying.

[Welshgasman]

I do have 'Option Compare Database Option Explicit' on top of the module.

So why did mine complain about

pubStrYrSpecial
objXLApp

each of which were not declared in the procedure.?

Are they Globals?

[Micron]

@lawdy, what is your Excel version? Not sure if it would matter but I'd declare the objects as application, workbook and worksheet - not Object, unless you have a reason for doing so. I also imagine that you're doing this over a network and might need a pause between Save and Close. Try stepping through the code and wait couple of seconds before closing.

[June7]

Do you want to explore the link option?

[CJ_London]

unless you have a reason for doing so

OP is probably late binding.

I note the file is a .xls, not .xlsx which might make a difference?

[Welshgasman]

OP is probably late binding.

I note the file is a .xls, not .xlsx which might make a difference?

I tried both extensions, first xlsx then xls. Each worked fine.

[lawdy]

What is the link option?

[Micron]

I think it was the linked sheet suggestion in post 4.

[June7]

Here is example of query statement that makes connection to a simple worksheet. Data can be edited and records added but not deleted.

SELECT * FROM [Units$] IN 'C:\Users\Administrator\June\MyStuff\Condos.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];

When Access External Data wizard sets up a link to worksheet, it will probably set IMEX property = 2 and cannot edit in the linked table.

Create a link by typing in SQLView of query object and change IMEX to = 0 and edit is possible.