Data Import from text delimated file

  • Thread starter Thread starter David Price
  • Start date Start date
D

David Price

I am trying to import a comma delimated text file into a spreadsheet but I
am having some trouble. I can import the data no problem but what I need to
do is import it in a different order than it is in the source file. I also
need this to be incorporated into a macro.

For example my source file has the following fields:
cy_address1, cy_address2, cy_address3, cy_address4, pt_value, pt_title

When I import it into Excel I want it to go for example in the following
order:
pt_title, pt_vlaue, cy_address1, cy_address2, cy_address3, cy_address4

For the life of me I cant find anything that will do this. Would importing
into sheet2 and then using the INDIRECT (or any other) function to copy the
colums into sheet1 work? Sheet2 would also need to be hidden as the
original formatting cant be seen.

Thanks

Dave


Technical Support
ABI Building Data Ltd
www.abibuildingdata.com
 
Hi,



The easiest way is, after the data are imported, click the letter of the
column to select it all and then, bring your mouse back on the letter, drag
it in the right order. But if you like a macro to do that, I could make one
for you.
 
Dave,

You can record a macro as you juggle the columns into the order you wish.
Start the macro recorder (Tools, Macros, Record). While it's running.
select a column to be moved (click the column heading). Now holding Shift,
drag that column by its border to the new location. Stop the recorder.
Note that this will move that physical column, without regard to the
heading. If the order of the columns is uncertain, you may wish to use
Access to import the text file, using a linked Excel table to your sheet.
It will place the columns by the column headings (first record, like Name,
Address, etc), regardless of physical position.
 
David said:
I am trying to import a comma delimated text file into a spreadsheet
but I am having some trouble. I can import the data no problem but
what I need to do is import it in a different order than it is in the
source file. I also need this to be incorporated into a macro.

For example my source file has the following fields:
cy_address1, cy_address2, cy_address3, cy_address4, pt_value, pt_title

When I import it into Excel I want it to go for example in the
following order:
pt_title, pt_vlaue, cy_address1, cy_address2, cy_address3, cy_address4
Click to expand...

I'm not great at VB, but my approach would use the macro recorder. Start
recording, import a sample text file, rearrange the columns per your wishes
using copy and paste, then stop recording. The basic commands will be
written in the recorded macro, and you can modify them as you wish.

Dave
dvt at psu dot edu
 
Back
Top