Exporting As Text with X Number of Spaces Between Data

Is there a way to export data in a MS Excel spreadsheet 
as text and define the amount of spaces between the data 
being exported?  The number of spaces need to vary.

This concept is the opposite of importing text as "Fixed 
Width" and creating column break lines.  Instead, I'd 
like to export columns into a text file and specify the 
amount of spaces between the text that is being exported 
from each column.  The amount spaces in the text file 
needs to be different between each set of column data.

Thanks!
0
10/2/2003 4:00:15 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
467 Views

Similar Articles

[PageSpeed] 8

One way:

Say you have 5 columns, and you want 2 spaces between A & B, 4 
between B &  C, 3 between C & D and 5 between D & E:

    Public Sub VariableSpaces()
        Dim dArr As Variant
        Dim myRecord As Range
        Dim myField As Range
        Dim sOut As String

        dArr = Array(2, 4, 3, 5) 'number of spaces between columns
        Open "Test.txt" For Output As #1
        For Each myRecord In Range("A1:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                Print #1, .Item(1).Text; Spc(dArr(0)); _
                             .Item(2).Text; Spc(dArr(1)); _
                             .Item(3).Text; Spc(dArr(2)); _
                             .Item(4).Text; Spc(dArr(3)); _
                             .Item(5).Text
            End With
        Next myRecord
        Close #1
    End Sub


In article <166f01c38899$af9dbcf0$a101280a@phx.gbl>,
 "Fonz" <fonzfreely@yahoo.com> wrote:

> Is there a way to export data in a MS Excel spreadsheet 
> as text and define the amount of spaces between the data 
> being exported?  The number of spaces need to vary.
> 
> This concept is the opposite of importing text as "Fixed 
> Width" and creating column break lines.  Instead, I'd 
> like to export columns into a text file and specify the 
> amount of spaces between the text that is being exported 
> from each column.  The amount spaces in the text file 
> needs to be different between each set of column data.
> 
> Thanks!
0
jemcgimpsey (6723)
10/2/2003 6:05:37 AM
Thanks!


>-----Original Message-----
>One way:
>
>Say you have 5 columns, and you want 2 spaces between A & 
B, 4 
>between B &  C, 3 between C & D and 5 between D & E:
>
>    Public Sub VariableSpaces()
>        Dim dArr As Variant
>        Dim myRecord As Range
>        Dim myField As Range
>        Dim sOut As String
>
>        dArr = Array(2, 4, 3, 5) 'number of spaces 
between columns
>        Open "Test.txt" For Output As #1
>        For Each myRecord In Range("A1:A" & _
>                Range("A" & Rows.Count).End(xlUp).Row)
>            With myRecord
>                Print #1, .Item(1).Text; Spc(dArr(0)); _
>                             .Item(2).Text; Spc(dArr(1)); 
_
>                             .Item(3).Text; Spc(dArr(2)); 
_
>                             .Item(4).Text; Spc(dArr(3)); 
_
>                             .Item(5).Text
>            End With
>        Next myRecord
>        Close #1
>    End Sub
>
>
>In article <166f01c38899$af9dbcf0$a101280a@phx.gbl>,
> "Fonz" <fonzfreely@yahoo.com> wrote:
>
>> Is there a way to export data in a MS Excel spreadsheet 
>> as text and define the amount of spaces between the 
data 
>> being exported?  The number of spaces need to vary.
>> 
>> This concept is the opposite of importing text 
as "Fixed 
>> Width" and creating column break lines.  Instead, I'd 
>> like to export columns into a text file and specify the 
>> amount of spaces between the text that is being 
exported 
>> from each column.  The amount spaces in the text file 
>> needs to be different between each set of column data.
>> 
>> Thanks!
>.
>
0
10/2/2003 1:39:51 PM
I would have guessed that you wanted each field to have the same width.

So column A would be padded with spaces to make each the beginning of column B
line up pretty.

If yes, then a minor modification to J.E.'s code would do it:

Option Explicit
Public Sub FixedWidth()
    Dim dArr As Variant
    Dim myRecord As Range
    Dim myField As Range
    Dim sOut As String

    dArr = Array(12, 14, 13, 15) 'length of each field
    Open "Test.txt" For Output As #1
    For Each myRecord In Range("A1:A" & _
            Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            Print #1, Left(.Item(1).Text & Space(dArr(0)), dArr(0)); _
                      Left(.Item(2).Text & Space(dArr(1)), dArr(1)); _
                      Left(.Item(3).Text & Space(dArr(2)), dArr(2)); _
                      Left(.Item(4).Text & Space(dArr(3)), dArr(3)); _
                      .Item(5).Text
        End With
    Next myRecord
    Close #1
End Sub


But if all the values in each column are the same number of characters, then
never mind.



Fonz wrote:
> 
> Thanks!
> 
> >-----Original Message-----
> >One way:
> >
> >Say you have 5 columns, and you want 2 spaces between A &
> B, 4
> >between B &  C, 3 between C & D and 5 between D & E:
> >
> >    Public Sub VariableSpaces()
> >        Dim dArr As Variant
> >        Dim myRecord As Range
> >        Dim myField As Range
> >        Dim sOut As String
> >
> >        dArr = Array(2, 4, 3, 5) 'number of spaces
> between columns
> >        Open "Test.txt" For Output As #1
> >        For Each myRecord In Range("A1:A" & _
> >                Range("A" & Rows.Count).End(xlUp).Row)
> >            With myRecord
> >                Print #1, .Item(1).Text; Spc(dArr(0)); _
> >                             .Item(2).Text; Spc(dArr(1));
> _
> >                             .Item(3).Text; Spc(dArr(2));
> _
> >                             .Item(4).Text; Spc(dArr(3));
> _
> >                             .Item(5).Text
> >            End With
> >        Next myRecord
> >        Close #1
> >    End Sub
> >
> >
> >In article <166f01c38899$af9dbcf0$a101280a@phx.gbl>,
> > "Fonz" <fonzfreely@yahoo.com> wrote:
> >
> >> Is there a way to export data in a MS Excel spreadsheet
> >> as text and define the amount of spaces between the
> data
> >> being exported?  The number of spaces need to vary.
> >>
> >> This concept is the opposite of importing text
> as "Fixed
> >> Width" and creating column break lines.  Instead, I'd
> >> like to export columns into a text file and specify the
> >> amount of spaces between the text that is being
> exported
> >> from each column.  The amount spaces in the text file
> >> needs to be different between each set of column data.
> >>
> >> Thanks!
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/3/2003 12:08:42 AM
Reply:

Similar Artilces:

Ctrl+X messed up
This is driving me nuts! I've put up with it for months and can't find the answer. When I try to use Ctrl+X instead of cutting it automatically grabs the data in the cell and throws it over into a cell a couple of columns away. What in the world is wrong with this thing? James, Maybe not much of a help, but the only thing I can come up with is that somehow a macro is attached to the control+X combination -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "James Fonda" <jimbo2311@netzero.net> wrote in message...

I keep getting and error on start of Office x.v
I just purchased Office X.v and installed it onto my Mac. I also removed Office Test Drive before installing the full Office X.v, but i didn't empty my trash. So after I installed the full version i keep getting the error "An unexpected error occurred while trying to load Microsoft Framework X library" on all office programs. I read on mactopia that this could be caused by "lock" identities in the microsoft identity file, but all of mine are unlocked. When i tried to empty my trash, it would not empty a few microsoft file because it says they are in use. Framework ...

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

Changing text size
Hi, We enter all our customers in our database in all caps (text) for the names, and addresses. But when you put in the zip code and the database auto finds the town and state it is put in upper, and lower case. Is there a way to change this default to all caps for the auto fill. I am experienced in editing xml. files ( just a note) Any help would be appreciated.....Dennis No, there's no way to change that in the front end, that's something that is happening at display time and is meant to make the data more readable. You could enter a suggestion on Customer Sou...

Changing the Tooltip text in an SDI Toolbar
I've been trying to find a way to change the tootip info for a toolbar. Currently, I've tried getting the toolbar CToolBarCtrl and CToolTipCtrl objects but haven't been able to figure out what to do with them. Any help would be appreciated. Thanks, Ken If you are talking about tooltip info of a particular button on a toolbar, change it in resource editor by double clicking on the button and changing the text that appears at the bottom of the dialog. Text that appears before \n is tootip. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ken Slight" <kslight@char...

importing multiple text files???
Hi, I am experiencing a big problem. As a part of my analysis for PhD I have to analyze more then 1000 files. The data that I have is in text files. To be more precise, I have 5 different data sets, from different meteo centres, each centre has a data set of 365 files for each day during the year. The format of text files is something as following: Camborne Met. Office WINDS rev 4.1 50.20 -5.30 88 02 01 01 00 00 23 0 29 3 35 07:09 (3.0) 07:09 (3.0) 06:08 (3.0) 284 284 65 65 400 400 23 23 12.5 12.5 1 1500 1500 35 35 400 400 43 90.0 43 74.5 313 74.5 HT SPD DIR Radials... 0.101 9.8 113 0.0...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Is there a wizard to export and match fields?
I would like to export products from my table into a csv that has different field names. Is there a wizard that would allow me to select which fields to export and what fields they match up to in the new file? Instead of exporting a table, create a query and alias the field names to the ones you want in the CSV, then export the query. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L "ZenMasta" <me...

check number > 10000
I have a checking account where my check numbers recently went over the 10000 mark and my credit union told me that the OFX format only goes up to 9999 so they start over at check number 1. I find it hard to believe that this could really be true. Anyone else run into this and have a better solution? Would another bank not have this issue? I use Money 2004 and it allows me to enter check numbers over 10000, but when I download my transactions from my Credit union it knows enough to match the check up correctly (IE: Credit union check number 10 match my entry of 10010) but when I accept ...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

How can I add numbers but ignore the minus signs?
How can I add numbers but ignore the minus signs? In the following, the answer I'm after is 17 A1 -10 A2 5 A3 -2 =SUMPRODUCT(ABS(A1:A3)) Gord Dibben MS Excel MVP On Thu, 07 Jun 2007 04:19:52 GMT, Invalid <invalid@invalid.comINVALID> wrote: >How can I add numbers but ignore the minus signs? In the following, the >answer I'm after is 17 > >A1 -10 >A2 5 >A3 -2 > > Gord Dibben <gorddibbATshawDOTca> wrote in news:1o2f63pa84r8o6tdf65sa9ptpbv0nlt7u6@4ax.com: Thank you Gord. ----- > =SUMPRODUCT(ABS(A1:A3)) > > > Gord Dibben M...

Spinning Beach Ball
I seem to notice a correlation between seeing the spinning beach ball more often after launching MS Word for OSX. I removed the PDFMaker.Lib and this improved the performance a great deal, but still noticing the SBB more often after launching Word. Anyone know about this? Many Word issues are related to corrupt Word Settings or Normal Template. You could try removing the Word Settings file, then testing. The Normal Template could be renamed, then test. Be sure Word is closed before removing or changing any files. You could also consider running the Remove Office utility and reinstalling Offi...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

retrieving data from Great Plains via eConnect
Hi, I've been working with eConnect for the last two weeks and have had success with importing a variety of transactions into Great Plains. However, I'd like to query Great Plains on demand and have data returned to me. For example, given a certain field value (like customer name = "Smith"), I'd like to have all customer records returned that match that specified value. Is there any way to do this easily with eConnect? I'm not so interested in the Transaction Requester Service because that seems to be focused more on automatically returning records that have...

Broken x-axis in excel
Hi. I want to create a chart, where my x axis contains values within a range above zero, and to a given value. So far so good. My problem involves that i want the axis itself to show that there is a break from zero to my given value (marked with a Z-like sign), wich excel does not automaticly do. Is this possible to acheive? I have seen charts vith this prefix, but there is no such function, or description of how to do it in the "help register". Thank you. -- ketil This page shows one approach, and links to a couple others: http://peltiertech.com/Excel/Charts/BrokenYAxis....

Using a Space in Webbrowser Control
Hello: As usual, I've run into a problem with something that is probably so simple... any help is appreciated. I'm using the webbrowser control with the navigate method. The url I'm using needs a space (which I know is not normally allowed, but it works directly in firefox and IE). Here's some sample code: ------------------------------------ strWebPage = "http://www.imdb.com" &" ?" & Text2.Text txtURL.Text = strWebPage Web1.Navigate txtURL.Text Do While Web1.ReadyState <> READYSTATE_COMPLETE DoEvents Loop DoEven...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...

find sum in list of of numbers
Hello, I have a list of numbers in a column and I need to find which numbers when summed together equal a figure. I have a list of invoice amounts that I need to match up with payments (the payments are always made for several invoices so I need to come up with sums of several invoices to get to this payment amount). An example would be I have this in the following section (A1:A10): $17,213.82 $4,563.02 $85,693.42 $1,166.01 $725.90 $580.09 $2,243.75 $240.16 $207.70 $725.90 I need to find which combination of these figures would sum $1,173.76. Thanks in Advance, Dza the troubled ...

Extracting data from one sheet to another
I have a speadsheet with 390 rows and 80 columns. For each column, I wish to copy a range of cells and place them in rows so I can create a separate table for each item represented by the column. For instance, I would like to take the data from the range B10:B15 and place that into a range A1:E1, and then data from range C10:C15 and place into a range G1:K1, D10:D15 into I1:M1 and then B16:B21 into A2:E2, C16:C21 into G2:K2, etc, etc, etc I could do this manually, but it could take a while. I was wondering if there was an easier way to do this. Any help would be greatly appreciated. Thanks...

Collaboration Data Objects (CDO) ?
Is this technology still current or is there something better to use? Can I use CDO to access Outlook on a non server operating system? Thanks Tom ...