ilona,

Your problem may be at
.SaveAs Filename:=strOldPath + "\" + strOldName,
FileFormat:=varOldFormat
.ActiveSheet.Name = varSheetName
End If

under

' Test to see if directory is at the root level

try commenting these lines out, and see if it works

Steve
"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 Steve,

Thanks Steve for you comments and suggestions. You helped in pen pointing the problem so I could rewrite the routine in a different manner but produce the same results without using the ActiveSheet.SaveAs.


Regards
ilona


----- Steve Smallman wrote: -----

ilona,

Your problem may be at
.SaveAs Filename:=strOldPath + "\" + strOldName,
FileFormat:=varOldFormat
.ActiveSheet.Name = varSheetName
End If

under

' Test to see if directory is at the root level

try commenting these lines out, and see if it works

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