Hi A,

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




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