Saving CSV file and sending to a folder over and over again

**scottymelloty** · 01-12-2009, 08:21 AM

Thanks for any help in advance

I have this code

Sub save_file_as()
Sheets("CSV").Select
        ActiveWorkbook.SaveAs Filename:= _
        Range("'MATCH SETUP'!B34").Value & ".csv", FileFormat:= _
        xlCSV
          Sheets("Calculator").Select
End Sub

so when i run this macro it saves the sheet caleed CSV as CSV file as a name from another sheet which changes all the time,, this works ok

however my problem is this only works once because when i run the macro again it cant find the Sheet called CSV anymore because it has renamed it from the previous running of the macro

so i need to be able to run the macro whenever i want over and over again

also it would be good not only could i save it by running the macro via a form button but also say it saves itself every 10 seconds, that would be a bonus

hope ive explained it well

thanks
scott

**scottymelloty** · 01-12-2009, 09:21 AM

If anybody has another way of doing it that would be good as well

i need the CSV sheet saving as a csv file to the path and name set in cell 'MATCH SETUP'!B34 , this cell name will change ie it will put the time it is saved in the name

so i need to save that file on demand when running the macro and hopefully say every 10 seconds as well (but that bit could wait)

maybe it could open a blank workwook, paste the data from CSV sheet onto the new workbook then save and send that new work book as a csv file to the path ,, maybe thats even more complicated !!

any ideas the less complicated the better would be good

thanks very much

scott

**scottymelloty** · 01-12-2009, 11:36 AM

I Have attached a file to show what i mean if it helps

you will have to change the path name on the MATCH SETUP tab in cell B32 so it can save somewhere on your own computer to test

so on the Calculator Sheet when you press that button it will save the datat on CSV sheet as a CSV file to the path specified which is what i want

however when you rpess the button again it dosnt like it because the sheet CSV dosnt exist anymore because it has been overwritten with a new name from the csv file last saved

**scottymelloty** · 01-13-2009, 05:51 AM

any ideas anyone ? could do with some help or a pointer in the right direction please

**scottymelloty** · 01-13-2009, 02:48 PM

help

any code to change a worksheet name back to original name would help

thanks

**scottymelloty** · 01-13-2009, 06:13 PM

ok ive taken itrom a differnet angle using some code ive found and changed a bit this works nicely wiht one exception,, it creates a copy of all the worksheets in my workbook

i woul dlike it to just copy one of the worksheets in my workbook , in this case the worksheet called CSV

What do i need to change ?

Sub Copysheet()
    Dim sht As Worksheet
    Dim NFName As String
    Const WBPath = "C:\Users\Scott\Documents\"

    For Each sht In ActiveWorkbook.Worksheets
        sht.Copy
        NFName = WBPath & sht.Name & ".csv"
        ActiveWorkbook.SaveAs Filename:=NFName, _
          FileFormat:=xlCSV, CreateBackup:=False
        ActiveWindow.Close
    Next
End Sub

thanks

Saving CSV file and sending to a folder over and over again

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Saving CSV file and sending to a folder over and over again

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions