I am using excel 2003. I need to import multiple text (.txt) files into excel. Below is an example of the type of text file that I need to import. Importing in and of itself isn’t the tricky part, but I have 125 files to do every day. I know I can use VB to automate and loop through this. I have some VB experience but haven’t been able to get it to work. I would like the macro to do the following:
1. <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com
ffice:smarttags" /><st1lace w:st="on">Loop</st1lace> through all the text files: I have a specific folder that the text files get saved in. I would like the macro to go to that specific folder (C:\My Documents\Sales), import each text file to an excel file, then save the excel file in that folder with the company name and date that is in the header of the text file. (Example of a file name from below I would like: “Company1 122109”. Doesn’t have to be exactly that, that was just a flavor of what I would like). There will be 125 excel files when done.
2. I only want it to bring in certain rows: the column headers (rows 5,6,7), and the sales “types” (rows 8-18). The number of rows to import can vary by file: sometimes there are 5 sales “types”, sometimes there are 15 sales “types”. I need the macro to be flexible enough to handle this.
Any and all help is much appreciated! Thank you.
1. <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com
ffice:smarttags" /><st1lace w:st="on">Loop</st1lace> through all the text files: I have a specific folder that the text files get saved in. I would like the macro to go to that specific folder (C:\My Documents\Sales), import each text file to an excel file, then save the excel file in that folder with the company name and date that is in the header of the text file. (Example of a file name from below I would like: “Company1 122109”. Doesn’t have to be exactly that, that was just a flavor of what I would like). There will be 125 excel files when done. 2. I only want it to bring in certain rows: the column headers (rows 5,6,7), and the sales “types” (rows 8-18). The number of rows to import can vary by file: sometimes there are 5 sales “types”, sometimes there are 15 sales “types”. I need the macro to be flexible enough to handle this.
Any and all help is much appreciated! Thank you.
Code:
1
6L - Company #1
2 Daily Segmentation Report
3 22Dec09 12:24 AM As at: 12/21/09 (DAY 21 OF PERIOD 12) - 31 DAY Page 1
4 ----------------------------------------------------------------------------------------------------------------------------------
5 ************ SINGLE OCC ************* *********** MULTIPLE OCC ************ **************** TOTAL **************
6 MKT SEG #rms revenue avg-rate %share of #rms revenue avg-rate %share of #rms revenue avg-rate %share of
7 rms sold rms sold rms sold
8 Type1* 1 79.95 79.95 4% 0 0.00 0.00 0% 1 79.95 79.95 3%
9 Type2* 4 339.80 84.95 16% 0 0.00 0.00 0% 4 339.80 84.95 13%
10 Type3* 5 449.75 89.95 20% 2 179.90 89.95 33% 7 629.65 89.95 23%
11 Type4* 1 95.95 95.95 4% 0 0.00 0.00 0% 1 95.95 95.95 3%
12 Type5* 6 486.00 81.00 24% 1 81.00 81.00 17% 7 567.00 81.00 23%
13 Type6* 4 0.00 0.00 16% 2 0.00 0.00 33% 6 0.00 0.00 19%
14 Type7* 1 39.00 39.00 4% 0 0.00 0.00 0% 1 39.00 39.00 3%
15 Type8* 1 199.90 199.90 4% 0 0.00 0.00 0% 1 199.90 199.90 3%
16 Type9* 0 0.00 0.00 0% 1 79.95 79.95 17% 1 79.95 79.95 3%
17 Type10 1 89.95 89.95 4% 0 0.00 0.00 0% 1 89.95 89.95 3%
18 Type11 1 79.95 79.95 4% 0 0.00 0.00 0% 1 79.95 79.95 3%
19 -----------------------------------------------------------------------------------------------------------------------------------
20 Totals 25 1,860.25 74.41 6 340.85 56.81 31 2,201.10 71.00
21 ***********************************************************************************************************************************
22 Total People: 40 Rate per person: 55.03
23 Total Rooms: 31 Average room Rate: 71.00
24 Total Revenue: 2,201.10
