How do I export populated cells to a TXT file

**Tworsey** · 07-17-2011, 11:08 PM

Hi all,

I'm completely new to VBS and need to complete a project by 9am (BST) this morning!
I've tried recording macros etc but just cant get what Im trying to do to work.

Basically I have a sheet on a workbook named 'transfer' It is auto populated from various sources. I trying to create script I can allocate to a button that will select all populated cells on the 'transfer' sheet and save them to a txt file need Transfer.txt

Any help will be greatly appreciated.

**rylo** · 07-18-2011, 12:16 AM

Hi

Bit more detail on the structure of your sheet, what the data looks like, order the cells are to be processed, any concatenation, separation characters etc, etc.

rylo

**Tworsey** · 07-18-2011, 02:35 AM

Hi rylo,

Thanks for replying.

The purpose of my sheet is to create a txt file that is used in another script that uploads .txt files saved in another location to members in a dataset on an IBM mainfrane. (if that makes sense!)

The sheet uses columns A-E. The user only has access to type or paste an eight character jobname in column B. When they populate this column the other columns will be auto populated. The breakdown of column content is as follows. Only data in columns B & D will ever change.

A) q:\sw ops ins to tso prose\jobs\
B) J1234567
C) .txt text2~'CSDSHIFT.CA7.PROSE(
D) J1234567
E) )'

The ultimate goal is that it will be saved in a txt doc in the following format...

q:\sw ops ins to tso prose\jobs\J1234567.txt text2~'CSDSHIFT.CA7.PROSE( J1234567)'

Hope that helps, sorry if this is really easy and I'm being stupid here I dont have a lot to do with VBS.

Regards,

Tom

**MrShorty** · 07-18-2011, 10:55 AM

A couple of thoughts:

1) For someone unfamiliar with VBA, I would think the easiest approach would be to avoid VBA altogether. As needed, program the spreadsheet to organize the data the way you want it to appear, then use the Save As... command to save the spreadsheet as a text file (tab delimited or comma delimited as needed). If you need a different delimiter, it would be easy enough to use the CONCATENATE() function to put everything into one cell that belongs in each record, then copy that down a column. Then Save As a text file.

2) When I've done this sort of thing using VBA, it has involved (and I found all of these adequately described in VBA help to put my code together):
a) the OPEN statement to create the text file
b) a loop that loops through each record in the text file
c) within that loop, code to collect the data for each record and store it in a string variable
d) a PRINT statement to write the record to the text file

Hope that helps

If I understand how you're data is laid out (assuming the data is in sheet1), the 1st option should work well. In column A of sheet2 put =CONCATENATE(sheet1!A1,sheet1!B1,sheet1!C1,sheet1!D1,sheet1!E1). Save sheet2 as a txt file.