VBA ExcelWrite to Output File w/out Overwriting Everytime?

[Saladsamurai]

Okay then

Here is a piece of the code that I am using:

    '   Send to File
    MyPath = "Z:\Casey B\MathML Converter\" & "MathMLAsString.txt"
    fNum = FreeFile()
    Open MyPath For Output As fNum
    Print #fNum, EntireMLText
    Close #fNum

This saves my work to a text file called "MathMLAsString"

What I would like to do is set it up so that everytime a run the code it saves the text file as a newer version so I can compare text files (i.e. MathMLAsString is not overwritten).

Something like MathMLAsString1.txt, MathMLAsString2.txt, etc...

I was thinking of starting a counter, but I do not know how VBA will know to increment it every time the code is run?

Any ideas?

Thanks,
Casey

 

[CFDFEAGURU]

Hello,

Here is one that I wrote to stop the overwriting. This routine is to send data from Access to Excel. It does the increment by 1 method you were looking for above. Just modify it however you like.

Private Sub cmd_Export_Click()
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim Path As String

Path = "I:\Access2000\Excel Spreadsheets\W Shape Properties.xls"
Set objBook = Workbooks.Add(Template:=Path) 'Your excel spreadsheet file goes here
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("Sheet1") 'Name of sheet you want to View
objBook.Windows(1).Visible = True
objApp.Visible = True

End Sub

Thanks
Matt

 

[TurtleMeister]

Static Counter As Integer
Counter = Counter + 1
MyPath = "Z:\Casey B\MathML Converter\" & "MathMLAsString" & LTrim(Str(Counter)) & ".txt"

 

[CFDFEAGURU]

The above works but you should zero the counter after a certain point. Right? (At least I would)

Thanks
Matt

 

[TurtleMeister]

The above works but you should zero the counter after a certain point. Right? (At least I would)

Thanks
Matt

Why?

 

[CFDFEAGURU]

To reset your report numbers back to one. Why else?

 

[TurtleMeister]

Well, I guess that would be fine whenever he wants to overwrite his files.

edit:
Also, he needs a way to save the counter, otherwise it will reset to 1 each time the application starts.

 

[TurtleMeister]

Here's another method that does not require the Counter to be saved.

Counter = 0
Do
    Counter = Counter + 1
    MyPath = "Z:\Casey B\MathML Converter\" & "MathMLAsString" & LTrim(Str(Counter)) & ".txt"
    If Dir(MyPath) = "" Then Exit Do
Loop
fNum = FreeFile()
Open MyPath For Output As fNum
Print #fNum, EntireMLText
Close #fNum