how to _ import text-data into Access???

[mooneater]

Hello,
I found here in an newsgroup entry following...

>-----------------------------------------------

HESCott (Programmer) Apr 10, 2003

I have some code that you can drop into a module (which I found right here on Tek-Tips) that allows you to do this. (I wish I could find the exact post that I copied this from so I could give proper credit - I have done several searches, but I cannot find the specific post or FAQ.)

In Access, open a Module, New. Then paste in this code:


'*********************** BEGIN COPY ************************
'the open filename api
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As gFILE) As Long

' the gFILE type needed by the open filename api
Type gFILE
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String

End Type


Function FileToOpen(Optional StartLookIn) As String
'Purpose: Calls the open file api to let the user select the file to open
'returns: string value which contains the path to the file selected. "" = no file seleted

Dim ofn As gFILE
Dim path As String
Dim filename As String
Dim a As String

StartOver:
ofn.lStructSize = Len(ofn)
ofn.lpstrFilter = "Text Files (*.csv)" _
+ Chr$(0) + "*.csv" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
ofn.lpstrFile = Space$(254)
ofn.nMaxFile = 255
ofn.lpstrFileTitle = Space$(254)
ofn.nMaxFileTitle = 255

If Not IsMissing(StartLookIn) Then
ofn.lpstrInitialDir = StartLookIn
Else
ofn.lpstrInitialDir = "c:\some default directory"
End If

ofn.lpstrTitle = "Please find and select the document to open"
ofn.flags = 0

a = GetOpenFileName(ofn)
If (a) Then
path = Trim(ofn.lpstrFile)
filename = Trim(ofn.lpstrFileTitle)
If Dir(path) <> &quot;&quot; Then FileToOpen = -1
FileToOpen = Trim(ofn.lpstrFile)
Else
FileToOpen = &quot;&quot;
path = &quot;&quot;
filename = &quot;&quot;
End If

FileToOpen = path

End Function

'*********************** END COPY *************************



Once this module has been saved, you can put this code behind the button click:


Dim filename As String
filename = FileToOpen()

If (Len(filename)) Then
DoCmd.Hourglass True
DoCmd.TransferSpreadheet (w/your parameters)
DoCmd.Hourglass False
End If

-----------------------------------------------<

Myproblem is now ... I seem not to be able to get the second code beneath a button to get the import started.

I have txt-data-files with &quot;|&quot; als delimiter and have fixed tables that already exist.

****Question****

... Can anyone give me an example of a correct TransferText codeline???
It seems i am to stupid .. just errors.
(the first column of the text-data-files are row/fieldnames)

And I would need a litle explaination how to put the smaller code beneath a button (with the RunCode macro i just get errors &quot;The object does'nt contain the Automation object 'Dim'&quot

Thnks for reading,
Nils