If varPathLength = 3 Then
.ActiveSheet.SaveAs _
Filename:=strNewPath + varSheetName + "_export.txt", _
FileFormat:=xlTextPrinter
.close SaveChanges:=False

Else
.ActiveSheet.SaveAs _
Filename:=strNewPath + "\" + varSheetName + "_export.txt", _
FileFormat:=xlTextPrinter
.close SaveChanges:=False
End If


--
Regards,
Tom Ogilvy


ilona wrote in 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 "do 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

Hi Tom,

Thanks Tom for you comments and suggestions. Unfortunately I was not able to use your solution because I wanted the flexibility of the utility remaining open and giving the user the choice of saving or not saving the changes rather than closing right away. But you helped in pen pointing the problem so I could rewrite the routine in a different manner but produce the same results.


Regards
ilona