Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

VBA ExcelWrite to Output File w/out Overwriting Everytime?

  1. Aug 19, 2009 #1
    Okay then :smile:

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

    Code (Text):
        '   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?

  2. jcsd
  3. Aug 19, 2009 #2

    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

  4. Aug 19, 2009 #3
    Code (Text):
    Static Counter As Integer
    Counter = Counter + 1
    MyPath = "Z:\Casey B\MathML Converter\" & "MathMLAsString" & LTrim(Str(Counter)) & ".txt"
  5. Aug 19, 2009 #4
    The above works but you should zero the counter after a certain point. Right? (At least I would)

  6. Aug 19, 2009 #5
  7. Aug 19, 2009 #6
    To reset your report numbers back to one. Why else?
  8. Aug 19, 2009 #7
    Well, I guess that would be fine whenever he wants to overwrite his files.

    Also, he needs a way to save the counter, otherwise it will reset to 1 each time the application starts.
  9. Aug 19, 2009 #8
    Here's another method that does not require the Counter to be saved.

    Code (Text):
    Counter = 0
        Counter = Counter + 1
        MyPath = "Z:\Casey B\MathML Converter\" & "MathMLAsString" & LTrim(Str(Counter)) & ".txt"
        If Dir(MyPath) = "" Then Exit Do
    fNum = FreeFile()
    Open MyPath For Output As fNum
    Print #fNum, EntireMLText
    Close #fNum
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook