export excel columns to TXT file, only if there is data

[jlm13]

I have an excel doc with 7 columns of data pulling from another sheet, each column in 45 rows deep and after the 45 rows as 2 rows of formatting i need to enter. I need a seperate TXT file for each column (or set of 45 rows).

soo it looks something like this

A b c d
1 1 1 (blank)
2 2 2
3 3 3
4 4 4
.. ... 33
45 45 end
end end file
end file

I need a macro that will look in each of the rows and first see if there is data, so it will only export if there is data to export. then it needs to know where the data ends and in which column as each column needs to export to its own TXT file.

Finally, I need it to say end and end file in the last two rows 46 and 47 in the txt file for each file.

Im guessing i need a loop of somesorts but i have no idea how to build it.

Any help would be great

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[jlm13]

This is what i currently have but i go through the same logic a few times, i'd liek to loop it

Please Login or Register  to view this content.

And then i go through the same code several times for each set of data (as the sets are in columns of 45)

[jlm13]

I tried the macro posted but it doesnt seem to return the data in the right form.

I need it to stay in column from in the text file. And i need separate text files for each distinct set of 45 rows of data.

Any help would be great

thanks

[JBeaucaire]

The macro writes each column of data into a text file of its own. It adds the terms "End" and "End File" at the bottom of each data set.

[jlm13]

Please Login or Register  to view this content.

this is what im currently doing and it keeps returning data from teh wrong tab, i want it to return data from tab "paste" and it is still only rertuning 1 file

[JBeaucaire]

From the wrong "tab"? This macro returns the data from the activesheet, whatever you have onscreen at the time you activate the macro.

I provided a sample sheet and when it is run it returns the three sample text files. You should receive the same results when you run my test file Book1.xls.

I can't really comment on what's happening with your file since I haven't seen it, but the code is pretty basic...

1) copy all the data in first column and save as a text file with two comments added at the bottom
2) repeat with next column, and next until column reports as empty

[jlm13]

Attached is the spread sheet, right now it has fake numbers but this is what it looks like

I've also attached what i would like the text exports to look like. If you could tell me what im doing wrong that would be great. Thanks

[JBeaucaire]

In the future, START your question with a sample file and sample output, like you've just done. We could have finished by post #2...

1) Let's eliminate those hidden columns. (that's why it probably only created one file for you)
2) Now that I see that the data really starts on row 19, we'll adapt for that, too.

[Paul]

JLM13,

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

[JBeaucaire]

Seeing as you just posted elsewhere, I'll assume we're done.

[jlm13]

havent had a chance to test it out yet, but thanks for your help. I'll let you know in the morning

it seems i've violated enough rules for tonight

[Paul]

If you intend to post back to this thread or any other, your first reply should be a link to the other thread per the rule stated.

[jlm13]

http://www.mrexcel.com/forum/showthread.php?t=454080

Sorry again

Ok soo the code is breaking on

Please Login or Register  to view this content.

application defined or object defined error

[Paul]

Change "X1UP" to "XLUP". Somehow a number one got in there where an L should be.

[JBeaucaire]

You should be able to simply cut-n-paste the code from the sample workbook above into yours instead of typing it out. That should eliminate translation errors.

[jlm13]

ok so i get a file but with the formatting needed, but my vaules arent there

Could this be bc they are forumlas pulling the values from other spreadsheet on the excel doc?


it shows up like

formatting


start of data







End of file
end of data

Is it not seeing teh data b/c its formulas?


Also it isnt exporting a text doc, its some TXT1 file, whats the formatting (xltext?) for text doc, i googled it with no results

[jlm13]

ok figured out the problem above, everything is exporting fine and it all works great.


Finally

the program is making more txt files for every row as i have a header and a "start of data" in all rows. If there anyway i can count how many rows have data in row 20, and then set it to only loop through that many times?


or is there any way to add "start of data" to the rows like end of file and end of data are being added at teh bottom?

[JBeaucaire]

If you've edit the macro, I may need to see that. I thought I put a check in that only exported columns with data after row 10:

Please Login or Register  to view this content.

You can adjust that up to 19 or 20 and see if that resolves it.

[jlm13]

Please Login or Register  to view this content.

I was thinking abotu making another variable that counts the rows perhaps?

[jlm13]

i've tried changing the LR>10, to 10-20 all the numbers inbetween and im still getting 7 txt files everytime i run it.


Any other ideas?

[JBeaucaire]

Post your file again with your macro in it. Let me look again.

When I run it on my system exactly as shown and with your file, I get 3 text files.

[JBeaucaire]

Wait, try this alternate version. I do a search in row 20 to see just how many columns have data in that row.

Please Login or Register  to view this content.

[jlm13]

i'll let you know how it goes in the AM, but it worked on my netbook with a dummy file

Thanks

[jlm13]

so its not working in the workbook i need it to. And i know why.

I have an "index" function pulling the values from another tab in the workbook, and if it doesnt find data i have it returning "". So some cells look empty but have a formula in them

When i paste all the values to another tab and run the macro it works.

is there a way i could have the macro look in row 20 and if the value is >1 copy? or will that still pick up the formula

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[jlm13]

still getting the same problem.

it works if i copy the values to another workbook and run it.

So it must be the formulas, but i cant figure a way around it.

[JBeaucaire]

Let's see those formulas.

[jlm13]

cant post the workbook

but this is the formula that is in the cells

=IF(R21=0,"",INDEX(Output!$A51:$A$320,1))

output is the othertab that has a data import on it, that im indexing to get the data to the tab the macro is on.

[JBeaucaire]

That looks fine to me.

When I use formulas like that on your sample sheet, with only real values in the first two columns, the dCol evaluation comes up to 2 for me.

[jlm13]

that happens for me too.

but when i add the formula and run it, the macro picks up the formula as a value and spits out 7 files

[jlm13]

i figured it out, thanks for the help everyone

i set the variables = to cells in excel and for the row count i did a sum

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

*phew*