Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save to TXT (2000)

    Hello,

    I'm trying to create a sheet that has autosave on for every 5 min. Everytime is saves, I'm running VBA in the "Thisworkbook" to save as a txt in the same location on the PC, overwriting the file without asking if it's OK.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Application.DisplayAlerts = False
    ActiveWorkbook.Save Filename:= _
    "Cocuments and SettingsUserDesktopstats.txt", FileFormat:=xlText, _
    CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub

    This keeps asking where I want to save and if it's OK that it's overwriting the orginal. The main goal is to have the excel data export to a text file automatically, every 5 min without Any user interaction.

    THANK YOU!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save to TXT (2000)

    I don't understand. If you save the workbook as a text file, it'll save only the active worksheet, and remove all VBA code.

  3. #3
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    Hello,

    Thats my goal. I just need the text from one excel sheet to auto save every 5 min to a text file, overwritting that file. The main excel sheet, I can manually save as an excel sheet, at the end of the day to keep all the VBA coding.

    The purpose is this. We have 4 excel sheets. I want 1 master sheet to look at those 4 with a formula, gathering info on those 4 and showing it on one sheet. The one "master" sheet then needs to save the values it's gathered every 5 min to a text file so I can have Access pull the info from that text file for other purposes. The VBA could acually save as a txt and an xls, overwriting both files everytime without checking if it's ok to do so.

    Hope that helps :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save to TXT (2000)

    Try this:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
    SendKeys "~"
    ActiveWorkbook.SaveAs Filename:="Cocuments and SettingsUserDesktopstats.txt", _
    FileFormat:=xlTextWindows, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub

    The SendKeys instruction simulates pressing the Enter key in the dialog.

  5. #5
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    it's almost there...It's still asking to choose A file location, and confirm writing over an existing file

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    I believe that if you set Cancel to True you would not need the somewhat unreliable SendKeys. I would also disable events. Something like this:

    <code>
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Cancel = True
    ActiveWorkbook.SaveAs Filename:="Cocuments and SettingsUserDesktopstats.txt", _
    FileFormat:=xlTextWindows, CreateBackup:=False
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    End Sub
    </code>
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    See my modification to Hans' code.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    still getting the confirm screens...

    Rather then go by the save as...what about a plan save or even "export"?

    Thanks for the continued help!!!

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    Could you upload a workbook that shows the problem (alter or delete any proprietary data)? If I run that code in a test workbook I create I do not get the confirm screen.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    Heres what I have...I made it past all the screens, except the error : stats.xls may contain features that are not compatible with text (tab delimited). Do you want to keep the workbook in this format? Yes or No.

    if it's easier, It doesn't have to be a tab delimited...it can be any type of text file....

  11. #11
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    Not sure if this matters: I have office 2k and it's not working on that one (thats the one I need to to work on) and I used the same VBA on office XP and it worked (with the exception that I explained above). The reason I need it to work on office2k is because XP doesn't have the autosave feature. If you know how to make an excel sheet autosave every 5 min on office XP, then we may be set :-)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save to TXT (2000)

    Setting Cancel to True would mean that the actual spreadsheet (the .xls file) will never get saved.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    When I save the workbook, I do not get the confirm message. Would you give a detailed explaination of what you do that results in the message?
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to TXT (2000)

    It will never get saved anyhow since once this procedure is run once, the file name is changed to the .txt file name.
    Legare Coleman

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save to TXT (2000)

    So it's impossible to save this code - once it is inserted, the workbook is never saved as a .xls, only as a .txt. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •