Pulling in .txt file via macro - NEED HELP

[d22]

Fairly new to Access Macros; trying to do the following:

- create a macro that pulls in a .txt file (needs to be pipe delimited) -
entered Transfer Type "Link Delimited", but stumped as to what to put in for
Specification Name

- Then need to create a query that removes certain data (trading positions
from several books)

- Then need to export that query (with data removed) as .txt file back to the
share drive

Would like to do all that in one macro.

One problem that I foresee is that the folder where the file is stored keeps
changing every day (most of the filepath is the same, but the actual folder
changes with every close of business date, e.g., I:\Marketshare\Capital\
20081205, I:\Marketshare\Capital\20081208, etc.

Any help would be greatly appreciated!!!

 

[Ken Snell \(MVP\)]

You'll need to create an Import Specification in which you specify the
delimiter, and then use the name of that Import Specification as the second
argument of the TransferText method.

You create an Import Specification by manually beginning the import process.
When you're in the wizard window, select the ; character as the delimiter,
and set any other settings you want (field names, data types, etc.). Then,
before you click the Finish button, click the Advanced button at bottom
left. In the new window, you'll see all the settings you've specified. Click
Save As button and save them as an import specification (name it whatever
you want, e.g., "MyImportSpec"). Click OK button to return to the main
window of the wizard. Then click Cancel button to cancel the rest of the
import.

Now go to your TransferText macro/code and add the name of the import
specification to the appopriate argument position.

 

[d22 via AccessMonster.com]

That worked beautifully! Thanks a lot.

Now, I'd like to add a query that removes certain data - can I do it all in
one shot? Also, do I need open Access and run this macro or can I store it
elsewhere and run it from there? sorry if the questions seem simplistic, but
I am mostly a power Excel user.

Thanks again

You'll need to create an Import Specification in which you specify the
delimiter, and then use the name of that Import Specification as the second
argument of the TransferText method.

You create an Import Specification by manually beginning the import process.
When you're in the wizard window, select the ; character as the delimiter,
and set any other settings you want (field names, data types, etc.). Then,
before you click the Finish button, click the Advanced button at bottom
left. In the new window, you'll see all the settings you've specified. Click
Save As button and save them as an import specification (name it whatever
you want, e.g., "MyImportSpec"). Click OK button to return to the main
window of the wizard. Then click Cancel button to cancel the rest of the
import.

Now go to your TransferText macro/code and add the name of the import
specification to the appopriate argument position.

Fairly new to Access Macros; trying to do the following:

[quoted text clipped - 20 lines]

Any help would be greatly appreciated!!!

 

[Ken Snell \(MVP\)]

You cannot use a query to filter out data as you import the data from the
text file. What you should do is import all the data from the text file into
a "holding" table. Then use an append query to copy just the data you want
(filter out the data you don't want) from the "holding" table to your
"permanent" table. You can create the query and save it; then run it via
OpenQuery action in a macro.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

That worked beautifully! Thanks a lot.

Now, I'd like to add a query that removes certain data - can I do it all
in
one shot? Also, do I need open Access and run this macro or can I store it
elsewhere and run it from there? sorry if the questions seem simplistic,
but
I am mostly a power Excel user.

Thanks again

You'll need to create an Import Specification in which you specify the
delimiter, and then use the name of that Import Specification as the
second
argument of the TransferText method.

You create an Import Specification by manually beginning the import
process.
When you're in the wizard window, select the ; character as the delimiter,
and set any other settings you want (field names, data types, etc.). Then,
before you click the Finish button, click the Advanced button at bottom
left. In the new window, you'll see all the settings you've specified.
Click
Save As button and save them as an import specification (name it whatever
you want, e.g., "MyImportSpec"). Click OK button to return to the main
window of the wizard. Then click Cancel button to cancel the rest of the
import.

Now go to your TransferText macro/code and add the name of the import
specification to the appopriate argument position.

Fairly new to Access Macros; trying to do the following:

[quoted text clipped - 20 lines]

Any help would be greatly appreciated!!!