Excel and csv , txt 1

[sweetleaf]

Do anyone know how to (within excel) create a text file
and put the values of the excel rows into it?

Reason is, I have an excel spreadsheet which I used to
save as a csv - but as you all probably already know -
csv's have a tendency to convert long numbers into
scientific numbers -- and b/c a db job reads these
csv's into a db table, such #'s will create errors in the db.
so, what i want to do instead of relying on an excel
to csv conversion, is have a command button on the
excel spreadsheet which when clicked will stick commas
b/w each cell value and parse the data out to a .txt
file.

I already know how to get the cell data and place the
commas b/w them but don't know how to:
1) create a text file within excel vba
2) output/write the cell data to the file
3) save and close the file

-- one more thing, is it possible to have it so that
after the text file is created it gets emailed to a
specified (hard-coded) address?

Thanks!

 

[ETID]

Sub build_text_file()

Open "c:\my_text_file.csv" For Output As #1

Do Until "your condition here"

Write #1, '"your_string_with_commas"
'"code to do stuff to get next the next string to write to file"

Loop

Close #1

end sub

 

[sweetleaf]

Thanks plenty ETID!

I noticed that what gets written out to the txt file gets enclosed with quotes -theres a " at either end of each record. I tried using replace(strName,chr(034),chr(011))
and that didn't work - do you have any suggestions?

Thanks!

 

[ETID]

Look in the help for ...

Print # statement


see if you can use this as an alterate to the write statement.

 

[sweetleaf]

you're awesome - thank you!

 

[Cresby]

check-out the command/method

FORMAT(variable/cell reference,"print format definiton")