Exporting files to text files

Dear excel specialists,

I made a large excelsheet with a lot of variables. Now i want to use this sheet to print/export a certain cell range to text format.
The reason behind this is that the textfile will be used as input for another program.

I already created a macrobutton with a popup window.

Now i want to ''generate'' a couple of files with different cable lengths/waterdepths (for example: 100m to 10m with 9 intervals is 9 files)

To save the textfile i want to use the name in cell V3 (linked cell)

I only need to print cell range F3 to N104. With different variables as waterdepth or cablelength.

How can i fix this? I already trying this for days without any good results...and yes i already tried to follow examples as:

http://www.youtube.com/watch?v=Kn_4I2RZu8c
http://www.cpearson.com/excel/ImpText.aspx

Kind regards,

Erwin

cpearson's code looks like it should adapt just fine to your situation. Can you be more specific in your questions/problems? You say you have not go any good results. What bad results have you got? Why are they "bad"? If you can help us understand the specific ways that cpearson's code is not working for you, I'm sure we can suggest ways to adapt it so it can work for you.

Hi MrShorty,

First of all, thanks for the quick reply.

This is my first contact with VBA and i thought...when i simply copy the cpearsons code it will do the job...but the error i get is ''expected end sub''..

See the new attachment.

When i hit the ''generate'' button it should start the Sub DoTheExport() right?
After that i also copied the code to a ''module'' DoTheExport.

I saw a howto video on youtube but unfortunately it starts after copying the data to the userforms and modules. And this is the part where it goes wrong in my sheet.
When i have this running i can extend the code with more intelligence.

To be short, i'm not able to write even a single textfile so far....

Erwin

Unfortunately, I'm not in a position to view your file, so I can't see specifically what you've done.

This is my first contact with VBA and i thought...when i simply copy the cpearsons code it will do the job...but the error i get is ''expected end sub''..

That sounds like somewhere in the copy and paste, an "End Sub" statement did not make it into your module. Every sub procedure starts with a Sub name() statement and ends with "End Sub". Examine your code and make sure there is an End Sub statement for each Sub statement.

When i hit the ''generate'' button it should start the Sub DoTheExport() right?

Assuming the button and the macro are correctly linked, yes. You can also execute the procedure from the Macros dialog, or you can invoke the run command in the visual basic editor.

It sounds like at this point you just need to get the syntax correct so that visual basic can compile the module.

Other than some comment lines that aren't preceeded by an apostrophe, I don't see any immediate errors, and the code seems to run fine (at least the DoTheExport procedure runs).

Either I'm unable to replicate your problem, or I still don't understand what the problem is.

@ excelvraag

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

@ Cutter, sorry, i'll do it from now on. Thanks for the help

@ MrShorty,

See the attachments for a better picture for my problem.

What I want is the following:

1:You hit the OFFPIPE File generator
2:A popup window appears and you must fill in box 2A or 2B
3:Hit generate and he will save the cell range F3:N104 to a textfile with the name "10metre.txt"

And my idea is to create a code that if you fill in the number 5 (for exampole) in the box "intervals".
Test1.jpg
he will make 5 files with the following names:

10metre.txt
20metre.txt
30metre.txt
40metre.txt
50metre.txt

Is the problem clear enough now? otherwise i will try it again...

I really appreciate the help!

tx

It helps put the specific problem "how do I write a text file" into the broader context of what you are trying to accomplish.

It still seems like the heart of the overall procedure is writing the text file. Once the code for writing a text file is in place, the additional code needed to loop through multiple files and manipulate the file name can be added without too much difficulty.

At this point, have you got it to write a text file?

Originally Posted by MrShorty

It helps put the specific problem "how do I write a text file" into the broader context of what you are trying to accomplish.

It still seems like the heart of the overall procedure is writing the text file. Once the code for writing a text file is in place, the additional code needed to loop through multiple files and manipulate the file name can be added without too much difficulty.

At this point, have you got it to write a text file?

Sorry for my late reply, i was working abroad for one week.

I commenced my troubleshooting on this excelsheet but unfortunately i didn't succeed.

I also tried the 'debug' option to locate the problem (now i use a dutch input VBA) when i run the VBA. Result/Screenshot is attached.

But to answer your question...at this point i haven't got it to write a text file

Mr.Shorty, do you have it running? If so, can you upload it so i can study the code in forms and modules?

Regards,

Erwin

From your screenshot, it looks like you are working from different code from what is posted in post #5. I'm using the code given in post #5. Here's the file and the sample output file.

Originally Posted by MrShorty

From your screenshot, it looks like you are working from different code from what is posted in post #5. I'm using the code given in post #5. Here's the file and the sample output file.

Mr. Shorty thanks for the help.

