Hi A,

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




A C Wrote:
> "Exceluser" [email protected] wrote in
> message
> news:[email protected]
>
> 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