Import Text File Window Bypass

[tmwilkin]

I have a macro set up to refresh a delimited .txt file import. However, I
continue to receive the "Import Text File" prompt box and have to select the
file. I would like to bypass this prompt all together by specifying the name
of the file. The guts of the code are below:

With Selection.QueryTable
.Connection = _
"TEXT;J:\Office Records\1 Central Reports and Projects
Folder\Dashboard\Source_files\Presidential Sales violations.txt"
.Refresh BackgroundQuery:=False
End With

I've also tried the .Connection with "TEXT;" & "J:...." and come up with the
same issue - so there must be something I'm doing wrong in calling the file
name. Can someone assist me with this issue?

Thanks,
Todd

 

[Shane Devenshire]

Hi,

Why not dispense with the Import macro and create a live connection to the
text file, then when you want to refresh you can just choose Data, Refresh
Data. It takes a little time to set it up the first time, but after that
there's nothing to updating.

Here is the basic idea:

1. Choose Data, Import External Data, New Database Query
2. With New Data Source selected click OK, enter any name you want and open
the second drop-down and choose Microsoft Text Driver (*.txt, *.csv) and
click Connect.

The steps in here depend on where the file is located. But what you are
doing is finding and selecting the file.

6. Double-click each field you want or click the > while selecting the table
name, if you want all the fields.
7. Click Next. This screen allows you to filter the record being returned
before they reach Excel.
8. Click Next. This screen allows you to sort the data before returning it
to Excel, probably little point in doing this.
9. Click Next. Choose Return data to Microsoft Office Excel and click Finish.
10. Choose a location for the linked data and click OK.

To refresh the data either choose Data, Refresh Data or click the Refresh
All button if you have it displayed.