Export a range to a text file

Hello need some advise on how to procede

I need to be able to create a text file containg some text as well as
data that is within a named range in excel and  then some more text.  I
can handle printing to the text files using cell values etc but am
unsure of the best way to print the ranges data.  Is there a way or
procedure to just print the range as is in csv format?  As well my range
will contain about 6 columns, each containg a number field (formatting
of decimal places is important, some have 2 dec some 3 etc) Also the
range has a max of 50 rows however will always contain lower rows of
data then that, I dont want blanks printed to the file if there is no
data.  I dont know whats the best way to handle this, will each row have
to be printed individually to the file? or is there a way to use the
range name to do it in one step?

If I do have to print each row one by one, can someone help me with some
example code (or where to get it) on the best way to find out the number
of rows of data there is then print each row of data to a text file in
csv?

field1,f2.f3,f4,f5,f6

tia

0
eskyle (12)
11/28/2003 6:10:38 PM
excel 39879 articles. 2 followers. Follow

3 Replies
470 Views

Similar Articles

[PageSpeed] 33

Scott,

Open "Test.txt" For Output As #1
Print #1, "Hello";            ' this line writes one field
Print #1, Range("A1").Text;   ' this line appends a field
Print #1, Range("A2").Text    ' this line ends the record
Close #1

Note the semicolons at the end -- they cause the record to not be ended.
Without it, it writes CRLF after the field is written, ending the record.

Range("A1").Text writes the cell as formatted in the worksheet.  Range can
be replaced with any object that returns a range (Cells(Row, Column),
Selection, Offset(R, C), etc).

You'll need to loop through your columns and rows and do the Print
statements.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Scott" <eskyle@telusplanet.net> wrote in message
news:3FC78F9D.6113801@telusplanet.net...
> Hello need some advise on how to procede
>
> I need to be able to create a text file containg some text as well as
> data that is within a named range in excel and  then some more text.  I
> can handle printing to the text files using cell values etc but am
> unsure of the best way to print the ranges data.  Is there a way or
> procedure to just print the range as is in csv format?  As well my range
> will contain about 6 columns, each containg a number field (formatting
> of decimal places is important, some have 2 dec some 3 etc) Also the
> range has a max of 50 rows however will always contain lower rows of
> data then that, I dont want blanks printed to the file if there is no
> data.  I dont know whats the best way to handle this, will each row have
> to be printed individually to the file? or is there a way to use the
> range name to do it in one step?
>
> If I do have to print each row one by one, can someone help me with some
> example code (or where to get it) on the best way to find out the number
> of rows of data there is then print each row of data to a text file in
> csv?
>
> field1,f2.f3,f4,f5,f6
>
> tia
>


0
nowhere8060 (363)
11/28/2003 8:20:50 PM
Thank-you for the 411

So I assume then that there is just no quick way of exporting all the fields
at once by naming all the fields as a "named range" then just outputting that?

Earl Kiosterud wrote:

> Scott,
>
> Open "Test.txt" For Output As #1
> Print #1, "Hello";            ' this line writes one field
> Print #1, Range("A1").Text;   ' this line appends a field
> Print #1, Range("A2").Text    ' this line ends the record
> Close #1
>
> Note the semicolons at the end -- they cause the record to not be ended.
> Without it, it writes CRLF after the field is written, ending the record.
>
> Range("A1").Text writes the cell as formatted in the worksheet.  Range can
> be replaced with any object that returns a range (Cells(Row, Column),
> Selection, Offset(R, C), etc).
>
> You'll need to loop through your columns and rows and do the Print
> statements.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Scott" <eskyle@telusplanet.net> wrote in message
> news:3FC78F9D.6113801@telusplanet.net...
> > Hello need some advise on how to procede
> >
> > I need to be able to create a text file containg some text as well as
> > data that is within a named range in excel and  then some more text.  I
> > can handle printing to the text files using cell values etc but am
> > unsure of the best way to print the ranges data.  Is there a way or
> > procedure to just print the range as is in csv format?  As well my range
> > will contain about 6 columns, each containg a number field (formatting
> > of decimal places is important, some have 2 dec some 3 etc) Also the
> > range has a max of 50 rows however will always contain lower rows of
> > data then that, I dont want blanks printed to the file if there is no
> > data.  I dont know whats the best way to handle this, will each row have
> > to be printed individually to the file? or is there a way to use the
> > range name to do it in one step?
> >
> > If I do have to print each row one by one, can someone help me with some
> > example code (or where to get it) on the best way to find out the number
> > of rows of data there is then print each row of data to a text file in
> > csv?
> >
> > field1,f2.f3,f4,f5,f6
> >
> > tia
> >

