VBA code for text to column in 2002

  • I have a report with details for a large number of countries. Each month, I need to copy the country details and move them to a sheet specific to that country,
    e.g. UK details to a UK folder, Germany details to a DE folder. The level of details available is different for each country. So there may be 10 lines for the UK but only 5 for Germany. However, the country details always start with (e.g.) 'COUNTRY: UK' and end with 'TOTAL FOR: UK.'


    Any suggestions on how the macro could look?


    I also published the question on:
    http://groups.google.com/group/excel-vba?lnk=rgh&hl=en


    and:
    http://groups.google.com/group/Microsoft-Excel?start=0&hl=en


    But so far no one's responded...

  • Re: Move macro


    It depends how your data is laid out. Is it in a proper database format, ie the country references in one column? If it is then Advanced Filter should do it.

  • Re: Move macro


    Are you trying to import from .txt? Also, is that your email address you are using as your Member name? If it is then it's not a good idea unless you want lots of spam etc

  • Re: Move macro


    Quote from [email protected]

    ... the country details always start with (e.g.) 'COUNTRY: UK' and end with 'TOTAL FOR: UK.'


    Any suggestions on how the macro could look? ...


    I used variations of the following for five years to import data from *.txt into *.xls reports
    Perhaps not the purest method but quick & dirty works just as well.


  • Re: Move macro


    Hi Roy
    Thanks for the quick reply. Yes, the file is a .txt file. I've created the macro that imports the file automatically but it's the move of the details I can't figure out.


    Another thing I can't figure out: How do I change my user name? I've tried to look for the right place to do it. Thanks for the tip, btw.

  • Re: Move macro


    Quote from Gday Bruce

    I used variations of the following for five years to import data from *.txt into *.xls reports
    Perhaps not the purest method but quick & dirty works just as well...


    Thanks for your tip, Bruce. I'll give it a try soon and let you know how it goes!

  • Re: Move macro


    Here is another code for importing from a text file.


  • Re: Move macro


    Hi max_lux
    I've got the file import figured out, so it's about getting the macro to scan the .txt file after import and move the country sections to the matching sheets. I got a tip from Gday Bruce the other day, but if you have another suggestion, I'm all ears ;) I've also attached an example of what the .txt file looks like

  • Re: Move macro


    Quote from Gday Bruce

    I used variations of the following for five years to import data from *.txt into *.xls reports...


    Hello Bruce. I had a go at your suggestion and changed minor things. But when I run the code, I first get a "Compile error: Variable not defined" at the "CountryFile = Mid..." line.


    If I omit that line and run the code again, I get a "Compile error: Syntax error" at the "Do While Left..." line.


    Can you tell me what I need to do to correct it?


  • Re: Move macro


    Hi Norie
    I attached an example txt file a couple of weeks ago, which you can find in this thread.


    Basically, I want my macro to look for (e.g) "COUNTRY: UK" and copy all lines down to "TOTAL FOR COUNTRY: UK", then paste that range to a folder called UK. I then want the macro to loop onto the next country and repeat the step above.


    Is there a way to have the macro perform lookup in a sheet with all the countries, so I don't have to include them all in the actual macro?


    I've got the report import macro in place, so that part is not a problem.

  • Re: Move macro


    Abi


    I looked at the text file and just couldn't figure out the data structure.


    Oh and by that don't mean where the data for each country starts and finishs, I mean the actual numeric data.


    Can you either attach a sample workbook or post the code that imports the data into Excel in the required format?

    Boo!:yikes:

  • Re: Move macro


    Quote from Abi

    I first get a "Compile error: Variable not defined" at the "CountryFile = Mid..." line.


    If I omit that line and run the code again, I get a "Compile error: Syntax error" at the "Do While Left..." line.


    Can you tell me what I need to do to correct it?



    Abi, see suggestions in code below;


    1. You didn't appear to be reading the first line of data from #2 before cycling through your code. (matchline was not set before you reached the first IF)
    2. matchline is the string from #2, you were replacing it with what is now the linecounter variable
    3. LEFT(string,#characters), in your LEFT(matchline,1) statement you're asking if "C"="COUNTRY"
    4. A "Do" goes with a "Loop", "Then" belongs to "If"


    Hope this helps.:ole:



  • Re: Move macro


    Abi, just had another look at this.


    The original code was developed to extract from a .txt file and used the command "Line Input" to obtain the next line from a .txt document (#2).


    To load the data from another worksheet (616) you'll need to substitute something like this;


    If this isn't what you're trying to do, please post a small data sample to help out diagnostics.


    Cheers

  • Re: Move macro


    Hi Norie and Bruce
    I attached an example of the workbook I read the txt file into. It also includes examples of the sheets I want to copy data to. I also attached the report import macro below, since I don't know how paranoid people are about opening a workbook from a 'stranger' with macros : D . I'm always a little cautious myself...


  • Re: Move macro


    Hi Norie
    I'm attaching a more 'real life' version of the txt file, ie the one I used in the workbook. The file is based on a data download from DB2. I know it's not 'pretty' but unfortunately I can't get it to look any other way. It does include all the data I want the macro to look for, though. For example in line 7and 17 in the workbook. Hope one of you can help me proceed... :thanx:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!