+ Reply to Thread
Results 1 to 3 of 3

Open file based on cell value and paste data

  1. #1
    Registered User
    Join Date
    bristol, England
    MS-Off Ver
    Excel 2007

    Open file based on cell value and paste data

    Hi guys

    I'm trying to write a short piece of code to prevent me from having to open 200+ .txt files and copy& paste the data into excel!
    Basically I have a workbook with 15 sheets in. Each sheet is laid out exactly the same.
    In each sheet I need to copy and paste data from .txt files into it in seperate columns The .txt files are in 10 different folders (the .txt in thses folders are all named the same, They are outputs from various model runs).

    What I require is a macro that for each worksheet in my workbook, will open a .txt file from a folder, the location of which I can insert in a cell. Copy the data from a column and then paste it into the column that I specify in worksheet.

    What I envision is:

    1) Manually insert into a master cell, on a master sheet, the column number relating to the column I need the data to be inserted into in each of the worksheets (Cola)

    2) Manually Insert the Folder address where the text files are located into a master cell (Fileloc)

    3) Command button on the master sheet that when pressed.....

    4) the macro will go to the first worksheet get the .txt file name from the cell (4,Cola)

    5) open the .txt file based on the .txt filename and location (Fileloc)

    6) copy the data from column 2 of teh .txt file and paste into the worksheet cell (6, Cola)

    7) then repeat the process for each worksheet, except for the mater sheet which has the cells that I have put the FileLoc and Cola numbers into.

    I can then manually adjust the file address and column number and just keep running the macro until all of my columns are filled. Da Dah!!!!

    I hope this makes sense and I'd really appreciate any help. I've had a go at putting some code together from other bits and pieces but it doesn't work. I'll try and attach an example of the workbook and code.

    p.s. it wont let me attach workbook at the moment but I'll try again later. Not sure the code alone will be much use.

    Please Login or Register  to view this content.

    Many thanks again

    And merry christmas

  2. #2
    Registered User
    Join Date
    MS-Off Ver
    2003, 2007, 2010

    Re: Open file based on cell value and paste data

    Personally, I would break your macro down into a suite of macros and then call these in from a new macro for CommandButton1_Click.

    The new CommandButton1_Click would loop through each of the 200 text files in turn.
    Access and Excel Developer | UK

  3. #3
    Forum Expert
    Join Date
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home

    Re: Open file based on cell value and paste data

    hi Aller01,

    I realise this is quite an old thread but I came across it in my wanderings. Do you still need help with this question?

    I've had a go at tidying up your code but I'm not sure if the modified version meets your requirements.

    Please Login or Register  to view this content.
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1