-----Original Message-----
I have created an Excel utility that imports and exports
data. Have found an issue where by when I export a
spreadsheet to a text file. When I close the workbook and
choose No ?odo not want to save changes?� some thing
strange happens. I would expect when I open the workbook
again that no modifications would have been made. Instead
I find that the spreadsheet that was used to export data
has changed. First change is that the sheet name has been
changed to the exported file name and any formatting that
I had was removed. Not exactly sure what is happening but
it looks like memory is not being cleared. How do I fix my
code so that when the close event occurs and a user
chooses not to save the changes that no changes are made
to the workbook? I need a quick solution due to production
deadlines. Thanks in advance for your assiatance

Export(ByRef boolExportStatus as Boolean, ByRef
strWorksheetName as String)
Dim strOldName As String
Dim strOldPath As String
Dim strNewPath As String
Dim varOldFormat As Variant
Dim varSheetName As Variant
Dim varPathLength As Variant
Dim strMsg As String
Dim strSheetName As String

On Error GoTo HandleError

strWorksheetName = ActiveSheet.Name

Application.DisplayAlerts = False

strMsg = "Please select a location for the export."
With ActiveWorkbook
strOldName = .Name
strOldPath = .path
varOldFormat = .FileFormat
varSheetName = .ActiveSheet.Name
strNewPath = GetDirectory(strMsg)

' Exit if dialog box canceled
If strNewPath = "" Then
MsgBox "No directory was selected -
Cancel button clicked."
boolExportStatus = False
GoTo ExitHere 'Error Handler
End If

' Find if path is only a root directory
varPathLength = Len(strNewPath)

' Test to see if directory is at the root level
If varPathLength = 3 Then
.ActiveSheet.SaveAs _
Filename:=strNewPath + varSheetName
+ "_export.txt", _
FileFormat:=xlTextPrinter
.SaveAs Filename:=strOldPath + "\" +
strOldName, FileFormat:=varOldFormat
.ActiveSheet.Name = varSheetName
Else
.ActiveSheet.SaveAs _
Filename:=strNewPath + "\" + varSheetName
+ "_export.txt", _
FileFormat:=xlTextPrinter
.SaveAs Filename:=strOldPath + "\" +
strOldName, FileFormat:=varOldFormat
.ActiveSheet.Name = varSheetName
End If

Application.DisplayAlerts = True

End With
boolExportStatus = True

ExitHe
Application.DisplayAlerts = True
Exit Sub

HandleError:
If Not dhError("ExportGpaWorksheet", True) Then
' Do Nothing
End If
Resume ExitHere
End Sub

Have the following Before Close event and was wondering
if I could add some code here?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

DeleteMenu

End sub

.
The quick solution is to do a work around and create an
auto open macro to clean back up the sheet before it gets
used again. The code probably has a problem with windows
focus or what the active sheet or book is. It can be
degubbed but would probably be a paying deal.

Thanks,

GJones

Hi GJones

Thanks for your input. Could you explain your solution because I am a little confused as to how to implement it

Ilona;

You said that when you go back in the sheet is messed up
and if I understoo correctly it was formatted
incorrectly. If you create a macro named Auto_Run it will
run automatically each time the workbook opens. If you
make the Auto_Run macro fix what ever the regular routine
messed up then it will still work. This is a work around
because you said you were under a time pressure.

Thanks,

Greg
-----Original Message-----
Hi GJones,

Thanks for your input. Could you explain your solution
because I am a little confused as to how to implement it?


.

Hi Greg

Thank you for your comments. I was able to fix the problem by rewriting the code so that I no longer used ActiveSheet.SaveAs routine. I really appreciated your comments and suggestions

Regard
ilon


----- GJones wrote: ----

Ilona

You said that when you go back in the sheet is messed up
and if I understoo correctly it was formatted
incorrectly. If you create a macro named Auto_Run it will
run automatically each time the workbook opens. If you
make the Auto_Run macro fix what ever the regular routine
messed up then it will still work. This is a work around
because you said you were under a time pressure

Thanks

Gre
-----Original Message----
Hi GJones
Thanks for your input. Could you explain your solution
because I am a little confused as to how to implement it