Automate data from Excel to csv file

[imy1971]

HI

I have set up a excel spreadsheet that I wish to transfer data to a csv file that sites on an ftp site. I have set up the ftp via the office network and now sits in the window explorer folder.

I would normally copy and paste the data but doing this on a daily basis is too much.

1. What is the best way to transfer data across to the csv file ?

2. I heard you can use indirect function in excel I am not sure what it does

Cheers

Imy

 

[p45cal]

You want to append data to an existing csv file, or create a new one/overwrite one?

 

[imy1971]

Yes i would like to append the data on a daily basis from excel to a csv file on a ftp site or a shared drive

 

[p45cal]

Is it a whole single sheet workbook or just a range in a workbook (or something else) which needs to be appended to the csv file?

 

[imy1971]

Yes it will be a range from the worksheet. Or tell me what is the easiest

Thanks

 

[p45cal]

The macro could be something like this:

Sub blah()
AppendToACSVFile "C:\Documents and Settings\Someone\My Documents\appendToMe.csv", ActiveSheet.Range("A1:C6")
End Sub

where, of course, you have to adjust:
1. for your file path and file name (the file should exist already, even if it's empty)
2. for the sheet and range you want to export

This needs to be supported by another macro:

Sub AppendToACSVFile(FullPathAndFileName As String, TheRange As Range)
Delimiter = ","
Set fswrite = CreateObject("Scripting.FileSystemObject")
'open file
Set fwrite = fswrite.GetFile(FullPathAndFileName)
Set tswrite = fwrite.OpenAsTextStream(8, -2)

For Each rw In TheRange.Rows
  FirstCellInRow = True
  For Each Cll In rw.Cells
    If FirstCellInRow Then
      OutputLine = Cll.Value
      FirstCellInRow = False
    Else
      OutputLine = OutputLine & Delimiter & Cll.Value
    End If
  Next Cll
  tswrite.writeline OutputLine
Next rw

tswrite.Close
End Sub

Now you need just run blah.
This is probably overkill, as I have just adjusted some code I already had, which was built for more flexibility than you seem to need, so there's probably a more elegant solution, but it was easier for me rather than write from scratch.