PDA

View Full Version : Error while saving with reference to another workbook in footers



nicnad
04-16-2012, 05:30 PM
Hi,

Hi,

I have the following code :


Public dateup As String
Public wbreport As Workbook
sub test()
Set wbreport = Workbooks.Open(ThisWorkbook.Path & "\" & "Report Model" & ".xls", False, True)
dateup = Right(ThisWorkbook.Sheets("MATRIX").Shapes("Rectangle 7").TextFrame.Characters.Text, 7)
With wbreport.Sheets("Matrix_EN").PageSetup
.LeftFooter = "Last Update: " & dateup
End With
wbreport.SaveAs (reportsavepath & "\" & "Report Revision " & dateup & ".xls")
end sub


With the code as is, Excel crash completly when reading this line.

wbreport.SaveAs (reportsavepath & "\" & "Report Revision " & dateup & ".xls")


The weird thing is, if I remove this line :



With wbreport.Sheets("Matrix_EN").PageSetup
.LeftFooter = "Last Update: " & dateup
End With

I don't get the error anymore.

What is causing this error? Have I declared my variable the wrong way and this is what is causing an error while saving?

I already posted that question on mr.excel forum and will let you know if anyone there finds the answer. Here is the link.

mrexcel (dot) com /forum/showthread.php?t=628996

Thank you for your help!

CatDaddy
04-17-2012, 09:31 AM
does the footer come up as you expect if you dont automatically save the file? and you can get rid of the with:

wbreport.Worksheets("Matrix_EN").PageSetup.LeftFooter = "Last Update: " & dateup

nicnad
04-17-2012, 10:32 AM
I tried getting rid of the with. Same problem occurs.

The footer come up as I expect if I don't save the file the only thing is, if I first activate the sheet that has the footer in it, Excel crash.

If I activate another sheet than activate this sheet, Excel does not crash.

I don't know if the exception Information will be relevent to you but here they are :

Code: 0xc0000005
Flags: 0x00000000
Record: 0x0000000000000000
Address: 0x000000003005546b

Thank you for your help.

CatDaddy
04-17-2012, 10:47 AM
try:
wbreport.SaveAs (reportsavepath & "\" & "Report Revision " & dateup & ".xls"), fileformat:=56

nicnad
04-17-2012, 11:15 AM
Thank you for the quick reply.

Excel still crash when I try the above line.

CatDaddy
04-17-2012, 11:41 AM
are you sure reportsavepath is a valid path?

nicnad
04-17-2012, 12:14 PM
Yes it is a valid path.

Sorry for the confusion, I forgot to write it in my first post.

Like I said, If I keep the exact same code and I block comment this part :


With wbreport.Sheets("Matrix_EN").PageSetup
.LeftFooter = "Last Update: " & dateup
End With

The code works perfectly and the workbook gets saved without any error.

But if I put the above part in the code, Excel crashes.

Maybe the way I am using the pagesetup or my variable dateup the wrong way...



Lets forget my previous code and pretend that we have two workbooks to make this simplier :

Workbook1 is refered as being Thisworkbook
Workbook2 is named "Report Model.xls"
Both workbooks are located in c:\test\

If there was a value in Sheets(1).cells(1,1) of Workbook2 that I would want to add has a part of the footer of the sheets named "Matrix_EN" in Workbook2 would you write the code as follow? :


Public dateup As String
Public wbreport As Workbook
sub test()
Set wbreport = Workbooks.Open("c:\test\Report Model.xls", False, True)
dateup = thisworkbook.sheets(1).value
wbreport.Sheets("Matrix_EN").PageSetup.LeftFooter = "Last Update: " & dateup
wbreport.SaveAs ("c:\test\" & "Report Revision " & dateup & ".xls")
End sub

Your help and time are really appreciated.

nicnad
04-17-2012, 12:24 PM
Public dateup As String
Public wbreport As Workbook
sub test()
Set wbreport = Workbooks.Open("c:\test\Report Model.xls", False, True)
dateup = thisworkbook.sheets(1).value
wbreport.Sheets("Matrix_EN").PageSetup.LeftFooter = "Last Update: " & dateup
wbreport.SaveAs ("c:\test\" & "Report Revision " & dateup & ".xls")
End sub

I just tried this in example workbooks and it worked.

I guess my problem has to do to with other code in my workbook.

The weird thing is that my code was running smoothly until I added this simple piece of code :


wbreport.Sheets("Matrix_EN").PageSetup.LeftFooter = "Last Update: " & dateup


Unfortunately, I cannot upload my workbook since it contains sensitive information.

Let me know if you think of anything that might be causing this...

Thank you for your help

nicnad
04-17-2012, 12:41 PM
I just reviewed my whole code and got it to save without crashing just by removing this line :


Public dateup As String

I am really not convinced that this crash problem while saving was directly linked to this but everything is working fine now.

I will let you know if the problem appears again.

Thank you for taking the time to have a look at this.

Have a great day.

Regards.