Exporting files to text files

**excelvraag** · 08-02-2012, 07:55 AM

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

**MrShorty** · 08-02-2012, 09:24 AM

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.

**excelvraag** · 08-02-2012, 10:00 AM

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

**MrShorty** · 08-02-2012, 10:34 AM

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.

**excelvraag** · 08-02-2012, 10:41 AM

Ok I will post it here, this is my code in the userform:

----------------------------------------

Private Sub cmbClose_Click()
   Unload Me
End Sub

Private Sub cmbGenerate_Click()

End Sub

Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=True

End Sub

----------------------------------------

The button "close" working haha. But I agree that's a basic one.

Furthermore the module "DoTheExport"

-----------------------------

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Value
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

-----------------------------

Regards,

Erwin

**MrShorty** · 08-02-2012, 12:22 PM

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.

**Cutter** · 08-02-2012, 05:45 PM

@ 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.

**excelvraag** · 08-03-2012, 06:11 AM

@ 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

**MrShorty** · 08-03-2012, 09:45 AM

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?

**excelvraag** · 08-14-2012, 04:34 AM

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

**MrShorty** · 08-14-2012, 09:22 AM

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.

**excelvraag** · 08-15-2012, 08:31 AM

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

**MrShorty** · 08-15-2012, 09:23 AM

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"))

**excelvraag** · 08-16-2012, 09:29 AM

Mr Shorty.

Thanks the first part is done: See the code below, he even saves the file as a .DAT extention which i need.

Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    
    FileName = ThisWorkbook.Path & Application.PathSeparator & Worksheets("OFFPIPE").Cells(3, 21) & ".DAT" 'Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = " " 'Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=False

End Sub

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Value
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

but about the 2nd and 3th question, see both textfile attachments. The first one is what i export. The other one is how it should be.
I know that all the ""sign are empty cells but i can't figure out to use the ''selectrange'' code you tried to explain.

Another thing, when i was trying to understand the code i just trialed/errored to learn. When i fill in:

Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(3).Row
        StartCol = .Cells([B]6[B]).Column
        EndRow = .Cells([B]104[B]).Row
        EndCol = .Cells([B]6[B]).Column
    End With

I expect than also range R3C6 to R104C6 but only row 3 is exported :S?

And I also created a link between the button ''generate'' to do the export.

So step by step i'm getting there

Thanks again.

**MrShorty** · 08-16-2012, 10:13 AM

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

    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=False

to

    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=TRUE, AppendData:=False

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?

startrow=3
startcol=6
endrow=104
endcol=6

**excelvraag** · 08-16-2012, 10:39 AM

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!

**Cutter** · 08-16-2012, 08:24 PM

@ 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.

**excelvraag** · 08-17-2012, 02:49 AM

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.

**excelvraag** · 08-17-2012, 10:19 AM

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

**MrShorty** · 08-17-2012, 12:22 PM

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.

**excelvraag** · 08-20-2012, 04:53 AM

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

**MrShorty** · 08-20-2012, 10:09 AM

I don't understand all of the details, but the basic elements of the filename appear to be:
1) A root string: "bwda"
2) A suffix that is a 3 digit integer calculated from user inputs.
3) the file extension ".dat"
So building the filename should be as simple as concatenating those three elements together. If you build the filename in a spreadsheet cell, you can use the concatenate operator "&" or the =CONCATENATE() function. Something like =CONCATENATE("bwda",text(a1,"000"),".dat"). If you build the filename in VBA, you will use the concatenate operator "&": myfilename="bwda" & activesheet.cells(1,1).text & ".dat" If it has trouble with converting the number to text, you may have to force the conversion from number to string, and you may have to round the number to an integer (possible floating point roundoff errors). You may also need to use the Format function (see the VBA help file for Format function) to force the number into a 3 digit number format.

**excelvraag** · 08-20-2012, 10:22 AM

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

**MrShorty** · 08-20-2012, 11:01 AM

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

'**code to get information from user
loopstep=(wdend-wdstart)/(intervals-1)
'**code to resolve any problems with wdend, wdstart, loopinterval
For i=wdstart to wdend step loopstep
activesheet.cells(11,3).value=i
'** code to build filename and write text file
next i

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).

**excelvraag** · 08-20-2012, 11:37 AM

MrShorty I modified it to

Private Sub CMB_Generate1_Click()
'**code to get information from user
loopstep = (WDend - WDstart) / (WDsteps - 1)
'**code to resolve any problems with wdend, wdstart, loopinterval
For i = WDstart To WDend Step loopstep
ActiveSheet.Cells(11, 3).Value = i
'** code to build filename and write text file
DoTheExport
Next i
   DoTheExport
   
   Unload Me