0
eskyle (12)
11/28/2003 10:13:51 PM
Scott,

You may be able to use the Text Write Program at www.tushar-mehta.com.  It's
a workbook with a program that has the option to write only a selection to a
text file.  Other options.  It's not set up for automation, but it's easy
enough to use code to stuff your options onto its Setup sheet, then run it.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Scott" <eskyle@telusplanet.net> wrote in message
news:3FC7C89E.FE619C79@telusplanet.net...
> Thank-you for the 411
>
> So I assume then that there is just no quick way of exporting all the
fields
> at once by naming all the fields as a "named range" then just outputting
that?
>
> Earl Kiosterud wrote:
>
> > Scott,
> >
> > Open "Test.txt" For Output As #1
> > Print #1, "Hello";            ' this line writes one field
> > Print #1, Range("A1").Text;   ' this line appends a field
> > Print #1, Range("A2").Text    ' this line ends the record
> > Close #1
> >
> > Note the semicolons at the end -- they cause the record to not be ended.
> > Without it, it writes CRLF after the field is written, ending the
record.
> >
> > Range("A1").Text writes the cell as formatted in the worksheet.  Range
can
> > be replaced with any object that returns a range (Cells(Row, Column),
> > Selection, Offset(R, C), etc).
> >
> > You'll need to loop through your columns and rows and do the Print
> > statements.
> >
> > --
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "Scott" <eskyle@telusplanet.net> wrote in message
> > news:3FC78F9D.6113801@telusplanet.net...
> > > Hello need some advise on how to procede
> > >
> > > I need to be able to create a text file containg some text as well as
> > > data that is within a named range in excel and  then some more text.
I
> > > can handle printing to the text files using cell values etc but am
> > > unsure of the best way to print the ranges data.  Is there a way or
> > > procedure to just print the range as is in csv format?  As well my
range
> > > will contain about 6 columns, each containg a number field (formatting
> > > of decimal places is important, some have 2 dec some 3 etc) Also the
> > > range has a max of 50 rows however will always contain lower rows of
> > > data then that, I dont want blanks printed to the file if there is no
> > > data.  I dont know whats the best way to handle this, will each row
have
> > > to be printed individually to the file? or is there a way to use the
> > > range name to do it in one step?
> > >
> > > If I do have to print each row one by one, can someone help me with
some
> > > example code (or where to get it) on the best way to find out the
number
> > > of rows of data there is then print each row of data to a text file in
> > > csv?
> > >
> > > field1,f2.f3,f4,f5,f6
> > >
> > > tia
> > >
>


0
nowhere8060 (363)
11/29/2003 4:48:42 PM
Reply:

Similar Artilces:

excel export available
I have a query that I wish to export the results to Excel. However, the Excel button on the Export Data tab is grayed out? It used to work. Any ideas of what makes the Excel Export unavailable? Thanks, Mike ...

Text Box dynamic Color Fill
Hi, I have a textbox in excel that is linked to a cell. Depending if the cell value is positive or negative, I would like to change the textbox background color (fill). Any advice? Tks a lot! - Castor Hi Castor! You can use a macro, like the sample below to change the background color in the textbox. You have to update the name of the textbox in the macro, if it's not "Text Box 1", and the cell where you want to check for a positive/negative values. Sub ChangeTextboxFillColor() TheActiveCell = ActiveCell.Address If ActiveSheet.Range("A1") >= 0 Then Acti...

If I accidently replace a saved file can I retrieve the old file?
No. PLEASE write your question in the body of the post and NOT just the subject. Have a look here: http://dts-l.org/goodpost.htm -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk "kamabiv" <kamabiv@discussions.microsoft.com> wrote in message news:2A547F5D-AA2C-44D7-AEC7-8961A81AC4E5@microsoft.com... > ...

Best Practises setup with AD or File Server
Hi, We have a scenario where we are retiring out dedicated backup server. Currently we are using Symantec Backup Exec 11d. However, instead of replacing the dedicated server, their is a suggestion that I use existing servers for Backup Exec Are their known issues installing backup exec on a DC which also holds FSMO roles (PDC etc)? The other option is to install backup exec (and tape drive) on a file server which hosts the H and S drives. what do you think? I would use the file server. Presumably it is being accessed during the day when no backups are running. The b...

