Workbook.SaveAs method not working in Excel 2010

Galaxiom · Dec 5, 2012

I have an automated workbook creation in an xls document. It automatically creates and saves a workbook to a specified name and path.

Works fine in Excel 2007 on XP but will not save the in 2010 on Windows7. No error message. It simply doesn't save.

Ultimately the user is presented with a Save dialog when they go to close the workbook which we leave open after the attempted SaveAs.

The document that runs the macro is digitally signed and the is a Trusted Publisher.

We do have an Administrative Template in Group Policy to set the default save location. We didn't have this on 2007.

However I would have thought the SaveAs method would override the default location anyway so I doubt it is the issue.

All suggestions welcome.

Rx_ · Dec 5, 2012

Using Excel 2010 from Windows 7 - running the same code on a Windows Server (distributed to clients via Citrix). Excel automation saves to a Network file from either platform. So, this works on two platforms each using MS Office 2010

Will throw a few code segmants to generally describe what works for me.

Code:

' create a path for each user
50          UserLogin = Environ("username")
60          UserPath = "X:\Regulatory\Database Reports\" & UserLogin & "\Preliminary Tax Audit Information for Agent"
70          strNewReportPath = UserPath
90          DirName = strNewReportPath
100             If Dir(DirName, vbDirectory) = "" Then
110                   If MsgBox("Is it OK to create a new folder in " & userpath & "? (recommended yes)", vbOKCancel) = vbOK Then
120                       DirName = UserPath
130                       MkDir DirName
140                       Err.Clear
' litte bit of VBA code for SQL record set, retreive data, format Excel, add formulas, set page printing  - the usual
9510        strSaveAsFileName = strNewReportPath & "\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & " Preliminary Tax Audit Information for Agent" & "  " & Area_String_For_Report_Name(ID_Area) & ".xlsx"
9530        objXL.ActiveWorkbook.SaveAs FileName:=strSaveAsFileName
9540        objXL.Visible = False
9550        objXL.Quit
9590      objXL.DisplayAlerts = True  ' set to false before automation starts
PROC_EXIT:
9600    On Error Resume Next
9610    objXL.Quit
9620    Set objXL = Nothing
9630    Set strDataPrelimWell = Nothing
' then some error trapping

We use a shared network file location restricted to specific users.
Run the applicaiton on Citrix, save reports into each users folder under each report name - with a time stamp so they are all filed in order
The folder's network rights are set by an admin. This prevents the workstation-by-workstation settings. It also allows for a nightly backup.

Hope you post your solution. Always interested to see any gotcha's to avoid for myself.

All applicaitons allow users to select parameters then run report. The code is in a Function that returns true/false (completed or not). The user is then instructed to find the completed excel report in their personal folder on the network.

Galaxiom · Dec 6, 2012

I have done a Repair install for Office.

I have trimmed down the prodedure to the bare bones and SaveAs simply does not work. I put it in a new workbook. I have tried xls and xlsm file formats. They will not work on Excel 2010 but are work perfectly in Excel 2007.

I have been through all the settings in Excel and changed anything that even vaguely sounded like it would stop something happening.

I have tried different paths with the same result. I have tried DoEvents in various places.

I have tried making the procedure Wait for a few seconds before trying to save.

It is like the line was simply ignored.The Saved property of the workbook remains as False. Oddly, despite this, no save dialog is offered on exiting the generated workbook.

My next move showed it is definitely run. In desperation I tried adding a loop to repeatedly save and that threw an error on the second pass.

Runtime error '1004':
Microsoft Excel cannot access the file 'C:\F747700'
(The unrelated filename is different each time.)

During the error state or after the project is reset, the generated workbook will ask for a save when it is closed. However, if I invoke On Error Resume Next before the loop, the generated workbook can be closed without a Save dialog despite the Saved property still being False.

I have tried it with and without Activate and UserControl.

I have disabled Antivirus and Intrusion Protection on my PC.

Code:

Private Sub SaveExcel(ByVal OutputFileName As String)
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim SaveName As String
Dim n As Integer
 
    Set oExcel = New Excel.Application
 
    oExcel.Visible = True
 
    Set oBook = oExcel.Workbooks.Add
    Set oSheet = oBook.Worksheets(1)
 
    oSheet.Cells(1, 1) = "X"
    oBook.Activate
    oExcel.UserControl = True
 
    For n = 1 To 3
        Application.Wait Now + TimeValue("0:0:3")
        SaveName = OutputFileName
        Debug.Print SaveName
        oBook.SaveAs SaveName
        DoEvents
        Debug.Print oBook.Saved
    Next
 
End Sub
 
Private Sub test()
     SaveExcel ("C:\test1.xlsx")
End Sub

Rx_ · Dec 6, 2012

