macro to read and sort data from multiple text files

Hello there !!

I have x number of ascii text files that have space delimited data
columns (files may have 3 to 40 data columns). There are about 25
header rows at the top of each file but I am not going to use info
from these rows. I also have a master.xls file. Could someone please
help me create a macro to do the following -

A) read first *.txt file from the given folder into sheet1 of the
master.xls file.

NOTE: I have already set up the sheet2 of the master.xls to sort
required rows of data from sheet1 (based on row headers) using vlookup
to populate a 40 column wide array.

B) Copy the sorted array of cells from sheet2 that have numbers in
them (since the number of columns in the text files vary, I may have
cells in sheet2 that have #REF! in them if the number of columns to be
read from sheet1 are less than 40) and paste (values only) into sheet3

C) clear the contents of sheet1

D) repeat (A) for second *.txt file

E) Copy the sorted array of cells from sheet2 that have numbers in
them and paste (values only) into sheet3 appending to the right of the
columns that were already in sheet3

F) repeat (D) and (E) untill all the *.txt files have been read



3/15/2007 10:59:55 PM
excel 39879 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 29


Similar Artilces:

Macro warning with no macros
I have a workbook that when I try to open it I get the warning tellin me it contains macros and asking whether I want to Enable or Disabl them. I put no macros, no VB scripts, nothing at all in the workboo other than standard Excel formulas and some charts. I developed it i Excel 2002, SP-2, but have moved it back and forth between tw computers, the other which has Excel 97. Regardless of whether Enable or Disable or change the security settings so as not to get th warning, once the workbook is open even if I go t Tools-Macro-Macros... it lists no macros. How can I find out what's cau...

Can anybody tell me how to kick off a macro automatically as file is opened? Regards ...

Convert old PPT file
My company recently upgrade the office 2003 to office 2007. It works well for most of the users except one. He had thousands of old powerpoint slides which created around year 1996,1997. By using right click to check the file properties, it shows the the application name is 'Microsoft Powerpoint 4.0' .These file can be opened by powerpoint 2003 but can not being opened by powerpoint 2007. Does anyone have idea how I can bulk convert those files to powerpoint 2003 format? Thanks James Steve has a macro here -- john ATSIGN PPTAl...

Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: intel Hi, I'm thinking of buying Mac: Office 2008, but before I do I want to check that I will be able to download Scritpsmart collection of templates without any glitches: <> Does anyone know if I can do this? Regards, SP. In article <ee98ff5.-1@webcrossing.caR9absDaxw>, wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > Processor: intel > > Hi, > > I'm thinking of buying Mac: Of...

encoding wmv file
hello I have a sample application developed in MFC dialog based project using directx sdk 9. the sample captures video from a webcam and saves it to a wmv file now i have to put that video into streaming server. i beleive that i will have to encode the wmv file using some codecs to put it into streaming server. i want to do the capturing and encoding simultaneously. below is a portion of my code to capture video // capture video pBuilder->RenderStream(&PIN_CATEGORY_CAPTURE, // Pin category &MEDIATYPE_Video, // Media type p...

Attached Toolbars Macros
Excel 2003 I have a custom tool bar, this tool bar has several items, each item is pointing to a VBA code. This tool bar is attached to the worksheet. I've copied this spreadsheet to several computers. After I copied the spreadsheet I decided to rename the spreadsheet, after I renamed the spreadsheet the Toolbar Item's macros were still pointing to the old name, I reasigned each tool bar item to the current spreadsheet, saved the spreadsheet and everything was working on my computer, but when I re-copied the new spreadsheet to other computers and I clicked on the Toolbar Item...

Sum after Sorting #2
There is an Excel Spreadsheet like the following Project Value Sum Power 120 354 (Power + Computer) Computer 234 AV 123 Quest 200 In other words, the formula for 354 is "=B2+B3" There is a requirement that we have to sort the first column in ascending order but keep the sum still be the value for Power & Computer (as follow) Project Value Sum AV 123 354 Computer 234 Power 120 Quest 200 I have tried a number of ways but still cannot get it fixed. Is there any suggestion ? Hi! Based on your example in the second table it lo...