Word text copied into email loses formatting alignment
When copying a Word document (containing indented and numbered paragraphs and bullet points into an email, the left alignment of the document loses its justification. How can this be fixed? -- HK If the formatting is important, send the document as an attachment. You have no control over how the recipient sees the email. If you are concerned that the recipient might not have Word, use one of the free pdf converters to convert the document into a .pdf file. -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my s...

How to Filter cells and save the file with certain criteria?
Hello. I have a Excel file that contains a large list of Tracking Numbers. The tracking numbers are from two sets of order types- Internet orders and Mail Orders. Orders that are from the Internet are matched up with a order number such as "5678". Orders from the mail-order side are designated by initials "MO". So in Excel it'll look something like this: trackingnumber orderid 123365656666 5467 152155896345 5468 123365634567 MO 152134567789 5469 152151234563 MO Is there a way to eliminate the Mail-Order rows and save the Excel file to ...

file send to as attachment outlook 2007
When using in word or excel command "send a copy of the document in an e-mail message as an attachment", outlook creats a new e-mail message with the document attached as it should be done. Then I add text in message body, and send the mail. It arrives only with the attachment. The text in message body disappears. This happens only if I work in outlook chache mode. My os is winXp Pro, and my server is SBS 2003. It used to work correct, until somthing happened. Any idea to solve? Thank you Zipi ...

Transparent text on picture in Word
I have a picture I have placed into a word document. I now want to add text over the picture that will appear transparent. Essentially the picture is a scenery pciture and I want to add a poem that appears over the picture. How can I do this? Although you can place your text in a text box with No Line and No Fill, it would make more sense to use the document body for your text and set the wrapping on the picture to Behind Text. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Tony" <Tony@discussions.mic...

accidently deleted file
Somehow I deleted the file MSMONEY.EXE. It seems I did prior to a restore point on my system. Is there anyway to get it back? Thanks....Inker Reinstall Money from the CD. It won't affect your data - that is kept on the *.mny file. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "Inker" <anonymous@discussions.microsoft.com> wrote in message news:072c01c3d799$f6d99a90$a501280a@phx.gbl... > Somehow I deleted the file MSMONEY.EXE. It seems I di...

Intercept file download in IWebBrowser2
I have a MFC application host a webbrowser(IWebBrowser2).Basiclly ,I hope when user click a file download url(href="test.zip"),I can intercept the download message and process it by my own download component instead of IE itself. Is it possible? Any article will be help. Adv tks. http://msdn.microsoft.com/library/default.asp?url=/workshop/browser/ext/overview/downloadmgr.asp "L.J.S" <bconline2002@hotmail.com> д����Ϣ����:Oc7j%23OcAFHA.612@TK2MSFTNGP09.phx.gbl... > I have a MFC application host a webbrowser(IWebBrowser2).Basiclly ,I > hope ...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

How do I export email addresses from excel to outlook?
I am trying to do a mail merge using email via outlook. I have 200+ addresses and I'd like to know how to import the addresses into the contacts section of outlook to do the merge from there. I've tried the help part of out look but it comes up saying that the excel file has no named ranges and that I should use excel to name the range of data to be imported. Any help much appreciated. Thanks Mark In outlook select file/import export/ import from another program or file/ and then follow the instructions from the wizard. Why do you want to do the mailmerge from Outlook? You can...

Starting with a new pst file
Hi, I would like to start with a blank pst file. I want to start again with no contacts, no emails, no appointments. How should I proceed to make this? Thanks for your help, Olivier Olivier Verdin <olivier.verdin@playcorporate.com> wrote: > I would like to start with a blank pst file. I want to start again > with no contacts, no emails, no appointments. Assuming Outlook 2002/2003, since you didn't think it important to mention your Outlook version, click File>New>Outlook Data File, Browse to where you want the new PST (or accept the default), give it a name ...

Question on LABEL Range
In a LABEL Range(say that it has 12 elements row-wise), how do one refer to the 4th elements? I have this ideas that in a worksheet, I have 12 rows single column LABELED range called MONTHS, where 1st elements is JAN(that is C6), and so on, and last element would be DEC(C17). How do I get the 4th element which will be APR, and put that value in the different cell using the LABEL reference instead of the absolute cell location(C9)? Thanks I am not sure I understand your question? You refer to the labels with their names, so if you want to sum the April row you use =SUM(Apr) Otherwis...

Exporting/ Importing
I tried importing a distribution list that another company sent me. After it finished importing, I received an error message: 855 error descriptions were recorded in the systems application event log. And to top that off, there was nothing in the Recipient Container that I imported the list to. What am I doing wrong? "phouamkha" <anonymous@discussions.microsoft.com> wrote: >I tried importing a distribution list that another company >sent me. After it finished importing, I received an error >message: 855 error descriptions were recorded in the >systems ...

Hide toolbars when file opens
Hi all, I have created a file for people to use to get quotes. When the file opens, I do not want them to have access to any toolbars. How can I hide these when the file is opened, but only for this file - not any others that the user may subsequently open. All that they may need to do, is print the page out after getting a quote. Many thanks. Rob You can use this in the Thisworkbook module Rob Private Sub Workbook_Activate() Dim bar As CommandBar For Each bar In Application.CommandBars bar.Enabled = False Next End Sub Private Sub Workbook_Deactivate() Dim bar As C...

how can I get a publisher file to acrobat reader
I have a Regestration Form created in publisher 2003. When a confrence addtendee signs on to our web site they need to be able to click on a file (acrobat reader) Hopfuly, and download it and print it so they can fill out the form and mail it off. (I say acobat reader because that seems to be the most common way it's done. I dont see an export in publisher or a inport to acrobat. how can i make this happen You will need a third party program to create the pdf file. Adobe Acrobat but it's spendy or a freebee i.e., Primopdf or another commercial program of modest cost, i.e., PDF-XC...

put files together
Hi all, I have in a directory "summary" some 800 files with a number for a name, like 8, 23, 44, 124, 2045 up to somewhere in the 2500's. There is no list of all those file names. Each file is a workbook with only one sheet called "ABCD". I know that the last cell used will be a cell in column H. I also know that the content of that cell will be ==== (4 to 7 times =). Column H will contain text, blanks (empty cells), numbers and other characters (like f.i. =). The number of rows used will be at least 6 and could be as much as 200 or so (25 is typical). I need ...

Outlook 2003 PST file issues
I created a backup.pst form Outlook 2003. I also copied the outlook.pst file onto a cd. I had to format my HD and reinstall Windows XP and Office 2003. When I try to open either of the two PST files I get an error message "The file C:\path info\backup.pst is not a personal folder file." Can anyone please help me. All of my important email data is in there. I followed the instructions on the Microsoft site for backing up the file, not it won't open. Did you remove the read only attribute these files acquired from being on a CD? -- Russ Valentine [MVP-Outlook] "Brad&...

Problem with .rc files in VS 2005
I have several projects that were created with VS 2003. These are MFC based applications with .RC files that contain localized strings. Some of these strings are Japanese, Korean, and Chinese encoded in MBCS. These projects work fine in VS 2003. I converted these projects to compile with VS 2005 and if I bring up the resource editor to view or modify the string resources, VS 2005 will modify the Japanese, Korean, and Chinese strings. It will replace the characters with all "?" question marks. Is there some setting that I need to set to prevent VS from modifying my string...

Configure a shortcut file programmatically?
Under program control, I want to change the paths to point to a database on the user's local drive. From within Access, does anybody know how to do this? Are you saying you want to create a shortcut file? Take a look at my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "David Portwood" <Mondrogan@yahoo.com> wro...

Adding attachments to msg-files
I have a big problem regarding editing of MSG-files. This is what I do: 1. Open a MSG-file from Windows Explorer 2. Add an attachment (i.e a Word-document) to the email. 3. Save and close the email. When I re-open the MSG-file the attachment is missing! Instead Outlook has created a copy of the email in my inbox(!), and that copy contains the attachment. This has been tested with different mail-formats (HTML, Plain Text and RTF), and with Outlook 2000 and Outlook 2003 with the same result. Can anyone explain this behavior? I would call this a bug in Outlook, since changes you believe you ar...

Cannot find MRT.log File
I've read that this is located in the windows\debug\ dir. I have searched for the file name in my entire drive and it's not there. I've been running MRT for a long time now, in its monthly updates. The MRT.exe file is located in its proper place. So as it stands the thing runs and I can't find out whether it found bad stuff or not. Can someone please tell me how to get to that log file. Thanks. On this system, the log file is \windows\debug\mrt.log. Jim <programmernovice@yahoo.com> wrote in message news:7b47fa93-576e-4a85-9d8b-b0572d9ad34f@h9g2000yqa.googleg...

how do I angle text in Publisher
I want to create an invitation for my son's Star Wars birthday party and would like to angle the text ot text box like the opening scroll in Star wars. Any help? Try the WordArt. -- Mary Sauer http://msauer.mvps.org/ "kmc17" <kmc17@discussions.microsoft.com> wrote in message news:0D1DB10C-A9A1-45D5-9A0F-02FD746E2CE4@microsoft.com... >I want to create an invitation for my son's Star Wars birthday party and > would like to angle the text ot text box like the opening scroll in Star > wars. Any help? kmc17 wrote: > I want to create an...

File Won't Open
Hi, I accidentally opened an xls file in Word, saved it with the Save command and now can't open it in Excel. Help please! -- Roadrunner ------------------------------------------------------------------------ Roadrunner's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27140 View this thread: http://www.excelforum.com/showthread.php?threadid=466566 It's gone, you might be able to open it in word and get some text info out of it but essentially it's gone :( -- Regards, Peo Sjoblom (No private emails please) "Roadrunner" <Roadr...