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.
A C Wrote:
> "Exceluser" Exceluser.firstname.lastname@example.org wrote in
> Hi A,
> Thank you so much for your help, the While statement and empty is
> giving me an error.
> Whats happening here is an issue here with my code rolling into
> lines when I pasted it into the posting.
> I have attached a txt document where I copied and pasted the code
> from the VBA module. This should preserve where new lines are
> Try copying from this and see if it works. If not I can send you the
> itself if you supply an email address.
> A C Wrote:-
> "Exceluser" Exceluser.email@example.com wrote in
> selection Choice1 choice2
> A1 x x
> A2 x
> A3 x
> A4 x
> A5 x x
> A7 x
> A8 x
> A9 x x
> The table above is done in excel. How can I output the data in a
> 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
> choices. The selection may very large as compared to what is
> here. is there anyway I can get this done using VSB?
> I am using Windows XP, Excel 2003.
> Below is a macro which will do what you want.
> * There are NO BLANK ROWS and NO BLANK COLUMNS in your table of
> * Every choice column has a header, eg Choice1
> * An empty cell indicates no selection, anything else in the cell
> 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,
> the path. (or you could hardcode it into the VB code, i have
> example in the code itself)
> Selection - the "Selection" fieldname cell
> The code also has no error checking, so for example if the filename
> 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
> choice, ie there were zero selections in that column of data.
> Hope this helps
> Sub Macro1()
> 'Open the file for output
> 'Filename = "c:Tempselection.txt"
> Filename = Range("Filename").Value
> filenumber = FreeFile
> Open Filename For Output As #filenumber
> '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
> 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
> '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
> 'If (Right(outputStr, 2) "()") Then
> ' 'Add the output string to the file
> ' Print #filenumber, outputStr
> 'End If
> 'Try the next column
> colOffset = colOffset + 1
> 'Close the file
> Close #filenumber
> End Sub-