End Sub

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

**MrShorty** · 08-20-2012, 12:09 PM

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.

**excelvraag** · 08-21-2012, 07:53 AM

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:

'**code to get information from user
loopstep = CLstep
'**code to resolve any problems with wdend, wdstart, loopinterval
For i = CLstart To CLend Step loopstep
ActiveSheet.Cells(28, 3).Value = i
'** code to build filename and write text file
DoTheExport
Next i
   DoTheExport

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:

'**code to resolve any problems with wdepth
ActiveSheet.Cells(11, 3).Value = X
For X = WD1 To WD2

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

**excelvraag** · 08-22-2012, 07:24 AM

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?

Private Sub CLend_Change()
If Not CLstart = "" And Not CLend = "" And Not CLstep = "" Then
    CMB_Generate2.Enabled = True
Else
    CMB_Generate2.Enabled = False
    End If
End Sub

Private Sub CLstart_Change()
If Not CLstart = "" And Not CLend = "" And Not CLstep = "" Then
    CMB_Generate2.Enabled = True
Else
    CMB_Generate2.Enabled = False
    End If
End Sub

Private Sub CLstep_Change()
If Not CLstart = "" And Not CLend = "" And Not CLstep = "" Then
    CMB_Generate2.Enabled = True
Else
    CMB_Generate2.Enabled = False
    End If
End Sub

Private Sub CMB_Close_Click()

    Unload Me
End Sub

Private Sub CMB_Generate2_Click()

'**code to get message when WD1 isn't filled in
If Not CLstart = "" And Not CLend = "" And Not CLstep = "" And WD1 = "" Then
   MsgBox "fill in at least 1 Waterdepth"
   Exit Sub
   Else
End If
    
    '**code to get A&R waterdepths
    ActiveSheet.Cells(6, 24).Value = WD1.Value
    ActiveSheet.Cells(11, 3).Value = WD1.Value
    
        '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
        
    If Not WD2.Value = "" Then
    ActiveSheet.Cells(7, 24).Value = WD2.Value
    ActiveSheet.Cells(11, 3).Value = WD2.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
     
    If Not WD3.Value = "" Then
    ActiveSheet.Cells(8, 24).Value = WD3.Value
    ActiveSheet.Cells(11, 3).Value = WD3.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
    
    If Not WD4.Value = "" Then
    ActiveSheet.Cells(9, 24).Value = WD4.Value
    ActiveSheet.Cells(11, 3).Value = WD4.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
    
    If Not WD5.Value = "" Then
    ActiveSheet.Cells(10, 24).Value = WD5.Value
    ActiveSheet.Cells(11, 3).Value = WD5.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
    
    If Not WD6.Value = "" Then
    ActiveSheet.Cells(11, 24).Value = WD6.Value
    ActiveSheet.Cells(11, 3).Value = WD6.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
    
    If Not WD7.Value = "" Then
    ActiveSheet.Cells(12, 24).Value = WD7.Value
    ActiveSheet.Cells(11, 3).Value = WD7.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
    
    If Not WD8.Value = "" Then
    ActiveSheet.Cells(13, 24).Value = WD8.Value
    ActiveSheet.Cells(11, 3).Value = WD8.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
        
    If Not WD9.Value = "" Then
    ActiveSheet.Cells(14, 24).Value = WD9.Value
    ActiveSheet.Cells(11, 3).Value = WD9.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
        
    If Not WD10.Value = "" Then
    ActiveSheet.Cells(15, 24).Value = WD10.Value
    ActiveSheet.Cells(11, 3).Value = WD10.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If
End Sub

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

**MrShorty** · 08-22-2012, 09:58 AM

I observe that this code block:

 If Not WD2.Value = "" Then
    ActiveSheet.Cells(7, 24).Value = WD2.Value
    ActiveSheet.Cells(11, 3).Value = WD2.Value
    
         '**code to get information from user
        loopstep = CLstep
        '**code to resolve any problems with wdend, wdstart, loopinterval
        For i = CLstart To CLend Step loopstep
        ActiveSheet.Cells(28, 3).Value = i
        '** code to build filename and write text file
        DoTheExport
        Next i
        DoTheExport
        Unload Me
    Else
    Unload Me
    End If

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.

**excelvraag** · 08-22-2012, 10:33 AM

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

Exporting files to text files

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Re: Exporting files to text files

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions