How to populate a .CSV file using ADO

[Glory]

http://msdn.microsoft.com/en-us/library/ms974559.aspx

This tells how to query text files. But I need to know how to populate one, preferably without opening it.

Can anyone get me started?

 

[Ruddles]

About two-fifths of the way down that page it says:-

Note You'll notice that we keeping talking about reading text files, and never say anything about writing to text files. Why? Well, unfortunately, the ODBC Text Driver (which is what we're using here) can only read from files; it can't write to them. To write to a text file, you'll have to use the FileSystemObject or something similar.

 

[Glory]

I totally missed that, thanks. From VBA help under FileSystemObject Object:

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)
a.WriteLine("This is a test.")
a.Close

How would I do this with tens of thousands of rows of data? Would it be possible to just insert an entire range all at once?

It's really fast returning values from a csv file or saving as one... I'm hoping writing to one will be just as quick.

 

[Ruddles]

It'll be slower as it'll have to loop through the rows doing a WriteLine for each one.

Can you not do this:-

ThisWorkbook.SaveAs Filename:="whatever.csv", FileFormat:=xlCSV

Or do you need to export only certain parts of the worksheet?

 

[Glory]

I need to turn sheets from multiple closed workbooks into .CSV files. The key being, without opening them. I haven't found anything yet that looks remotely promising. Do you have a clue what I might do?

 

[Glory]

I should have said:

I need to turn sheets from multiple closed workbooks into a single .CSV file, without opening the .xls files.

Open MyCSV.csv For Append As #1
 
For i1 = 1 to LastUsedRow
 
    For i2 = 1 to LastUsedColumn
 
        Cells(r, c) = a
 
        FileSystemObject.Write a & ","
 
    Next
 
Next

Something like that, maybe? Except, is there a way to write a whole row or range at once, instead of just a single cell's value?

 

[Ruddles]

I don't know how to read the contents of an Excel workbook without opening it.