Hi,
Thanks so much for your help. I am just learning VSB. I guess that's
why I am so "slow". What is "filenumber"? It's not a built in, so what
do I change it to? The error it's giving is "Runtime error "1004"
Application defined or objective-defined error"

I am using Microsoft Excel 2003, with Visual basic 6.3
Email:

thanks




A C Wrote:
"Exceluser" wrote in
message
.. .-

Thank you so much!It is not working as yet, I hope I get it to work
eventually. I keep giving me a 1004 error.-

Hi

Can you be more specific about the error you receive, 1004 errors can
be
caused by multiple things. Post the error text/message you receive in
the
error dialog as well as the number.

I tried to send you the test excel workbook I created which works for
the
very small dummy dataset I input, but your email is invalid. Sent to:

If you want this file post your email address (or post a corrupted
version
and clues on how to uncorrupt it) and I will send it again.

Regards
A
-


A C Wrote:-
"Exceluser" wrote in
message
.. .-

Hi A,

Thank you so much for your help, the While statement and empty is
giving me an error.
-

Hi

Whats happening here is an issue here with my code rolling into
multiple
lines when I pasted it into the posting.

I have attached a txt document where I copied and pasted the code
straight
from the VBA module. This should preserve where new lines are
appearing.
Try copying from this and see if it works. If not I can send you
the
.xls
itself if you supply an email address.

Regards
A
-



A C Wrote:-
"Exceluser" wrote in
message
.. .-

selection Choice1 choice2
A1 x x
A2 x
A3 x
A4 x
A5 x x
A6
A7 x
A8 x
A9 x x

The table above is done in excel. How can I output the data in a
text
file as
Choice1(A1,A3,A4 etc..)
Choice2(A1, A2, A5 etc.)
I basically want to list all the selection with X's with the
corresponding
choices. The selection may very large as compared to what is
demonstrated
here. is there anyway I can get this done using VSB?

I am using Windows XP, Excel 2003.


--
Exceluser-

Below is a macro which will do what you want.
Assumptions:
* There are NO BLANK ROWS and NO BLANK COLUMNS in your table of
data
* Every choice column has a header, eg Choice1
* An empty cell indicates no selection, anything else in the cell
indicates
a selection (you used "x" in your example).
* You have set up 2 ranges:
Filename - this holds the name of the file you want to write to,
including
the path. (or you could hardcode it into the VB code, i have
supplied
an
example in the code itself)
Selection - the "Selection" fieldname cell

The code also has no error checking, so for example if the filename
is
invalid it will crash. You might want to add error checking later.
There is some sample code in there if you dont want to output an
empty
choice, ie there were zero selections in that column of data.

Hope this helps

Regards
A

Sub Macro1()

'Open the file for output
'Filename = "c:\Temp\selection.txt"
Filename = Range("Filename").Value
filenumber = FreeFile
Open Filename For Output As #filenumber

Range("Selection").Select
Range("Selection").Activate

'Loop over all the choices
colOffset = 1
While (ActiveCell.Offset(0, colOffset).Value "")
'Start the output string
outputStr = ActiveCell.Offset(0, colOffset).Value & "(" 'eg Choice1(
'Walk down each row and include it in the string if the cell is not
empty
rowOffset = 1
While (ActiveCell.Offset(rowOffset, 0).Value "")
If (ActiveCell.Offset(rowOffset, colOffset).Value "") Then
'This is selected, add it to the string
outputStr = outputStr & ActiveCell.Offset(rowOffset, 0).Value &
","
End If
rowOffset = rowOffset + 1
Wend
'Each row checked, close off the string
If (Right(outputStr, 1) = ",") Then
outputStr = Left(outputStr, Len(outputStr) - 1)
End If
outputStr = outputStr & ")"
'Add the output string to the file
Print #filenumber, outputStr
'OR WRITE USING THIS CODE IF YOU DONT WANT TO PRINT OUT AN EMPTY
SET,
eg
Choice1()
'If (Right(outputStr, 2) "()") Then
' 'Add the output string to the file
' Print #filenumber, outputStr
'End If

'Try the next column
colOffset = colOffset + 1
Wend

'Close the file
Close #filenumber


End Sub-


--
Exceluser--


--
Exceluser-


--
Exceluser