Macro to import date from text file

  • Thread starter Thread starter Jasper Recto
  • Start date Start date
J

Jasper Recto

I need to import data from a text file.

I would like to create a macro that would prompt the user for the file and
then import it into an existing database.

How would I tackle this?

Thanks,
Jasper
 
I need to import data from a text file.

I would like to create a macro that would prompt the user for the file and
then import it into an existing database.

How would I tackle this?

Thanks,
Jasper
Click to expand...

Take a look at the online help for TransferText.
 
I took this code from some Microsoft web. This will help you to obtain the
file (the user will select) because Access doesn't have the "common dialog"
object to browse and select files.

Put this in a Form, below declarations:
Option Compare Database

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
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

Private Function LaunchCD(strform As Form) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = strform.Hwnd

sFilter = "Text Files (*.txt)" & Chr(0) & "*.txt"

With OpenFile
.lpstrFilter = sFilter
.nFilterIndex = 1
.lpstrFile = String(257, 0)
.nMaxFile = Len(.lpstrFile) - 1
.lpstrFileTitle = .lpstrFile
.nMaxFileTitle = .nMaxFile
.lpstrTitle = "Select text file to import"
.flags = 0
End With

lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "No file selected!", vbInformation, _
"Importing Fail"
LaunchCD = ""
Else
LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1,
OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function

' Trigger this function in the form with an "Import File" button, like this
Private Sub ImportFile_Click()
Dim Path As String
Path = LaunchCD(Me)

If Path = "" Then Exit Sub

'Code to import to the current dbase and name the table as the source
file (Document)
DoCmd.TransferText acImportDelim, , "ImportedTxT", Path, True

'acImportDelim = delimited width (or you can change it for acImportFixed
for fixed width)
'True= if the first row in the text file has field names (false if it
hasn't)

End Sub


I hope it helps

Rastro

Ps: Search for "TransferText" method in VBA Access Help to find out more
options to import the files
 
Rastro,

This almost works great. Just one question.

For the TransferTetxt command, how do I tell it to use a " ~' as the
delimiter and how can I assign the format for each field?

Thanks,
Jasper
 
Rastro,

How would I add an import specification to the command line?

Thanks,
Jasper
 
You can't.
This takes Access default's settings for list separators. But you can change
this settings with code, I think. The trick will be to change it, do the
import, and reverse the changed setting.
Let me find out how.

Rastro
 
You mean to set in the dialog a default directory location?
(I'm sorry but English isn't my mother tongue)
If it yes, paste this sentence inside the "With OpenFile" section on
LaunchCD function:

.lpstrInitialDir = "C:\YourDirectory"
 
Hi,

I have tried this macro but it as a debug error in the function LaunchCD

..lpstrFilter = sFilter

It seems that my Access 2003 (Danish) dosen't like the sFilter string:

sFilter = "Text Files (*.txt)" & Chr(0) & "*.txt"

When I try to use only "Text Files" it didn't help...

As I can see from the macro, sFilter is the 'Filetype' - Is that correct??
 
Back
Top