File location?
Where are the aspx files stored? http://site/subsite/list/defautl.aspx where would i find that? the reason i ask is when i go to export there are a couple of files that im told access denied. not sure why since im the admin on all levels. Thanks Justin Well, that really depends... mostly your stuff is stored in SQL, which means that its really data in a table cell. However, depending on whether a page has been customized or not it could be pointing to definition files that are stored on the Web Front End servers, and you really want to leave those alone. Furthermore, th...

Run macro automatically.
How do I make a macro run automatically when a worksheet it is attached to is loaded? right click on the sheet tab>view code>left window worksheet>right window activate -- Don Guillett SalesAid Software "Excel macro" <Excel> wrote in message > How do I make a macro run automatically when a worksheet it is attached to is > loaded? I am unable to find "activate " when I right click on worksheet. ( i assume that you are referring to the windo...

How to Use One PST file on Two Computers?
Hello: I have a desktop machine with a pst file in my Outlook 2003. I would like to copy this pst file to my laptop. How do I synchronize the same file on 2 machines so I won't get duplicate e-mails? I use one premium Yahoo e-mail account and several free Yahoo accounts with YahooPOPs.. Thanks in advance, Spudy Hi Spudy, it is not possible by default to share PST files. A list of a list of third-party synctools are also on this site: On the Microsoft Office Marketplace site is a list of third-party messaging tools:

Help with macro/and or formula?
Perhaps this is a silly question, but i just can't figure out how t format this worksheet. The problem is that a lot of the cells hav numbers in them with negative signs in the back instead of in the fron (ie. 23-) and i was wondering if anyone knew of a macro/formula i coul use to quickly fix this problem for all the cells. I would b extremely grateful. Thanks -- brefed1 ----------------------------------------------------------------------- brefed15's Profile: View this thread:

Protected sheets and running macros
i have a sheet with a macro ran by a normal button at the top. Howeve when i protect the worksheet where ever i click on the sheet the macr executes. Can any one offer any advice on how to stop this? Thanks Robert -- Message posted from hi, i am not able to reproduce the problem, hence i may not be able t provide a panacea for that issue. here is what i suggest. i have a sheet that has a macro that is run when a control toolbo button (that is, not a form button) is clicked. this sheet is protected based on the user logged in. what i have don is to protect t...

Importing a pst file in Outlook XP
My computer's configuration changed and I had to delete some hard disk partitions. After doing that, when I open Outlook it pops up a window saying "Unable to open your default e-mail folders. The file F:\{...}\outlook .pst could not be opened". Then I press OK and then a new pop-up says "Would you like to open your default File System folder instead?" If I press Yes, Outlook opens without (of course) the outlook.pst file. Also, I can't import my pst file neither I can Create a new one. If I press No, Outlook closes. Does anyone know how to open my pst fil...

How to setup online access to multiple accounts in one institution
I am using Money 2004 Small Business. I have 4 different accounts with Ameritrade, each account has a different online account ID and pin number. Money does not appear to let me setup all four accounts for activity download, it appears to permit only one set of account login info per institution. Is Microsoft short sighted in designing this or I am missing something? Please help, your effort will be greatly appreciated. Matt In, Admin wrote: >I am using Money 2004 Small Business. I have 4 different accounts >with Ameritrade, each account has a different onl...

Macro Freezes Reading Pane
I have a macro that Sends/Receives Tasks from Project Server into Outlook Tasks. Everything is working fine, except one weird thing that happens. I have the Timer set on the macro and it starts as soon as Outlook starts. The macro gets the tasks from ProjectServer every hour. As soon as the macro starts running the Reading pane freezes in the Inbox. If i stop the macro then Reading pane is back to normal. Can anyone please tell me if there is something I can do in VBA to fix this annoying issue. Thanks a lot ...

Macro to remove rows?
Hi, I receive thousands of rows of data from an extract that comes from a database. The data extract automatically generates page headers that need to be removed, and I would love to automate this process. The headers do have a pattern to them, but I'm not clever enough to figure out a good way to use that to write a macro. The first row of the header starts with hyphens ------------------------------- Then there's a variable number of rows of header data to delete followed by another row of hyphens ------------------------------- Then 2 additional rows that must be deleted. The nu...

Where is Read All Unread Mail ?
Using Outlook 2003, had to re-create my profile due to corruption. And now I no longer have a Folder title to display ALL UNREAD MAIL. Does this need to be created somehow, so that it will display all unread mail spread out in all the folders? if the folder is not in the search folders, right click on Search Folders and choose New. Its one of the predefined options. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? Outlook 2007: Outlook Tips by...

How can I sort and maintain formatting?
I have a very large excel doc (well over 1,000 rows, by 30 somethin columns) that require formatting (resizing the row height) each row b hand. I have done all of that tideous work, but now I need to sort b multiple columns, and when I do that, the formatting of the cells i not maintained. (The size of the row stays, rather than moves with th data) Please tell me there is a remedy and I haven't just wasted m time formatting for nothing! Thanks for any and all advice -- littlelostgr ----------------------------------------------------------------------- littlelostgrl's Profile: http:/...

Rules on Read receipts
Thanks to anybody who can answer this. I'm starting to get cross-eyed trying to figure this one out. Is there any way to create a rule for a message that is a read receipt? Read receipts (in my system) have no "To:" address specified. For some reason, I cannot create a rule that will send them directly to a folder. I know that doing this is very simple, but it's like eating spinach if you don't like spinach - I just can't do this! Please help! (And for those of you who are wondering, no, I don't like spinach...) which version of OL are you using? "hendi...

Duplicate Data
One main form with two subforms (linked by ProjectID), first subform is for data entry, the second just allows the user to view what has been saved from the previous subform. The subform allows users to report on a month basis by selecting a month in a combo box [PeriodID]. I would like to add some code in the Befire Update event that checks the PeriodID for a match before it is saved. Can someone help me with the code please, I have tried various DLookup snippets of code from this site, but nothing is working as the code does not really suit my purpose. regards Maybe t...

Exporting to Word in a csv file
I am doing the above and leading zeros are being dropped...I need them in because they are account codes that I am uploading to my GL. Can anyone help? Brad I've run into leading zero problems before when exporting/importing. Try this--make sure your data items with leading zeros are "text" format, that should preserve the leading zeros. I made a test file, saved it as CSV, then pulled into Word (insert file), and the leading zeros were still there. But, if you open up the CSV file again in Excel, the leading zeros disappear... "Brad" <Brad@discussions.microso...

I cannot find the option in Outlook Express as to how to set up my inbox when reading e-mails to go back to the inbox instead of opening up the next message. My older version had it in Tools/Options/Reading and I also have it available at my office in the professional version. Where is this setting located now? Hi, Julie - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Ex...

Calling on a macro within a macro!
Hey All!! Quick Question - How do you call up a macro within a current macro? I have one big macro and I need it to jump to several other macros when it runs! Does it matter where the other macros are stored or is it better to have them all in one module? Help appreciated Thnx! Hi Mellowe, '=============>> Public Sub Main() 'Your code Call One 'your code End Sub '<<============= '=============>> Public Sub One() MsgBox "Hi from One" End Sub '<<============= > Does it matter where the other macros are stored or ...

Remoting and File Size Limit
Hello Group: I have been directed to ask this question in this group. If this is the incorrect forum: please let me know. We have a web-app that uses remoting to access some components. XML from the web page is sent to these components and a value is returned. We have noticed that on one server: this works great, but on two other servers, after the XML gets to be bigger than 4KB, the XML gets truncated and as a result: the process fails. We feel failrly confident this is some server issue / setting and not really a coding issue however are totally clueless on where to look to make ...

shortcut keys to macros
Hi, Is it at all possible to assign shortcut keys to macros in Outlook? Thx, Hans ...