.xls to .txt file auto update on save

Good Morning,

I am trying to change the file location that the file is created in from "MyDoccuments" to "\\prod\userdata\IS-3\FSD-SysDev\HD\MonthendHosp\Monthend Automation processes "

I am also trying to get the Before save function to work on my code so that it automatically updates the .txt file when the user hits save.

Here is what I have:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
Next N
Option Explicit
Sub SaveAsPipeDelimited()
Const DELIMITER As String = "|"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
Dim N As Long
nFileNum = FreeFile
Open "EOMStatsForKirk.txt" For Output As #nFileNum
For N = 1 To ActiveSheet.UsedRange.Rows.Count
Print #nFileNum, Cells(N, 1) & "|" & Cells(N, 3) & "|" & Cells(N, 5) & "|" & Cells(N, 12)
Next N
Close #nFileNum
End Sub


Please advise when possible. I need to have this completed today! Thank you so much for your help in advance.

Thank you so much for the help. That did change the file location for the created text, howver, the .txt file still will not update on save. I have to run the macro to get it to update.

Do you happen to know how to do this?

Please Login or Register  to view this content.

The message box is not needed for the _Before save and I am not sure if I need to save as the workbook_beforesave or the Sheet1_Beforesave. The ultimate goal is to have the .txt file updated without prompting the user "behind the scenes". The code is supposed to look like this:

Please Login or Register  to view this content.

Found here :

HTML Code: 

I have the concept but I am not sure how to tie it together.

Thanks again for all your help.

I have to have the .txt file to report as pipe delimited. I am having the code pull out columns A,C,E& L and report as a pipe delimited .txt file. The text file has to update behind the scenes when the user hits save. I added the code in red and it still would not update the .xtx file without me having to run the macro.

Please Login or Register  to view this content.

If I replace my code with this it does not create a .txt file at all and there is not a macro to run.

If it helps my code is not in the "ThisWorkbook" module it is in "Sheet1" Or " EOM Stats"(As it is named)

I moved the code to "ThisWorkbook" and the code works perfectly! Thank you for all of your help!!