+ Reply to Thread
Results 1 to 20 of 20

Macro tweaking and better way to import text file using VBA

  1. 03-19-2014, 12:21 PM #1
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Macro tweaking and better way to import text file using VBA

    Hello,

    I have recorded the following macro to import a text file.May i request senior members to help me tweak this code or show a new way of importing the same. Also i can't figure out how to remove the unwanted rows and unwanted headers after importing the text fle.

    Sample file is attached below.

    http://www.fileswap.com/dl/U4ullXptMC/

    The macro that i have recorded

    Please Login or Register  to view this content.
    Note : This post has been crossposted at http://www.mrexcel.com/forum/excel-q...s-headers.html

    any help will be of great use to me and i can learn new way to import the file.

    Regards,
    Zaska
    Reply With QuoteRegister To Reply
  2. 03-19-2014, 01:08 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro tweaking and better way to import text file using VBA

    Hello zaska,

    This is really the best and most flexible method of importing text files. Since there many different ways files text files can be formatted, what exactly did you want to do with this file after it is imported?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 03-19-2014, 01:28 PM #3
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Sir,
    Thank you for the reply. I am looking to import the above using a procedure in the following way.

    Please Login or Register  to view this content.
    After importing the text file i want to remove the duplicate headers and sort the data according to Account number and remove the unwanted rows having "-----------". I have attached formatted excel sheet herewith.

    If you have some time can you please show a new method to import the text file other than the way i have recorded the macro?

    Regards,
    Zaska
    Attached Files Attached Files
    Last edited by zaska; 03-19-2014 at 01:31 PM.
    Reply With QuoteRegister To Reply
  4. 03-19-2014, 08:14 PM #4
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Can anyone help me to tweak the macro?

    Regards,
    zaska
    Reply With QuoteRegister To Reply
  5. 03-19-2014, 10:12 PM #5
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro tweaking and better way to import text file using VBA

    Hello Zaska,

    This macro will create a clean copy of the text file. However, it does not copy the data to the workbook. 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 03-19-2014, 10:23 PM #6
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Hello Sir,

    Thank you very much. Could you please make it copy data to the workbook including the headers.Because I want to analyse the data in excel and the real text file has thousands of rows.

    Thank you for your valuable time.

    Regards,
    Zaska
    Reply With QuoteRegister To Reply
  7. 03-19-2014, 11:00 PM #7
    mc84excel
    mc84excel is offline
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Talking Re: Macro tweaking and better way to import text file using VBA

    Quote Originally Posted by zaska View Post
    After importing the text file i want to remove the duplicate headers and sort the data according to Account number and remove the unwanted rows having "-----------".
    Try this HTH
    Attached Files Attached Files
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************
    Reply With QuoteRegister To Reply
  8. 03-19-2014, 11:04 PM #8
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro tweaking and better way to import text file using VBA

    Hello Zaska,

    I modified the macro to include the headers. You can use the recorded macro (Macro1) to import the clean file.
    Please Login or Register  to view this content.
    I have changed the file name in the macro to the clean copy.
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 03-19-2014 at 11:07 PM.
    Reply With QuoteRegister To Reply
  9. 03-19-2014, 11:15 PM #9
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Thank you very much.

    If you don't mind can you show me a different way to import the text file other than the recorded macro. I am just curious to learn.
    Reply With QuoteRegister To Reply
  10. 03-22-2014, 09:22 PM #10
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Sir,

    If you have some time could you please let me show a different way to code the same to import the text file?

    Thank you
    Reply With QuoteRegister To Reply
  11. 03-22-2014, 10:13 PM #11
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro tweaking and better way to import text file using VBA

    Hi Zaska,

    I'd like to see that
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  12. 03-22-2014, 10:47 PM #12
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Hello Sir,

    Thank you very much.

    Regards,
    Zaska
    Reply With QuoteRegister To Reply
  13. 03-23-2014, 11:43 AM #13
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Post Re: Macro tweaking and better way to import text file using VBA


    Hi zaska,

    another way example in the threads :


    Regards !
    Reply With QuoteRegister To Reply
  14. 03-23-2014, 02:12 PM #14
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    @ Marc L

    I am new to programming and thank you for the above links. When you have some time,could you please be kind enough to have a code for the text file that i have posted above.

    Thank you very much.

    Regards.
    Reply With QuoteRegister To Reply
  15. 03-23-2014, 04:53 PM #15
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro tweaking and better way to import text file using VBA


    For VBA newbie, QueryTable is the best way …
    Reply With QuoteRegister To Reply
  16. 03-23-2014, 06:18 PM #16
    mc84excel
    mc84excel is offline
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Macro tweaking and better way to import text file using VBA

    Quote Originally Posted by zaska View Post
    Sir,

    If you have some time could you please let me show a different way to code the same to import the text file?

    Thank you
    It may help us if we knew why you don't want to use the import code you currently have?
    Reply With QuoteRegister To Reply
  17. 03-23-2014, 11:04 PM #17
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Sir,

    I want to learn a new method that is the reason i am asking for a different code , other than the recorded macro.

    Regards,
    Zaska
    Reply With QuoteRegister To Reply
  18. 03-25-2014, 05:58 PM #18
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro tweaking and better way to import text file using VBA

    Hi Zaska,

    I put Jindon's code in a Do...Loop and I patched the results - I've got 23 fields with no label for the last field - anyway:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  19. 03-26-2014, 08:13 PM #19
    zaska
    zaska is offline
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    454

    Re: Macro tweaking and better way to import text file using VBA

    Thank you very much sir.
    Reply With QuoteRegister To Reply
  20. 03-26-2014, 08:29 PM #20
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro tweaking and better way to import text file using VBA

    You're welcome and thanks for the rep!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro tweaking help for save to PDF using a location prompt but file name from cell
    By cleol56 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2014, 01:01 PM
  2. Import several text files into an excel file with macro
    By rhkm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2013, 05:04 AM
  3. VBA / Macro Help -Adding file name as column to existing Import Multiple Text File Macro
    By NKRA in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-12-2012, 02:39 PM
  4. macro to import text file
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2008, 09:08 AM
  5. Macro Import Text File, File Name
    By Bob S in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2006, 06:01 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1