I'm now able to actually export a file to text. In your code you have the browser location for the textfiles.

FileName = "C:\Erwin\Test\Excel\test1.txt" 'Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
If FileName = False Then

So now the file is written to directory C:\Erwin\Test\Excel\ and than the filename. Is it possible to use the name (test1.txt) from cell R3C21?
Is it possible to save the file in the same folder as the excelfile itself? So if i copy the excel to my other notebook with a different directory it doesn't gives an error?

And the textfile is the same as yours. The upcoming problem now is that i don't want the "" signs in the file otherwise the program can't read the textfile. A space must be a space (see attachment for an idea of the required textfile.)

Also the textfile is quite long, I always want to expert cell range R3C6 to R104C6, is this achievable?

Thanks again for your help

So now the file is written to directory C:\Erwin\Test\Excel\ and than the filename. Is it possible to use the name (test1.txt) from cell R3C21?
Is it possible to save the file in the same folder as the excelfile itself? So if i copy the excel to my other notebook with a different directory it doesn't gives an error?

Yes. If you look in VBA help for the Open statement, you see that the filename argument is simply a string expression (any string expression) that evaluates to a valid file name. So you would look up the path for the current workbook (see VBA help for the Path property as it applies to the workbook object). Then use the value or text property to look up the filename in R3C21 (myworksheet.cells(3,21).value where my worksheet is a reference to the desired worksheet). Then use the concatenate operator (&) to piece those two strings together into the final file name.

And the textfile is the same as yours. The upcoming problem now is that i don't want the "" signs in the file otherwise the program can't read the textfile.

My file didn't have any quotes in it, so I'm not sure where they are coming from on your end. Try stepping through the code (set a breakpoint at an appropriate spot) and watch what it is doing to see if you can see which statement is adding the quotes.

Also the textfile is quite long, I always want to expert cell range R3C6 to R104C6, is this achievable?

As written, you will notice the "selectiononly" argument in the "exporttotextfile" procedure. Currently this is set to false, which causes the procedure to write the activesheet. If you select the desired range before running the code and set selectiononly to true, then it will use only that range. If you want to hardcode the range into the procedure, simply set a variable to refer to that range (Set myrange=activesheet.range("F3:F104"))

I know that all the "" sign are empty cells

The For ColNdx/Next ColNdx loop is where the text string is built for writing each line of text, so this is the loop you need to change if each row is not looking right. It appears that the code puts two Chr(34)'s (quotation marks I believe) when it encounters an empty cell. So I suspect you need to change this to what you want it to put in when it encounters an empty cell.

As for using the Selection object rather than the entire activesheet object change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then the code looks like it should only use the block of cells selected (the Selection object) for the export rather then the entire worksheet.

I'm not sure why your attempt to hard code the row and column numbers into the activesheet.usedrange failed. I expect it has to do with the way VBA handles only one index to the .Cells property. I have always used two indices in the cells property (.cells(3,6).row), so perhaps it isn't really seeing rows 3 through 104. If you continue in this way, why not simply use?

Please Login or Register  to view this content.

Awesome! This is a big achievement for me.
I used the simple code (last one) and it runs fine.

Now i will try to make a loop for different ''intervals'' which you have to fill in at the pop up window.

This is nice puzzling, many thanks!

@ excelvraag

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Cutter,

I knew it about marking the thread as solved, but the excelfile is 95% finished...I only need to connect the textboxes to the file and create a loop for different intervals.
I'm not sure if i can fix the loop with intervals, the textboxes should not be too hard to fix.

But i thought, first try to solve it myself and if i'm not able i ask it in this thread again (also the reason i didn't put it on solved yet).

Credits added to the people who helped.

I now fixed 2 seperate userforms which will pop up depending on a selection on the sheet.

That userform shows 3 textboxes. I would like to disable the ''generate'' button and when ALL 3 textboxes contain numbers the generatebutton will be enabled.
I tried different codes searched by using google, but didn't succeed so far. With one textbox i can do it but if i use ''and'' it goes wrong aparently.

Another question, how do i make a loop for different files?
in the textboxes you can fill in Startdepth, Enddepth, Intervals....so i would like to realize the following:

Startdepth=10
Enddepth= 50
Intervals= 5

Export 5 files with depths of 10, 20 30 etc.
When i increase intervals to 9 for e.g. it will be 10,15,20,25,30,35,40,45,50 etc.

Regards,

Erwin

Unfortunately, I never use userforms, so I'm afraid I can't help you with that part of the application.

Another question, how do i make a loop for different files?

I don't know what controls file name. Since filename is controlled by a simple string variable, looping through several files should be as simple as changing the filename string variable for each loop. How are you expecting to get the filename for each loop: prompt? increment a character in the filename (eg filename1, filename2, filename3)? store list of filenames in an array or range?

