+ Reply to Thread
Results 1 to 5 of 5

Help importing a non-delimited text file into excel via vba

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    USA
    MS-Off Ver
    2019
    Posts
    3

    Help importing a non-delimited text file into excel via vba

    Greetings all,

    This is my first post here as I can't figure out how to accomplish this task. I was given the job of fixing an old employees code that imported a non-delimited text file via vba from a network share drive. Attached below is a small snippet of it for reference. I just want to import the file into a sheet so that it separates the fields so that I can access the data in other sheets via functions. I currently get an error at the .Refresh BackgroundQuery:=False and have no idea what is causing the issue. If anyone can give me the proper way to import the file so that it is usable I would greatly appreciate it!

    TEXT FILE EXAMPLE:
    Please Login or Register  to view this content.
    MY CODE:
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 10-23-2019 at 03:20 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Help importing a non-delimited text file into excel via vba

    Hello mlindsay1990,

    Here is the description of the Refresh method for a QueryTable object:
    Used only with QueryTables that are based on the results of a SQL query. True to return control to the procedure as soon as a database connection is made and the query is submitted. The QueryTable is updated in the background. False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn't specified, the setting of the BackgroundQuery property determines the query mode.
    Remove Refresh from your code because you are not using SQL.
    Please Login or Register  to view this content.
    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!)

  3. #3
    Registered User
    Join Date
    10-23-2019
    Location
    USA
    MS-Off Ver
    2019
    Posts
    3

    Re: Help importing a non-delimited text file into excel via vba

    Okay, so I did remove the refresh background query. But, now I'm having issues with it not working at all now. I run it and it seems to select each page but not insert any of the data. I didn't change anything else and when I tried putting it back in it still doesn't work. I feel like there is probably a way that is more correct to do this, am I right? Or is using the QueryTables the correct method to import a non-delimited text file?

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow

    Hi !

    Using a QueryTable is one of the methods. You can even try manually from the Data 'menu' via From a text file
    Once you well respond to the Import Assistant and you get the expected result,
    activate the Macro Recorder and redo the same operations : you will get your own code base in order to amend the original code.

    For further help, attach a source text file

  5. #5
    Registered User
    Join Date
    10-23-2019
    Location
    USA
    MS-Off Ver
    2019
    Posts
    3

    Re: Help importing a non-delimited text file into excel via vba

    I think I got it, thank you for your help! It was removing that .Refresh BackgroundQuery:=False from the code that caused some additional issues. The following code performs what I needed correctly.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Importing a delimited text file with different data types into an array
    By ifthenifthen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-20-2018, 06:43 PM
  2. Importing non-delimited text file into excel
    By Miteshkg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2016, 04:48 AM
  3. Importing txt space delimited text file
    By huytran102 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-16-2014, 04:13 PM
  4. Importing Delimited Text File
    By JHCross in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2011, 06:01 PM
  5. Importing Delimited Text file
    By mmb.kala in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2010, 02:45 AM
  6. Importing space delimited text file
    By EDIEC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2008, 03:44 PM
  7. Excel 2003 Importing large delimited text file
    By Ron in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-05-2005, 11:06 AM

Tags for this Thread

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