My small code make excel crash

[Petter]

Hi,

The code under make excel crash. I have found out that the last line
"ActiveWorkbook.Close" is the reason, but i dont understand why. And i like
to solve it aswell...
How can i close the workbook without crashing excel.....???




Sub FlyttetilHLT()

Dim adr
Dim fadr
Dim klient
klient = Range("c4").Value
adr = ActiveWorkbook.Path
Worksheets(2).Activate
fadr = adr & "\" & "IT" & klient & "TRS.HLT"
ActiveWorkbook.SaveAs Filename:=fadr, _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
End Sub

 

[jaf]

Hi Petter,
I think you need slashes before and after klient.

fadr = adr & "\" & "IT" & klient & "TRS.HLT"

Should be...

fadr = adr & "\" & "IT" & "\" & klient & "\" & "TRS.HLT"

 

[Petter]

Hi Jaf,

That is not the problem...
The file name would be somthing like IT006TRS.HLT and that is correct.
That bit work like a charm....

It has something with that i trying to close the workbook that i have
saved..could it be something with that is not longer a workbook??..since i
saved it as a .txt file???

 

[mdmackillop]

By using the line
Debug.print fadr
and viewing the result in the Immediate window
or
MsgBox fadr
you can check the result of your code before trying to save the file.

 

[jaf]

Hi Petter,
If you are running code, you have to have at least one workbook open.
Are you closing the workbook that contains the code?

Application.quit will close Excel completely without an error.
John

 

[Ron de Bruin]

hi Petter

Excel version ?

 

[Ron de Bruin]

If you use 2007 and run your code from a shape read this
Good night
**************************************

Code run from a shape can crash Excel 2007

If a macro that closes an Excel workbook is assigned to a shape, clicking it in Excel 2007 will cause a crash.
Note: It seems that If Office 2003 is also installed on the machine you not have this problem.

To see this, copy the code below into a standard module. Then add a shape, a rectangle for example, to worksheet, right click it,
and assign this macro to it.
Sub CloseWithoutSaving()
Dim Response

Response = MsgBox("Are you sure you want to close the workbook without saving," _
& vbNewLine & _
"Click No and use the Save button to save your changes" _
, vbYesNo, "My test Macro")

If Response = vbYes Then
ActiveWorkbook.Close SaveChanges:=False
Else
'Do nothing
End If

End Sub

Note: This code runs correctly in Excel 97-2003.The moral is that you should consider using a Forms or ActiveX button rather than a
shape if there is any possibility Excel 2007 will be used. Those controls do not have this problem.But there is a Workaround :Copy
this macro also in your module and right click on the shape and assign this macro to it.

Sub Assign_This_Macro_To_The_Shape()
Application.OnTime Now + TimeSerial(0, 0, 1), _
ThisWorkbook.Name & "!CloseWithoutSaving"
End Sub

When you click on the Shape it will wait for one second and then run the macro with the close code.Thanks to Dave Peterson for this
tip.

 

[Petter]

Thx, i am running 2007.
But I dont have any shapes in there.
Mdmackillop solved the problem for me, but thx anyway...nice to know in the
future.

 

[Petter]

sorry...JAF solved it not mdmackillop...

If you use 2007 and run your code from a shape read this
Good night
**************************************

Code run from a shape can crash Excel 2007

If a macro that closes an Excel workbook is assigned to a shape, clicking it in Excel 2007 will cause a crash.
Note: It seems that If Office 2003 is also installed on the machine you not have this problem.

To see this, copy the code below into a standard module. Then add a shape, a rectangle for example, to worksheet, right click it,
and assign this macro to it.
Sub CloseWithoutSaving()
Dim Response

Response = MsgBox("Are you sure you want to close the workbook without saving," _
& vbNewLine & _
"Click No and use the Save button to save your changes" _
, vbYesNo, "My test Macro")

If Response = vbYes Then
ActiveWorkbook.Close SaveChanges:=False
Else
'Do nothing
End If

End Sub

Note: This code runs correctly in Excel 97-2003.The moral is that you should consider using a Forms or ActiveX button rather than a
shape if there is any possibility Excel 2007 will be used. Those controls do not have this problem.But there is a Workaround :Copy
this macro also in your module and right click on the shape and assign this macro to it.

Sub Assign_This_Macro_To_The_Shape()
Application.OnTime Now + TimeSerial(0, 0, 1), _
ThisWorkbook.Name & "!CloseWithoutSaving"
End Sub

When you click on the Shape it will wait for one second and then run the macro with the close code.Thanks to Dave Peterson for this
tip.