I don't know what your "depth" variable are controlling so I'm not sure what to make of that.

Actually the filename contains 7 characters which will change by changing input on the excelsheet
See attached file.

so the name is in this way for different depths or cablelengte always different/unique.
I need something in the following context (example):

- starting with using original cell values from excelsheet. (waterdepth = worksheet data cell A1 (for example 22metre)
- fill in userform with waterdepth and intervals. (start depth 10meter, end depth 30meter, intervals 3)
- waterdepth 22m. replaced by 10m.
- write file: bwDa010.DAT
- write file: (enddepth - startdepth = 20 / intervals -1 = 10m. per interval)..10metre + 10meter = bwDa020.DAT
- 20 m. + 10 meter is 30 so write file bwDa030.DAT.
- Close / end

It's quite hard to explain what i have in mind. Do you get the point?file1.jpg

Naming the file is done by concatenate several cells together.
This runs fine.

My problem / question is more about the counting and loop for generating multiple files.

Excelsheet offpipe, cell R11C3 contains the waterdepth for example 12meter.

- When you open the userform you fill in the WDstart (start waterdepth), WDend (end waterdepth) and intervals (the quantity of files i want).
- WDstart=10, WDend=30 and intervals 3.

Now he has to overwrite the original cellvalue 12 (cel R11C3) with 10 and export the file as text.
Now he has to overwrite the cellvalue 10 with 20 and export again.
overwrite 20 with 30 and export again.
counting stopped / WDend depth is reached.

Regards,

Erwin

I don't know that it is the best loop, but my first thought is to use a For...Next loop. something like:

Please Login or Register  to view this content.

I expect a Do While loop or a Do...Loop until loop would also be good alternatives.
The main question I see is how to handle cases when wdstart, wdend, and interval don't result in integer loopstep (such 10 to 31 interval 3).

MrShorty I modified it to

Please Login or Register  to view this content.

And it works perfect!
When i try for example startdepth 12, enddepth 60, steps 6 he creates files 12,24,36,48,60 as it should be.

Edit:/about your main question..
Can we extend the code with a ''rule'' that decimals are not allowed?

so if i fill in startdept 10 enddepth 3 with 3 intervals he will create:
3
6.5
10

Round up is fine by me or do you like a msgbox with ''please adjust intervals'' more?

Regards

Can we extend the code with a ''rule'' that decimals are not allowed?

Perhaps the question to precede this one is, do we need to deal with non-integer values for loopstep and i? I don't recall any examples in this thread of non-integer values for this parameter, so I wasn't sure if non-integer values would be allowed. If the spreadsheet works just fine for non-integer values, and the rest of the code for writing the text file works fine for a non-integer value (especially the filename building part), then maybe nothing needs to be done.

MrShorty,

You're right. The spreadsheet works fine for non-integer values (the export vba) but my simulation program isn't able to open them due to the double point in the name.
I add a note ''please use integer values'' to the userform waterdepth

I rebuild the userform ''cable length'' and changed the formula to:

Please Login or Register  to view this content.

This works great but my collegue asked for another thing. We want this loopfunction for several depths. Do i scare you already?
See the attachment for the new form. I named the boxes WD1, WD2, WD3 etc. (waterdepth1, waterdepth2)

I tried to solve it with:

Please Login or Register  to view this content.

so if i fill in boxes WD1 to WD6 he must replace the A&R depths with that value.
Then the existing loop must be done for WD1 lets say 5 intervals (5 files for this depth)
then the loop for WD2 (also 5 files) and so on and so on.

But the tricky thing is. Cell R11C3 needs to contain the waterdepth otherwise the naming will get an error.

Do you get the point?Newform.jpg

Mr. Shorty,

Yesterday i succeeded in creating the code to the requirements i had.

Be honest, see the code below...can you hold your laugh?

Please Login or Register  to view this content.

is there a way to write this ''shorter''?

I observe that this code block:

Please Login or Register  to view this content.

is repeated multiple times. The only difference is in WDj.value that is used in column 24 and cell 11,3. I can't tell what WDj is referring to. If WDj is structured as a block of cells or as an array, then it should be easy to rewrite it with this block of code inside of another loop.

Your right, i want that ''codeblock'' for multiple waterdepths (WDj).

In my sheet i extended the ''file naming'' with a letter for a wd.
so if he writes:
ActiveSheet.Cells(7, 24).Value = WD2.Value
ActiveSheet.Cells(11, 3).Value = WD2.Value

in the cell for filenaming i have (IF7,24=11,3, A,IF 8,24=11,3, B, etc,etc,etc,etc,etc,etc,etc,X)

When this is finished i'm the happiest man on earth