ExcelBanter - Exporting excel to text file

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

filenumber is just a new local variable which is going to hold the system
fiile number than FreeFile returns. You could just as legitimately named it
myFilenumber, or even myWheelbarrow if you felt so inclined.

One of the things you should check is your references. Open visual basic
for your workbook, then click Tools--References.
You should have something like the following ticked (ticked references will
be at the top of the list), although the numbers may be slightly different:
-Visual Basic For Applications
-Microsoft Excel 10.0 object library (possibly a diff number)
-OLE Automation
-Microsoft Office 10.0 Object library ("" "")
If not, find them in the list and tick them and try and run it. Not all of
the above are actually required, but as you may well extend the
functionality of your program at a later date it's a good base set to start
with (ie you could get away with less but you dont want to be dealing with
not having the proper references if you extend your code at a later date so
the above is a good set to have).

I have sent you an email with my test book in it so you can see a version
which works. If it doesnt work then this may indicate an issue with your
system. I have made some slight tweaks to it so that it explicitly lists
the object/variable types. You will see what I mean when you compare the
new code to the original code posted.

Regards
A

"Exceluser" wrote in message
.. .

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