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]

Apologies for pushing this back up to the top again, but if someone could even just give me enough to get started (i.e. the sort of thing I could search for here or in VBA help) I'd be most grateful.

Thank you all!
Have a great weekend!

AJ

 

[daleyman]

nice question.




Sub output_file()
'determine number of columns from row 1

maxcol = 1
Do Until Cells(1, maxcol + 1).Value = ""
maxcol = maxcol + 1
Loop

'output file until value in column a is empty

Open "c:filename.csv" For Output As #1
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
strg = ""
For colx = 1 To maxcol
If colx > 1 Then strg = strg & ","
strg = strg & Chr(34) & Cells(rowx, colx).Value & Chr(34)
Next
Print #1, strg
rowx = rowx + 1
Loop
Close #1
End Sub

 

[Yogi Anand]

Hi AJ:
Forgive me if I am misreading you -- would a TEXT FILE not always be a single column file?

 

[daleyman]

would be a single column on the file i guess, but each row is made up of columns with each value in quotes and delimited by a comma....


"A1","B1","C1","D1"
"A2","B2","C2","D2"

hence .csv (comma delimited text-file)...

heh, does anyone know what C-S-V actually stands for, i'm guessing comma-something-something....

 

[Legacy 98055]

Comma Seperated Values

 

[daleyman]

ah ha! hee hee

 

[Legacy 98055]

Daley,
Am not sure, but since you are assigning all values to one string, you may lose data type info?

Might want to use the write statement with an array.

Example:

Redim ToCsv(1 to maxcol)

Select Case maxcol
Case 1
Write#1,ToCsv(1)
Case 2
Write#1,ToCsv(1),ToCsv(2)
Case 3
Ect...
End Select

Tom

 

[daleyman]

yeah, understand your point even if not your code. case if would certainly be able to tackle the scenarios, depends what the application is.

Personally, i wouldn't put quotes around everything either, just use commas, keeps the file size down, but it was in the question.

 

[nisht]

Why dont you try this way
sub cvsfilesave ()
thisworkbook.SaveAs "d:/nisht",xlcvs
end sub

ni****h desai