Write Sheet to text file, comma delimited.

I would like to add a button to the master sheet of my workbook, that when pressed, will write the contents of the data sheet to a text file in comma delimited. I have found several examples of source code for VBA that accomplish text file writing, but I have not found a way to modify them where they function properly.

I have attached a sample worksheet that I need printed (written) to the text file, however, mu main issue is that I cannot make the write function dynamic, where it only writes the rows containing data (including headers). The amount of rows on the data sheet change over time, so I wanted to ensure that the text write code will scan all the rows in the sheet, and writing only those containing data/values/text.

It would help if you posted the code you have and explain why it does not quite solve your problem.

Originally Posted by Andy Pope

It would help if you posted the code you have and explain why it does not quite solve your problem.

I guess it would, eh? Sorry about that.

The code below I had found while searching this forum, but I am unsure of how to manipulate it to accomplish what I need:

Please Login or Register  to view this content.

The code/macro will be attached to a button on the main sheet, which is why I use the Sheets("ASCII01").select to switch to the sheet I want to write.

I just need to modify the code to scan the worksheet and write all the applicable rows, comma delimited.

Please edit as necessary, and I do appreciate your work on the matter.

Cheers, S/T

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please Login or Register  to view this content.

Thank you for the correction.

Please Login or Register  to view this content.

I used the code insert like you described; re-read the forum rules as well.

S/T

I was think more along the lines of correcting the post rather than reposting it

Anyway you now know about code tags and how to use them.

Did the correction sort your problem out?

I will test it out; also using this as a learning tool by researching all the code and learning how it is used to achieve the results.

The code worked great; the only issues I still have are:
1. The time and date were written in Excel serial number formats, rather than the standard "24-May-2010" and "10:00:03". This may be just a case of me not formatting the cells as Date/Time on my calculation sheet.
2. In the declarations, can I tell VBA to create the folder "Temp" in the C directory if it does not exist? This workbook will run on different users' computers, so this may be required.


Just for my reference, what is the #FB? How does the file buffer work in this case?

Thank you.

S/T

Try this,

Please Login or Register  to view this content.

I have changed FB to intUnit. The is a pointer to the File stream.

I have added code to use the number format. You may need to refine this for other data sets.

Thanks Andy.

As for the formatting, I just removed all cell formatting in the master sheet; but regardless, the code works nicely.