Import txt file macro

[access2007_newbie]

Hello,

I am currently using a transfer text macro to import a text file. This works
great if the name of the text file is always the same. Is there a similar
marco (or any other suggestion) that will allow me to choose the text file to
be imported? This text files always contain the same information, just
different names. I am currently using Access 2007.

Thanks for all your help.
Kevin

 

[Tom van Stiphout]

On Tue, 18 May 2010 07:08:01 -0700, access2007_newbie

You'll have to use some VBA code. The typical scenario is where you
respond to an "Import Text Bile" button click by opening a file/open
dialog (see http://www.mvps.org/access/api/api0001.htm) and take the
return value (the file to be opened) and pass it to
DoCmd.TransferText.

-Tom.
Microsoft Access MVP

 

[access2007_newbie]

Tom,

First, YOU ARE THE MAN! That code works great! However, I am a VB virgin.
I'm having a little trouble inserting the transfer txt function (see below)
that includes my import parameters. Any suggestions in the code to insert
this?

'------------------------------------------------------------
' Proc_1_macro_Run_Process
'
'------------------------------------------------------------
Function Proc_1_macro_Run_Process()
On Error GoTo Proc_1_macro_Run_Process_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "01 delete KBASE Table", acViewNormal, acEdit
DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "KBASE Import Specification",
"KBASE", "C:\Documents and Settings\williamsonk\My Documents\KBase
FTP\447351.txt", False, ""


Proc_1_macro_Run_Process_Exit:
Exit Function

Proc_1_macro_Run_Process_Err:
MsgBox Error$
Resume Proc_1_macro_Run_Process_Exit

End Function

Thanks for all your help,
Kevin (A grateful Access user).

 

[Tom van Stiphout]

On Tue, 18 May 2010 09:46:03 -0700, access2007_newbie

The code from the article goes in a standard module.
Then in your Run_Process function you write:
DoCmd.TransferText acImportDelim, "KBASE Import Specification",
"KBASE", GetOpenFile(), False, ""

If you look in the GetOpenFile function, you will see that it is setup
to look for mdb files. Of course you can change that line.

-Tom.
Microsoft Access MVP

 

[access2007_newbie]

Tom,

Thanks for all your help. I got it to work exactly like I wanted.

Thanks again,
Kevin