My small code make excel crash

P

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
 
J

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"
 
P

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???
 
M

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.
 
J

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
 
R

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.
 
P

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.
 
P

Petter

sorry...JAF solved it not mdmackillop... :)

Ron de Bruin said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top