Your code ran on my Excel 2010 (Office 2010) from an Access module.
Set the test subroutine to Public.
replaced 'Application.Wait Now + TimeValue("0:0:3")
with oExcel.Wait Now + TimeValue("0:0:3")
It ran and asked if I wanted to replace the existing workbook.

Because you indicated it works on a previous version of office and not on 2010, can you describe more about that?
There are ways to dynamically detect the version and set a reference, or at least there use to be, will admit it has been a while since I did that.

As MS Office can be 32 bit or 64 bit - please include that information.
Are you referencing an older version (than Office 2010) of VBA / Office by chance? Is your goal to work on both Office versions?

On the last version of Access, I had upgraded from a previous version and had some "DLL Hell" and version issues myself.

Just so you know - your code (with the one change noted above) runs perfectlly on my Access 2010 Windows 7 workstation.
changed SaveName to: SaveName = OutputFileName & " " & n
Set breakpoint at END SUB - the err.number shows zero
This is just so you can validate your code is OK.

Output of my Immediate Window:
C:\test1.xlsx 1
True
C:\test1.xlsx 2
True
C:\test1.xlsx 3
True
? err.Number
0

JANR · Dec 6, 2012

I'v tested the code and in Excel 2010 with Windows XP everything was ok, but when I tried it in Windows 7 this is my result:

Code:

Private Function test()
     SaveExcel ("d:\test1.xlsx") 'Works ok
     SaveExcel ("c:\somesubfolder\test1.xlsx") 'Works ok
     SaveExcel ("c:\test1.xlsx") ' does NOT work on c-root in windows 7
End Function

I'v tested it without the wait call, see modified code:

Code:

Private Sub SaveExcel(ByVal OutputFileName As String)
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim SaveName As String
 
    Set oExcel = New Excel.Application
 
    oExcel.Visible = False
 
    Set oBook = oExcel.Workbooks.Add
    Set oSheet = oBook.Worksheets(1)
 
    oSheet.Cells(1, 1) = "X"
    oBook.Activate
    oExcel.UserControl = False
 
        SaveName = OutputFileName
        Debug.Print SaveName
        oBook.SaveAs SaveName
        Debug.Print oBook.Saved
        oBook.Close
   
Set oBook = Nothing
End Sub

So it looks like some security in Windows 7 that do not like you to put files on C-root, just a thought

JR

Galaxiom · Dec 7, 2012

Thanks for the feedback.

The C: root in Win7 does appear to not like being written to by scripts.
I also note that the New group in the Explorer COntext Menu does not include anything but "Folder". FIles can be dropped there though.

That is part of the mystery solved.

However when I use my H: drive the first attempt to SaveAs quietly fails as before.

If I use the loop it succeeds on the second and subsequent attempts provided I don't change the content. I can either SaveAs over the top of the first save or as new names.

However if I include code in the loop to change the content of the sheet and save it as a new name, every second attempt succeeds.

The following code saves the even numbered versions only.

Code:

Private Sub test()
    SaveExcel ("H:\test\test")
End Sub
 
Private Sub SaveExcel(ByVal OutputFileName As String)
 
Dim oExcel As Object 'Excel.Application
Dim oBook As Object 'Excel.Workbook
Dim oSheet As Object 'Excel.Worksheet
Dim SaveName As String
Dim n As Integer
 
    Application.DisplayAlerts = True
    
    Set oExcel = CreateObject("Excel.Application")
    
    oExcel.Visible = True
    
    Set oBook = oExcel.Workbooks.Add
    Set oSheet = oBook.Worksheets(1)
    
    oExcel.UserControl = False    
    oBook.Activate
    
    For n = 1 To 6
        SaveName = OutputFileName & n & ".xlsx"
        oSheet.Cells(1, 1) = n
        Debug.Print SaveName
        oBook.SaveAs SaveName
        Debug.Print oBook.Saved
    Next
 
    oBook.Close
    Set oBook = Nothing
    
End Sub

I even tried running the LateBinding code from Access with exactly the same results.

I guess I can work around it by saving twice but it is certainly weird.

Rx_ · Dec 7, 2012

Been here done this before (smile).
ahh, in advanced mode, can't see the code now.
You have created an Excel orphan. I had the same kind of thing, every second time. Unless you do it too much, then it seems to stop working.

First, go to your Task Manager. You will find a silent version of Excel running. Delete all of the Excel threads.
I will be right back to show you what caused this.

Finding the C:\ drive was great! So, you are probably very close now.

Galaxiom · Dec 7, 2012

Rx_ said:
You have created an Excel orphan.

I wish that was it but it isn't. I have been coding long enough to have been through that issue and come to terms with how to manage instances of objects.

There are two instances of Excel running. The original with the macros and the other which holds the created one. As the sample code stands both instances stay open at the end.

Besides the code works perfecly in Excel 2007. It was only when it ran in Excel 2010 that the trouble started.

