Macros - Upgrading from Excel 2003 to Excel 2010

Good morning,

As a company we are in the process of migrating Office 2003 to Office 2010. We have a number of Macros written many years ago, by a person that has since left the company, only to fine the Macro starts to run then fails at the first ActiveWorkbook.Save stage.

The Macro is some 1734 lines long, and the first Save is at Line 48, therefore I am only including the first section. I can provide the full routine if needed.

Can anyone assist me in the reason for the failure.

Sub PRINT_DEBITNOTE_DOCS1()
'
' PRINT_DEBITNOTE_DOCS3 Macro
' Macro recorded 19/05/2000 by jstevenson
'
' Keyboard Shortcut: Ctrl+Shift+W

Application.DisplayAlerts = False
Workbooks.Open Filename:="\\Neptune\Doehle\Accounts\Crew\crewaccs\NEW 2012 LSUMS-MID\ATLANTSKA PLOVIDBA LUMPSUMS\LSUM-AP001.xls", UpdateLinks:=3
Calculate
Application.Goto Reference:="DEBITNOTEUSD"
ActiveSheet.PageSetup.PrintArea = "DEBITNOTEUSD"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "DEBITNOTEUSD"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.69)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWorkbook.Save
ActiveWorkbook.Close

Thanks in anticipation of any help you can give.

What exactly happens - error message? If so, what?

Macros - Upgrading from Excel 2003 to Excel 2010.

Quote:

Originally Posted by Aflatoon

What exactly happens - error message? If so, what?

Hi Aflatoon,

Sorry I missed that :doh:.

The user gets a VB Runtime 1004 error - Method 'Save' of object '_workbook' failed.

:banghead: So far I have:

1. added the network path to the Trusted Location,
2. enabled all Macros
3. Checked "Trust access to the VBA project object model"
4. Unchecked 'Remove Personal Information from file properties on save'

Can the same user manually save that workbook?

1 Attachment(s)

Quote:

Originally Posted by Aflatoon

Can the same user manually save that workbook?

Yes. One of the first things I tried was to check the file permissions.

I have attached the full macro as a zipped Txt file.

for a macro of such magnitude, this code is terrible, while recording macros is of great help to writing code, this looks like it has just been recorded and left to chance

Quote:

Workbooks.Open Filename:="\\Neptune\Doehle\Accounts\Crew\crewaccs\NEW 2012 LSUMS-MID\ATLANTSKA PLOVIDBA LUMPSUMS\LSUM-AP001.xls", UpdateLinks:=3

rather than just opening the workbook and working with the then active workbook, the workbook should be set to a workbook object, this would avoid many possible errors, or if errors occur would be much easier to dignose

Code:

set wbook = Workbooks.Open(Filename:="\\Neptune\Doehle\Accounts\Crew\crewaccs\NEW 2012 LSUMS-MID\ATLANTSKA PLOVIDBA LUMPSUMS\LSUM-AP001.xls", UpdateLinks:=3)

in this case wbook is an object of that specific workbook and can be referred to using the object

Code:

wbook.save wbook.close

likewise a sheet object can also be set

Code:

set wsht = wbook.sheets(1) ' change sheet index or name to suit with wsht.pagesetup 'etc

for your current problem
for test puposes
if you do not save the file, try forcing save on close, or allow alert dialog to ask user, does the file save, or do you still get error?

Code:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' ActiveWorkbook.Save ActiveWorkbook.Close true ' true to save, without true or false an alert should be raised if application.displayalerts is not set to false