Hi

I am currently creating a form which allows users to create a series of
text files based on the data in an excel spreadsheet. I have all the
code for the the creation of the text files but I would like to give
the user more freedom with which files they choose to create. At
present there are 448 records and the code will output all of the
records -an example of this is below:

Private Sub CreateText()

For Each Rng In Range("A2:A450")
If Rng.Value < "" Then
FNum = FreeFile
Open Rng.Value & ".txt" For Output Access Write As #FNum
Print #FNum, Rng(1, 2).Value
Print #FNum, Rng(1, 3).Value
Print #FNum, Rng(1, 4).Value
Print #FNum, Rng(1, 5).Value
Print #FNum, Rng(1, 6).Value
Print #FNum, Rng(1, 7).Value
Close #FNum
End If
Next Rng
End Sub

I would like the user to be able to use a selection from the name box
instead currently all of the records. I thought it would be easiest if
the user creates a name for their selection of cells so instead of the
above code it would read something like this:

Private Sub CreateText()

For Each Rng In Range("Name")
If Rng.Value < "" Then
FNum = FreeFile
Open Rng.Value & ".txt" For Output Access Write As #FNum
Print #FNum, Rng(1, 2).Value
Print #FNum, Rng(1, 3).Value
Print #FNum, Rng(1, 4).Value
Print #FNum, Rng(1, 5).Value
Print #FNum, Rng(1, 6).Value
Print #FNum, Rng(1, 7).Value
Close #FNum
End If
Next Rng
End Sub

This is easy enough to do but I would really like it if I could
populate a listbox on the form with names from the name box so a user
could make his selection, create a name and then be able to select this
again in the list box of the form before outputting the relevant text
files.

I hope this makes enough sense. If anybody out there has any feedback
on this it would be hugely appreciated.

Cheers,
Mike

How about an alternative idea that would make me happier as a user?

Give me a column that I can put an X in (or leave blank). Then I can use all of
excel's features to verify that I have what I want (I'm thinking
data|Filter|autofilter--or even Data|Sort).

Then you can just process the rows that have an X in that column (or non-empty
or whatever).

And as a final step, you could ask me if those X's should be cleared or left for
the next time.



Greshter wrote:

Hi

I am currently creating a form which allows users to create a series of
text files based on the data in an excel spreadsheet. I have all the
code for the the creation of the text files but I would like to give
the user more freedom with which files they choose to create. At
present there are 448 records and the code will output all of the
records -an example of this is below:

Private Sub CreateText()

For Each Rng In Range("A2:A450")
If Rng.Value < "" Then
FNum = FreeFile
Open Rng.Value & ".txt" For Output Access Write As #FNum
Print #FNum, Rng(1, 2).Value
Print #FNum, Rng(1, 3).Value
Print #FNum, Rng(1, 4).Value
Print #FNum, Rng(1, 5).Value
Print #FNum, Rng(1, 6).Value
Print #FNum, Rng(1, 7).Value
Close #FNum
End If
Next Rng
End Sub

I would like the user to be able to use a selection from the name box
instead currently all of the records. I thought it would be easiest if
the user creates a name for their selection of cells so instead of the
above code it would read something like this:

Private Sub CreateText()

For Each Rng In Range("Name")
If Rng.Value < "" Then
FNum = FreeFile
Open Rng.Value & ".txt" For Output Access Write As #FNum
Print #FNum, Rng(1, 2).Value
Print #FNum, Rng(1, 3).Value
Print #FNum, Rng(1, 4).Value
Print #FNum, Rng(1, 5).Value
Print #FNum, Rng(1, 6).Value
Print #FNum, Rng(1, 7).Value
Close #FNum
End If
Next Rng
End Sub

This is easy enough to do but I would really like it if I could
populate a listbox on the form with names from the name box so a user
could make his selection, create a name and then be able to select this
again in the list box of the form before outputting the relevant text
files.

I hope this makes enough sense. If anybody out there has any feedback
on this it would be hugely appreciated.

Cheers,
Mike

--

Dave Peterson