Rx_ · Dec 7, 2012

Saw you were in Australia. Checked for you before leaving work late. Years ago, I came down to walk about the island for six weeks. Lots of fond memories.
Now, things start to happen every other time (2nd time) as the code can get confused.
http://www.access-programmers.co.uk/forums/showthread.php?t=189401&highlight=excel
I didn't read your post - you are running this from MS Excel, not access.

Started up version of Excel 2010 at home. Added a Module. Made the Test subrountine Public. Changed the loop for a single time For n = 1 To 1

Changed directory to D: since I don't have an H: and created a folder Test
your code ran perfectlly. It did not leave an orphan.

On explorer view, make sure the Excel in H:\Test is not in preview mode, we had some problems with Excel locking if the explorer was in preview.
References are set to - VBA7, Office 14\Excel.exe, stdole2.tlb, Office14.

Rx_ · Dec 7, 2012

http://www.xtremevbtalk.com/archive/index.php/t-302383.html
Excel (or vba) has a XlSaveConflictResolution Enumeration
workbook object's 'UserStatus Property' in help file to determine if other users (process) are conflicting.

This may be worth reading, a compatibility mode in Excel 2010 in the properties is mentioned along with some other SaveAs issues
http://www.mrexcel.com/forum/excel-...-basic-applications-compatability-issues.html

Your code worked perfectlly on my home Windows 7 Enterprise Office 2010 - and so did the code below. Another site showed this worked for them to fix a SaveAs issue.

Code:

Public Sub test()
    SaveExcel ("D:\test\test229")
End Sub
 
Private Sub SaveExcel(ByVal OutputFileName As String)
Dim oExcel As Object 'Excel.Application
Dim oBook As Object 'Excel.Workbook
Dim oSheet As Object 'Excel.Worksheet
Dim SaveName As String
Dim n As Integer
Dim SaveFile As String
    Application.DisplayAlerts = True
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    Set oBook = oExcel.Workbooks.Add
    Set oSheet = oBook.Worksheets(1)
    oExcel.UserControl = False
    oBook.Activate
    
    For n = 3 To 3
        SaveName = OutputFileName & n & ".xlsx"
        oSheet.Cells(1, 1) = n
        Debug.Print SaveName
        'oBook.SaveAs SaveName
        SaveFile = oBook.SaveAs(SaveName, -4143, , , , , True) ' xlnormal
'expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)
        Debug.Print oBook.Saved
        Debug.Print "My SaveFile is " & SaveFile
    Next
    oBook.Close
    Set oBook = Nothing
End Sub

Galaxiom · Dec 7, 2012

Rx_ said:
The Application.displayalerts= true creates an orphan

After the Set oExcel = ...
put the oexcel.displayalerts = False - we don't want those across the memory boundry interruptions.

I can see where you are coming from but it isn't creating an orphan. Anyway I tried letting it for the instance instead as you suggested but unfortunately that has made no difference.

It is supposed to be on by default anyway but to be sure I put it on in my test code just to ensure that any alerts would be displayed.

