Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: Macro rewrite - Import Text File

    We received a file from a consultant that they need us to refresh daily. It contains a macro that allows the file to update based on some source data that is stored in a TEXT file.

    The macro code is as follows

    Selection.QueryTable.Refresh BackgroundQuery:=False

    When it is run, Excel opens an Import Text File Prompt, which we then need to point at the directory where AP_AR.txt exists.

    Rather then have to do this, isn't there a much easier way to code this so that when it is run the VBA already has the path and just uses those paths to update. There are a total of 10 sheets in the Macro, so to have it automated would be very helpful.

    Thank you in advance.

  2. #2
    Join Date
    Feb 2004
    ADO Import works very well for this. You will need to create a reference to the "MS Active X 2.x" Dll in Tools References. You will also need to write code to run Text to Columns after importing the text. This requires custom Excel VB to run.

    Here are some examples. You can find more by searching for 'Excel ADO Text Import' in any flavor search engine.

    Importing more than 65,536 rows into Excel

    (the second link didn't work when I just tried it. I've used this before so it may just be temporary)


  3. #3
    Join Date
    Mar 2004
    Thanks, I will take a crack with that.

Posting Permissions

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