-----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
ExitHere:
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
