VBA ExcelWrite to Output File w/out Overwriting Everytime?

  • Thread starter Thread starter Saladsamurai
  • Start date Start date
  • Tags Tags
    File Output
Join the discussion
Registration is free. Ask a follow-up in this thread, or start your own.
Sign up Log in
7 replies · 30K views
Saladsamurai
Messages
3,009
Reaction score
7
Okay then :smile:

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

Code: 
    '   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
 
Physics news on Phys.org
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
 
Code: 
Static Counter As Integer
Counter = Counter + 1
MyPath = "Z:\Casey B\MathML Converter\" & "MathMLAsString" & LTrim(Str(Counter)) & ".txt"
 
The above works but you should zero the counter after a certain point. Right? (At least I would)

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

Thanks
Matt
Why?
 
To reset your report numbers back to one. Why else?
 
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.
 
Here's another method that does not require the Counter to be saved.

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