Smart macro to create proper CSV files?

[AJ]

This is a follow up from an old post...

http://www.mrexcel.com/board/viewtopic.php?topic=3508&forum=2

Babytiger wrote a beautiful bit of code (below) to create proper a CSV file
(i.e. with quote marks around each field).
I wondered whether anyone can help me refine it for my purposes.

Open "C:FileName.txt" For Output As #1
For i = 1 To 10
Print #1, """" & Range("A1").Offset(i - 1, 0).Value & """" & "," & _
"""" & Range("A1").Offset(i - 1, 1).Value & """" & "," & _
"""" & Range("A1").Offset(i - 1, 2).Value & """"
Next i
Close #1

I have to create files like this all the time. Unfortunately, they are always different with a variable amount of rows and columns. While I can easily see how to adjust the code for the number of rows to create, I've been racking my brain and just cannot work out a way to make it so that it processes a variable number of columns.

Basically, the perfect final version of this macro for me would be to read the number of rows and columns in the spreadsheet and create a file accordingly without the user having to edit the macro in anyway.

Can anyone help?
It's driving me nuts as I know I could be so close to a wicked solution that would save me hours and hours of formating!

Rgds
AJ

 

[AJ]

Thank you all very much for the help.
Daleyman was absolutely spot on so thanks very much.
TsTom, like Daleyman I think I understand your points but as yet lack the know how to take them further! However, as Daleyman's code works beautifully, I'm a very happy chappy!
nisht, thanks for the input, but that way doesn't put in the quotes around every field.
It is of course fine, if the quotes are not essential. I just have a rather backward package where they are essential!

Cheers!
AJ