Error while saving with reference to another workbook in footers

[nicnad]

Hi,

Hi,

I have the following code :

[vba]
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
[/vba]

With the code as is, Excel crash completly when reading this line.
[vba]
wbreport.SaveAs (reportsavepath & "\" & "Report Revision " & dateup & ".xls")
[/vba]

The weird thing is, if I remove this line :

[vba]

With wbreport.Sheets("Matrix_EN").PageSetup
.LeftFooter = "Last Update: " & dateup
End With
[/vba]
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]

does the footer come up as you expect if you dont automatically save the file? and you can get rid of the with:

[VBA]wbreport.Worksheets("Matrix_EN").PageSetup.LeftFooter = "Last Update: " & dateup[/VBA]

[nicnad]

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]

try:
[VBA]wbreport.SaveAs (reportsavepath & "\" & "Report Revision " & dateup & ".xls"), fileformat:=56
[/VBA]

[nicnad]

Thank you for the quick reply.

Excel still crash when I try the above line.

[CatDaddy]

are you sure reportsavepath is a valid path?

[nicnad]

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 :

[vba]
With wbreport.Sheets("Matrix_EN").PageSetup
.LeftFooter = "Last Update: " & dateup
End With
[/vba]
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? :

[vba]
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
[/vba]
Your help and time are really appreciated.

[nicnad]

[vba]
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
[/vba]

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 :

[vba]
wbreport.Sheets("Matrix_EN").PageSetup.LeftFooter = "Last Update: " & dateup
[/vba]

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]

I just reviewed my whole code and got it to save without crashing just by removing this line :

[vba]
Public dateup As String
[/vba]
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.