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?

    Thanks,
    Casey
     
  2. jcsd
  3. Aug 19, 2009 #2
    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
     
  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)

    Thanks
    Matt
     
  6. Aug 19, 2009 #5
    Why?
     
  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.

    edit:
    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
    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
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: VBA ExcelWrite to Output File w/out Overwriting Everytime?
  1. Excel macro's and VBA (Replies: 18)

Loading...