(Incidentally, during testing of DisplayAlerts I have come across something weirder still that I am going to pursue for the rest of the afternoon. I'll post a link from this thread when I get to the bottom of it.)

On Excel 2010, the workbooks are all started in independent windows. Are you sure that the first excel instance isn't started with a workbook and perhaps 3 tabs?

The workbooks are definitely configured as in the code.

Here is what has happened to me.
[URL="http://www.access-programmers.co.uk/forums/showthread.php?t=189401&highlight=excel"]http://www.access-programmers.co.uk/forums/showthread.php?t=189401&highlight=excel[/URL]

I remember that thread. Lessons like that are golden. That kind of thing is why I trim down testing to the bare minimum. when the going gets tough.

Thanks for sticking with me on this one. I am the only VBA coder where I work and it is particularly bewildering with nobody to even bounce the idea with. You and JANR have made some incisive observations.

Rx_ · Dec 7, 2012

I was wrong, was testing it from Access before, sorry I misse that you were doing this from Excel. And, now I see it does instanuate a new independent copy. I am batting zero here.
I am one of a kind in an international company LOL. Have been in your shoes and totally agree that bouncing ideas off helps. that is why I have a record for giving the most "Thanks" LOL
My previous post was so off base, I replaced it. Take a look - it is weird, but worth looking at. I had some really bad problems after updating from Office 2000 to 2007. applied patches - and fixes to patches. Very crazy stuff. Eventually got a new workstation with just Office 2007 and it all was good again.
The update from 2007 to 2010, didn't have the same problems. So, I just wanted to throw that at you for consideration.
The Registry isn't always... perfect.

I think your very compentent and on the right track. The code works for me. I will try it in Excel on my development workstation downtown - in about 9 hours. But, it worked running from Access modules and it worked here at home on Excel. I could not replicate your problem.

Galaxiom · Dec 11, 2012

I have persisted with this problem without resolution.

The original Office 2010 install was part of a disk image with Winows7. I have downloaded the Office 2010 installer from Microsoft and tried that. No change to the behaviour.

I also installed it onto an older machine running Windows XP Professional. This machine ran Excel 2007 just fine before. The Excel 2010 problem occurs there too.

Next I am going to try it on a laptop at home that already has Office 2010 H&B installed.

However I have also experienced problems with a number of Access applications that had worked flawlessly for a few years under Access 2007.

My judgement at this point is that Office 2010 is a bug ridden pile of rubbish.

Rx_ · Dec 11, 2012

Lets add the SP1 to the discussion
http://www.access-programmers.co.uk/forums/showthread.php?t=212868&page=2
There are some problems depending on the version.
http://blogs.office.com/b/microsoft...acts-64_2d00_bit-installations-of-access.aspx
http://blogs.office.com/b/microsoft.../17/office-2010-sp1-coming-end-of-june-b.aspx

Office 2010 was a very fustrating experience. It is probably time for you to read about the Service Pack and dig into the DLL if necessary.
Microsoft has very little low support for customer support. It is one of the problems with recommendations of Microsoft cloud soultions. The wife was using Windows 7 Movie Maker to make some Chirstmas DVD. It is all cloud based. Even with high-speed internet, it takes hours on a souped up i7 desktop. Worst of all, if it hits a small snag, Microsoft has no clue - so the process starts all over again.

Galaxiom · Dec 11, 2012

My ASUS laptop does not exhibit the problem. Like my work PCs it has Excel 2010 including SP1. Laptop has not been updated for a while but it isn't far behind. I really don't think that is the issue.

Laptop: 14.0.6112.5000
Desktop: 14.0.6123.5001

Has to be something about the PCs at work. I have tried disabling the Antivirus and Intrusion Protection during the Office install and while running the script.

They are both Dell (Optiplex 755 and Optiplex 9010SF) but I can't really see it being related to the actual hardware. All the same, if anyone has a Dell with Office 2010 I would appreciate it if you would try the code.

When all sensible ideas fail .........

Rx_ · Dec 12, 2012

Both my old computer replaced 4 months ago and the current one is Dell.

This brings memory back a long time ago. I was teaching the certified MS Excel Object Model Programming class at Marathon Oil in Findlay Ohio.
Half the class (on the left side, no political pun intended) couldn't get a sizeable amount of object code to work. The other side had no problem. Identical PC desktops. I told the IT guy.
The next day, he told me that he was updating the ROM Bios and had finished half of them. He had stayed to finish the other half. We tested those that had not workded and sure enough they worked. It can't be my imagination. The whole class experienced it.

At this customer's site, I struggled with the upgrade to 2010. Just werid events that worked befor would not work. I tried SP, patches to the SP. Finally, IT got tired of my whining (and spitting... come to think of it.. the spitting was probably it) and gave me a new formatted computer with fresh Office 2010. I had no problems after that. Had forgotten about that ugly experience until you had your problems. It is as if Freud unlocked my past fears.

My ranting doesn't solve your problems. However, it is not just you.

Galaxiom · Apr 29, 2013

Galaxiom said:
I have an automated workbook creation in an xls document. It automatically creates and saves a workbook to a specified name and path.

Works fine in Excel 2007 on XP but will not save the in 2010 on Windows7. No error message. It simply doesn't save.

The problem went away as mysteriously as it arrived after a round of Microsoft Updates applied some time in February.

I had worked around it by running SaveAs twice and it became obvious that it had been fixed when users ran the end of February report.

baldmosher · Oct 31, 2013

I'm having very similar issues trying to automate in VBA (Office 2010, not SP1, installed around April this year, so that's my first thing to try).

In my case I run a macro on Workbook.Open that updates all pivots, then on Workbook.Close saves the workbook as an .xlsm to a temp folder, then opens it, saves as .xlsx and .xls then zips the files to SharePoint.

If I manually open the workbook, and close it, everything works.

If I try to automate that using an external workbook to open and close it, the .SaveAs fails. If I pause the code, and try to use the Immediate pane to test, .Save doesn't work either. I can, however, click the Save icon in Excel.

I think I found this thread about 3 months ago but completely forgot to read page 2.....

EDIT still not fixed:

SP2 now installed, and still, the error occurs! Annoying. However, SP2 isn't shown in Help>About so I'm not convinced it's installed properly....

Anyway I've come up with a blunt workaround... I send myself an email from Workbook 1, I have an Outlook VBA trigger that will open WB2 directly to run the macros, instead of opening WB2 from WB1. So that way, I don't get the Save error.

Workbook.SaveAs method not working in Excel 2010

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Attachments

JANR

Registered User.

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

baldmosher

New member

Similar threads

Users who are viewing this thread