+ Reply to Thread
Results 1 to 9 of 9

VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Macro

  1. #1
    Registered User
    Join Date
    01-11-2007
    Posts
    94

    VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Macro

    Hello All,

    Appreciate help this forum and contributor provides.

    I have existing macro (below) which combines (50+) multiple text file delimited via "|" from given folder and saves as as excel table. I want to add text file name to given record field in table so i know where I pulled this record from and which file it belongs to. Can this be done in this macro? I have to do this every day so this would help in validation.

    Folder has multiple .txt file like below

    I_CNNSR6_09112012.txt
    E_CBSSR6_09122012.txt

    I want to add file name as column field against the record from that file let say E_CBSSR6_09122012.txt file has following records (or multiple) pulled in table

    Location|Product1 |38080090|7500|KGS|7510937.5
    Location|Product2 |38081090| 500|KGS|751937.5

    I want get this way in my table columns

    Col A Col B Col C Col D Col E Col F Col G
    Location Product1 38080090 7500 KGS 7510937.5 E_CBSSR6_09122012
    Location Product2 38081090 500 KGS 751937.5 E_CBSSR6_09122012

    Thanks
    NKRA


    Credit to following for following macro site http://www.rondebruin.nl/csv.htm

    Macro

    Please Login or Register  to view this content.
    Last edited by NKRA; 11-11-2012 at 07:14 PM.

  2. #2
    Registered User
    Join Date
    01-11-2007
    Posts
    94

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Hello , Any help on this?

  3. #3
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Try this:
    Please Login or Register  to view this content.
    Post responsibly. Search for excelforum.com

  4. #4
    Registered User
    Join Date
    01-11-2007
    Posts
    94

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Hi,

    I have no knowledge of coding language, how do i add above solution into my original code? Any guidance or suggestion?

    Rgrds,NKRA

  5. #5
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Use my code instead of your code. Press Alt+F11 in Excel to open the VB Editor, click Insert -> Module to create a new standard module, paste all my code into the white editor pane where the cursor is and run the Main_Import macro.

  6. #6
    Registered User
    Join Date
    01-11-2007
    Posts
    94

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Hi Chippy,

    I did use your solution it works no issue on that front, file does lot of flickering and takes a bit more time than the solution i had shared earlier. So is there way to get your solution into my earlier code?

    Kind Rgrds, NKRA

  7. #7
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    To stop the screen flickering, put:

    Application.ScreenUpdating = False

    at the start of Import_Text_Files_in_Folder

    and:

    Application.ScreenUpdating = True

    at the end.


    Your code creates a single file using DOS COPY *.txt singleFile.txt, and imports that single file into Excel, so it is bound to be quicker than importing text files one by one as my code does. However, I can't think of a way to get each file name into the associated records of the singleFile.txt created by DOS.

    A slightly faster method would be to use a query table (Data - Import External Data - Import Data) to import each text file directly into the destination sheet, updating the destination cell for each file. Use the Macro Recorder to generate the code to get started with this method.

  8. #8
    Registered User
    Join Date
    01-11-2007
    Posts
    94

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Thanks for suggestion, will try to implement this. I surely need file name to associate the record. Many thanks for your help.

    Regards NKRA

  9. #9
    Registered User
    Join Date
    01-11-2007
    Posts
    94

    Re: VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Ma

    Is there way to separate following in 3 columns

    I_CNNSR6_09112012.txt

    Col H Col I Col J
    I CNNSR6 09112012

    Rgrds
    NKRA

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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