1

I use excel to edit tabular data and track the changes using version control. Each time I make a change in the xls(x) I have to go to File->Export, after which the excel continues to edit the exported file instead of the xls(x).

I would like excel to automatically export a text version of my sheet every time I save my xls(x) sheet, and continue editing the xls(x). How do I achieve this?

EDIT:

I saved the file as a macro-enabled file (xlsm), enabled macros and following John Coleman's suggestions, inserted the following code (ref) into VBAProject->ThisWorkbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Application.DisplayAlerts = False
    Dim s As String
    s = ActiveWorkbook.FullName
    s = Replace(s, "xlsm", "txt")
    ActiveWorkbook.SaveAs Filename:=s, FileFormat:=xlText
    Application.DisplayAlerts = True

End Sub

but the active workbook becomes the txt file (and excel crashed immediately after). Is there a way to export and continue editing the original file?

Community
  • 1
  • 1
Sparkler
  • 2,581
  • 1
  • 22
  • 41
  • 2
    Put the exporting code in the event handler `Workbook_BeforeSave()` – John Coleman Dec 15 '17 at 15:10
  • @JohnColeman I get the error mentioned above – Sparkler Dec 15 '17 at 15:32
  • 2
    In the `ThisWorkbook` module there should be a stub which you can call up: `Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)` See this: https://www.exceldemy.com/how-to-enter-event-handler-vba-code/ – John Coleman Dec 15 '17 at 15:35
  • @JohnColeman This seems to partially work: the active workbook becomes the txt file. Is there a way to export and continue editing the original file? – Sparkler Dec 15 '17 at 15:56
  • I'm not quite sure what you are asking -- saving doesn't close the workbook and you can still edit afterwards. Perhaps put `Cancel = True` in the body of the event handler -- though you will have to find a way of bypassing the event handler if you go this route (or perhaps have a public Boolean flag which the event handler checks to determine if you really want to save). – John Coleman Dec 15 '17 at 16:04

1 Answers1

1

Save the file as a macro-enabled file (xlsm), enabled macros, go to Developer->Visual Basic and insert the following code into VBAProject->ThisWorkbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim sh As Worksheet
    Set sh = Application.ActiveSheet

    Dim s As String
    s = ActiveWorkbook.FullName
    s = Replace(s, "xlsm", "txt")

    Dim wb As Workbook
    Set wb = Application.Workbooks.Add
    sh.Copy After:=wb.Sheets(wb.Sheets.Count)

    wb.SaveAs Filename:=s, FileFormat:=xlText, CreateBackup:=False
    wb.Close

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub
Sparkler
  • 2,581
  • 1
  • 22
  • 41