Exporting excel to text file #2

Hi A,

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




A C Wrote: 
> "Exceluser" Exceluser.1qzwtn@news.officefrustration.com wrote in
> message
> news:Exceluser.1qzwtn@news.officefrustration.com...-
> 
> 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
0
6/22/2005 8:47:30 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
441 Views

Similar Articles

[PageSpeed] 22

"Exceluser" <Exceluser.1r1rhn@news.officefrustration.com> wrote in message
news:Exceluser.1r1rhn@news.officefrustration.com...
>
> 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" Exceluser.1qzwtn@news.officefrustration.com wrote in
> > message
> > news:Exceluser.1qzwtn@news.officefrustration.com...-
> >
> > 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


begin 666 Selection_VBACode.txt
M4W5B($UA8W)O,2@I#0H-"B=/<&5N('1H92!F:6QE(&9O<B!O=71P=70-"B=&
M:6QE;F%M92 ](")C.EQ496UP7'-E;&5C=&EO;BYT>'0B#0I&:6QE;F%M92 ]
M(%)A;F=E*")&:6QE;F%M92(I+E9A;'5E#0IF:6QE;G5M8F5R(#T@1G)E949I
M;&4-"D]P96X@1FEL96YA;64@1F]R($]U='!U="!!<R C9FEL96YU;6)E<@T*
M#0I286YG92@B4V5L96-T:6]N(BDN4V5L96-T#0I286YG92@B4V5L96-T:6]N
M(BDN06-T:79A=&4-"@T*)TQO;W @;W9E<B!A;&P@=&AE(&-H;VEC97,-"F-O
M;$]F9G-E=" ](#$-"E=H:6QE("A!8W1I=F5#96QL+D]F9G-E="@P+"!C;VQ/
M9F9S970I+E9A;'5E(#P^("(B*0T*(" @("=3=&%R="!T:&4@;W5T<'5T('-T
M<FEN9PT*(" @(&]U='!U=%-T<B ]($%C=&EV94-E;&PN3V9F<V5T*# L(&-O
M;$]F9G-E="DN5F%L=64@)B B*"(@)V5G($-H;VEC93$H#0H@(" @)U=A;&L@
M9&]W;B!E86-H(')O=R!A;F0@:6YC;'5D92!I="!I;B!T:&4@<W1R:6YG(&EF
M('1H92!C96QL(&ES(&YO="!E;7!T>0T*(" @(')O=T]F9G-E=" ](#$-"B @
M("!7:&EL92 H06-T:79E0V5L;"Y/9F9S970H<F]W3V9F<V5T+" P*2Y686QU
M92 \/B B(BD-"B @(" @(" @268@*$%C=&EV94-E;&PN3V9F<V5T*')O=T]F
M9G-E="P@8V]L3V9F<V5T*2Y686QU92 \/B B(BD@5&AE;@T*(" @(" @(" @
M("=4:&ES(&ES('-E;&5C=&5D+"!A9&0@:70@=&\@=&AE('-T<FEN9PT*(" @
M(" @(" @(&]U='!U=%-T<B ](&]U='!U=%-T<B F($%C=&EV94-E;&PN3V9F
M<V5T*')O=T]F9G-E="P@,"DN5F%L=64@)B B+"(-"B @(" @(" @16YD($EF
M#0H@(" @(" @(')O=T]F9G-E=" ](')O=T]F9G-E=" K(#$-"B @("!796YD
M#0H@(" @)T5A8V@@<F]W(&-H96-K960L(&-L;W-E(&]F9B!T:&4@<W1R:6YG
M#0H@(" @268@*%)I9VAT*&]U='!U=%-T<BP@,2D@/2 B+"(I(%1H96X-"B @
M(" @(" @;W5T<'5T4W1R(#T@3&5F="AO=71P=713='(L($QE;BAO=71P=713
M='(I("T@,2D-"B @("!%;F0@268-"B @("!O=71P=713='(@/2!O=71P=713
M='(@)B B*2(-"B @(" G061D('1H92!O=71P=70@<W1R:6YG('1O('1H92!F
M:6QE#0H@(" @4')I;G0@(V9I;&5N=6UB97(L(&]U='!U=%-T<@T*(" @("=/
M4B!74DE412!54TE.1R!42$E3($-/1$4@248@64]5($1/3E0@5T%.5"!43R!0
M4DE.5"!/550@04X@14U05%D@4T54+"!E9R!#:&]I8V4Q*"D-"B @(" G268@
M*%)I9VAT*&]U='!U=%-T<BP@,BD@/#X@(B@I(BD@5&AE;@T*(" @("<@(" @
M)T%D9"!T:&4@;W5T<'5T('-T<FEN9R!T;R!T:&4@9FEL90T*(" @("<@(" @
M4')I;G0@(V9I;&5N=6UB97(L(&]U='!U=%-T<@T*(" @("=%;F0@268-"B @
M(" -"B @(" G5')Y('1H92!N97AT(&-O;'5M;@T*(" @(&-O;$]F9G-E=" ]
M(&-O;$]F9G-E=" K(#$-"E=E;F0-"@T*)T-L;W-E('1H92!F:6QE#0I#;&]S
:92 C9FEL96YU;6)E<@T*#0H-"D5N9"!3=6(`
`
end

0
no3952 (12)
6/22/2005 10:26:17 PM
Reply:

Similar Artilces:

spread a picture across 2 pages
Hello, The newsletter I produce (publisher 2002) is in A5 format. It prints 2 pages per A4 sheet, double-sided, which is folded in half. I've set up the page and print options OK. I want to include a map which is quite large, too big to fit sensibly on an A5 page. I would like to spread it across 2 adjacent pages, probably as a 'centrefold'. Can I do this within the publisher file using a single picture box, or must I split the map into 2 pictures and put one on each page? With thanks KK After managing to set up OE-QuoteFix on his new PC, Ed reads a message from KK <tre...

Excel 2003 Inserting current date
I wish to create a spreadsheet that whenever data is entered in a row/column, the actual date is entered automatically in a date column. I do not want the date to be overridden. Mark Right-click your sheet tab and "View Code". Copy/paste this event code in that sheet module. Enter anything in any cell in column A and B will get a static date. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Col B time will not change if data in Col A is edited On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row ...

Contacts export display X400 not SMTP
Hi all, I'm not sure if this behavior is by default or not, but when my users try to export their contacts to an Excel file, any internal (domain) contacts show the X400 address rather than the SMTP address. Is there a way to change this? I've read some about VB scripts with CDO or something, but hoping there is an alternate way. Thanks Mike -- what version Exchange? -- Susan Conkey [MVP] "Mike W." <wallmr_snowpam@gmailDOTC0M> wrote in message news:iQ72h.553254$Pi2.240974@fe08.news.easynews.com... > Hi all, > > I'm not sure if this behavio...

Export to XML exports single row only
What I did: On XML source panel of Excel 2007 added XML map, mapped to columns using ranges (i. e. A1:A1825), selected “Export” from “Developer” tab in the ribbon. Result: Output XML file contains only one record (first row from the worksheet) I’ve done this numerous times in Excel 2003 with no problem. What happens?? Please help! Thanks, Eugene ...

how to extract data from csv file? (part 3)
hi dave thanks so much for your advice and help... i really appreciate it... i have tried your method to change the separator as u said but in fact, mine was already set to the us standard... i run the command again but again the data is all in one column... i know i can record a macro to do the stuff that i need but because i'm working as an analyst and i have to extract data from any csv files as and when needed and they might not have the same structure and it would be faster and more efficient for me if i can run what i need from just one command... what i tried in my command is to t...

Access vs Excel
I wanted to get the opinion of both the excel and access group on this subject. We currently have an ERP but is lacking in some aspects. In order to get around the shortcomings, I have an user that is very proficient with excel. He has made spreadsheets that access the information out of our ERP and mimics some of it's functions. He is using excel to store information and has created interfaces for users to enter in data. My question is, should we continue using excel to do this. We have hundreds of excel files that get created with information everywhere. OR should we start using acc...

Export / Import Security
Ok, so I have setup my test environment again. Now I have to create security all over from scratch to define what will be used in production. Is there a way to export from the test environment and then import it into the newly upgraded installation? In both cases security has been/will be completely removed from the 8.0 install (restored on new server) before being upgraded to version 10. Dear, Security will be upgraded upon upgrading GP from GP 8.0 SP5 to GP 10.0. Converting the Security structure from GP 8.0 to 10.0 will not be an easy task. Regards, -- Mohammad R. Daoud MCP, MCBM...

Excel to Access
Does anyone know how to take an Excel spreadsheet and merge it into an existing Access database? Thank you. ...

Text disappears when format changed in MS Word (Office X)
I save Word documents in Mac Word 5.1 format while using Office X. Sometimes when I resave, the format changes to the generic Microsoft Office document format. After this happens and I open the document, the text of the document is missing entirely. I an using Panther 10.3.3. Any ideas? Thank you. James Costello ...

winmail.dat
When we send attachments to people they show up on their ends as winmail.dat files. We are using Outlook 2000 on the XP platform. I researched and can't seem to come up with a solution. We are not using RTF (rich text format) and that seems to be the only configuration associated with this problem that I can find. We are using plain text and it still happens when we forward an email w/ an attachment and reset it to send via plain text. Any help would be appreciated. Thank you. Use plain text or html format and disable Word as your e-mail editor. If it still occours, it could b...

lost Excel data!
I've just deleted some important data from an Excel sheet having moved it to another workbook. Unfortunately I closed the new workbook without saving and I can't find the data! I've not got a backup of the old workbook and the data is not on the clipboard. Is there any way of recovering it? thanks MrBen AFAIK, NO. We all gotta learn sometime (to make backups) -- Don Guillett SalesAid Software donaldb@281.com "MrBen" <anonymous@discussions.microsoft.com> wrote in message news:2305c01c45e8d$7842a610$a601280a@phx.gbl... > I've just deleted some importa...

Exporting Sent Email Addresses from Entourage
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello! I'm trying to export email addresses from an employee's Entourage, but would like to include email addresses in his "Sent Items" folder. Is there a way to export these email addresses aside from exporting the "MBOX" file, which has all the email content as well? Thanks in advance! On 3/12/08 8:49 AM, in article ee9381d.-1@webcrossing.caR9absDaxw, "Quinlisk@officeformac.com" <Quinlisk@officeformac.com> wrote: > I'm trying to export email addresses from an emplo...

how can I get Outlook (not Outlook Express) to be used to send files from my computer
How can I get Outlook (not Outlook Express) to be used to send files from my computer? I've just started using my new Compaq Presario 3000 laptop running XP Home. Each time that try to send a file by right clicking on it and selecting Mail Reciepient the mail is sent via Outlook Express and not Outlook. THis is a problem for me as I do niot use Outlook Express for anything but Newsgroup reading. The other problem is that I want a record of having sent the file in my Outlook Sent Mail folder. On my old computer the files went through Outlook and not Outlook Express I've checked th...

Excel XML
Hi all, Newbie here. I have an application that outputs Excel XML, auto-emailing to a distribution list. This ended up working wonderfully, users can just click on the attachment in the email and the file opens up in a great Excel spreadsheet for them. Prior, we would only export delimited text files, which had to be imported to Excel. Not hard, just extra steps. However, I have TWO users that are on Macs. Their version of Excel on the Mac cannot handle XML or read it. I've been Googling for days now and have only found years old postings about how Mac's Excel left this f...

Export item ID?
Hi there. Having resolved my previous EMS Data import issues, I have now moved on to more complicated things (importing supplier lists, aliases etc). However, these operations require the item ID for each item - something I don't have. Is there some way I can export these from the database so I know what item ID corresponds to each item. I have run a query in Administrator (select itemlookupcode, ID from item) which tells me this, but I haven't worked out how to export this out to a spreadsheet. Any help would be appreciated, cheers After running your query goto File menu and Export....

Fonts get size 1 and can't be changed after a procedure
Hi, I have been using code to loop for each chtobj in a worksheet to set font size right etc. Sometimes an error occur and the size become 1 pt. I can live with that once or so, but I can't change the FONT BACK neither with code nor from excel chart GUI. No code are running in background during this of cource, I try even to change the font from Excel GUI with macro switched off. It can only be solved with building a new chartobject... Have any of you got this error, any knowing why it occure and what I can do about it? I thougt it was because some of the charts had no values or s...

Exporting email in Outlook Express
I am unable to export all emails in Outlook Express. First it had an error message mentioning MAPI so i followed the instructions on Microsoft Knowledge Base to install Windows Messaging - now when i try to export the following error message is: MAPISP32 caused an invalid page fault in module KERNEL32.DLL at 0187:bff7b9a6. Does anyone know what this means? tania jackson <tania@realtime-bs.com> wrote: > I am unable to export all emails in Outlook Express. Ask in an Outlook Express newsgroup. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapid...

Excel 2002, now 2007
I was previously using Excel 2002 and decided to buy Home and Student non-commercial 2007. I am having problems converting my 2002 files to 2007 and also do not seem to be able to upload the files thru 2007. I'm thinking because it is non-commercial, Excel 2007 non-c does not want you to upload to a website. Okay, can I purchase an upgrade of just Excel 2007 so that I can convert files and upload to my website? If I could I would go back to using my 2002 version I tried loading it again but the computer says it does not find a MS product to upgrade... Thanks...Nancy ...

Problem with Excel 2007
Hi Everyone, It is my first time working on Excel 2007. I was working on a project which I started on Excel 2003, I opened another project for reference and when I returned to the first project all sheets disappeared. The only accessible buttons on the toolbar are the Macro buttons. All macros are still there and sheet names in the VBA are still there. Did I loose my work? Thanks to all albertmb;657269 Wrote: > Hi Everyone, It is my first time working on Excel 2007. I was working on a > project which I started on Excel 2003, I opened another project for referen...

How to create "frames" in Excel 2003?
Hi all, Is there a way to create frames in Excel like one can do for webpages? For example if I split my sheet vertically, I want a frame on the left side which has "links" that if I click them change the sheet I'm viewing on the right side. Thank you! Anyone? ...

Exporting to Powerpoint
I have a small table in Excel that I am trying to export to PowerPoint, for some inexplicable reason one column was always missing even though it was specified in the range which the Macro is supposed to export. My manager is the only was able to see the value in that column and no other can replicate his success. Do you know any reason for this? We are all looking at the exact same file, why could he see something that the rest of us cannot? Tao, Why don't you simply copy and paste the whole table into a Powerpoin slide? Joh -- Message posted from http://www.ExcelForum.com...

Getting this message when i startup excel 2003
Getting this message when i startup Excel 2003, C:\Program Files\Adobe\Acrobat 5.0\pdfmakerA.xla could not be found, i have Acrobat 6 on my computer, i have tried the repair and it still comes up, i have unistalled from add and remove, then reinstalled office 2003 but it still comes up when i start Excel Go to Tools-->Add-ins, and uncheck it. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Brian" <Brian@discussions.microsoft.com> wrote in message news:7792B9F9-2A5F-471B-A6B4-8151485F5D5F@microsoft.com... > Getting this message whe...

WMP Subtitles When Playing A File
When I Play an MPEG file is some of my other Media Players, sunch as BlazeDVD 5.1, subtitles are shown when I play the file. However, when I play the file in WMP 11.0, the subtitles are not shown. I know the subtitles are in the video stream because they show up in other players but why not WMP? Are you supposed to be able to turn the subtitles off and on? I have read on other forums that the subtitles are embedded in the video. But, how can that be? In one player they show up and another they don't. On Mon, 30 Nov 2009 11:09:01 -0800, Ray <Ray@discussions.microsoft.c...

MFC vs. WinForms #2
I need to design a program with a GUI using the Microsoft Visual Studio platform. I have substantial experience writing programs with Borland C++ Builder, but I have not used Visual Studio before. I would like to gather some opinions on whether I should learn MFC for the design, or whether I should make the leap to using C# and the WinForms approach (I have no experience with C#). Basically, I would like to find out the tradeoffs (i.e. learning curves, reusability, portability, etc.). Also, I am unclear as to why WinForms are unavailable in Visual C++ .NET, since my understanding is that W...

Export Image Package
I have an access application that has stored .JPG images within the database as a "Package". How can I export the image back to a normal .jpg file so that it can be read by a normal (non ole server) imaging application? ...