Pulling in .txt file via macro - NEED HELP "d22" <u48072[ at ]uwe> 12/9/2008 3:36:10 PM 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!!!

Re: Pulling in .txt file via macro - NEED HELP "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 12/10/2008 3:37:33 AM 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. -- Ken Snell <MS ACCESS MVP> http://www.accessmvp.com/KDSnell/ "d22" <u48072[ at ]uwe> wrote in message news:8e68e11029336[ at ]uwe... [Quoted Text] > 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!!! >

Re: Pulling in .txt file via macro - NEED HELP "d22 via AccessMonster.com" <u48072[ at ]uwe> 12/10/2008 3:15:29 PM 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 Ken Snell (MVP) wrote: [Quoted Text] >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!!! -- Message posted via http://www.accessmonster.com

Re: Pulling in .txt file via macro - NEED HELP "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 12/14/2008 7:10:53 PM 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/ "d22 via AccessMonster.com" <u48072[ at ]uwe> wrote in message news:8e754557529be[ at ]uwe... [Quoted Text] > 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 > > Ken Snell (MVP) wrote: >>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!!! > > -- > Message posted via http://www.accessmonster.com >