Excel called from Access doesn't save

[tman138]

I have an Excel sheet that originates from an Access database. Since there are numerous sheets and I have multiple users who need to access it, I want to format it once and not have worries about users needing to do so or have them enable macros. Toward that end, I first create the workbook from the DoCmd.TransferSpreadsheet method creating a dynamic number of sheets and dynamically naming them. I prep the file by first copying an Excel template file which contains macros to format the sheets and insert totals. The first sub that runs from the OpenWorkbook macro checks for the existence of Sheet1, if it exists, it deletes it and loops through the remaining sheets formatting each in turn and saves the workbook. If I build the workbook from Access and run it manually from Excel it works great. I end up with Sheet1 deleted and all of the remaining sheets correctly named and formatted. I can then open the sheet with macros disabled and have the desired result.
However, I want to fully automate this process running the app and saving a fully formatted Excel workbook on a network share. When I call the already outputted Excel sheet from Access it opens, the formatting macros run, but the workbook doesn’t save, even though I have ‘ThisWorkbook.Save’ line in one of the macros that runs.
One oddity is that when I step though the code module and Excel opens the workbook, my dynamically named sheet has a ‘1’ appended to it. i.e.: my sheet is named using a global variable that I set when I output the file. – gFileName - I name it “sched” & the date & “.xls” (sched122709.xls) but shows in the Excel title bar as sched1227091.xls

This is my Access function:

Public Function fnOpenSheet()
Debug.Print gFileName
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Set oXL = CreateObject("Excel.Application")

On Error Resume Next
oXL.UserControl = True
On Error GoTo 0

On Error GoTo ErrHandle
sFullPath = gFileName

With oXL
.Visible = True
.Workbooks.Open (sFullPath)
Debug.Print sFullPath
'.Save
'.Run "subSave"
.Workbooks.Close
'.Workbooks.Close SaveChanges:=True
.Quit
End With
ErrExit:
Set oXL = Nothing
Exit Function
ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Function

If I uncomment .save I get a Microsoft Excel error : A file named ‘RESUME.XLW’ already exists in this location. Do you wish to replace it?

So I thought I’d add a macro to the workbook and call it from Access. I added one call subSave consisting of a single line:
ThisWorkbook.Save
When I uncomment that the macro is called but still nothing saves in the workbook.
If I uncomment .Workbooks.Close SaveChanges:=True
I get an Access error Application Defined or Object Defined error.

When I drop ActiveWorkbook.Close SaveChanges:=True into my subSave macro, I get a an automation error.

What seems to be happening is that when calling the workbook from Access the open file is actually a temporary file of some sort that isn’t being written to via the macros.

Any suggestions as to how to work around this? I really need to finish this and move on to other projects.

 

[tman138]

I have resolved this.
Once I realized that the worksheet name was actually different, I decided to add an intermediate workbook tmp.xls where I allow the macros to do their formatting work and then I have a naming function in the macro which contains the path and file naming. I set those in a global variable (gPathOut) on launch and end my formating loop with:
ActiveWorkbook.Close SaveChanges:=True, Filename:=gPathOut
This is effectively a SaveAs command writing to a new file.
It works like a charm.

 

[SkipVought]

Your probelm is with

[b]ThisWorkbook[/b].Save

as ThisWorkbook has a very specific reference that, I believe, does not exist in your context.

    With oXL
        .Visible = True[b]
        With .Workbooks.Open (sFullPath)[/b]
           Debug.Print sFullPath[b]
'BTW, you do not SAVE the application.  You SAVE the WORKBOOK
           .Save[/b]
           oXL.Run "subSave"
           .Close[b]
'You already saved the WORKBOOK.  What is this?[red]
         '.Workbooks.Close SaveChanges:=True[/red][/b]
        End With
        .Quit
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tman138]

Skip;
The save ThisWorkbook.Save executed fine wihtin Excel when run manually.
The problem was the workbook name changes when called programatically from Access, so while my saves were being executed, they were only running against the temporary or phantom workbook that is created behind the scenes.
This was evidenced by
Debug.Print ThisWorkbook.Name which returned my workbook name with a '1' appended.
Once I figured this out I changed strategies and named and saved my formatted workbook from the Excel code instead of my Access code.
While I've written hundreds of Access applications, this was only my 3rd that involved Excel. It was an interesting project.
Thanks

 

[SkipVought]

Which is why I stated, "ThisWorkbook has a very specific reference that, I believe, does not exist in your context."

Your context being MS Access.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]