VBA - Import *.txt to Access

[Guest]

Hi

I want to import a *.txt file on a monthly basis by means of macro which

1) opens 'Get External Data/Import' menu for *.txt files in directory: 'c:/my documents/" (enabling user to choose file

2) checks a date or item within the *.txt file (say column 1, row 2 - row 1 has column headings) and checks with the access table that it hasn't prevoiusly been imported

3) imports the *.txt file

How do i do this?

Thanks
Marcus

 

[Joe Fallon]

Here are some ideas:
============================

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With
============================
How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub


--
Joe Fallon
Access MVP

Hi,

I want to import a *.txt file on a monthly basis by means of macro which:

1) opens 'Get External Data/Import' menu for *.txt files in directory:

'c:/my documents/" (enabling user to choose file)

2) checks a date or item within the *.txt file (say column 1, row 2 - row

1 has column headings) and checks with the access table that it hasn't
prevoiusly been imported.

 

[Allen Browne]

Browsing for a folder requires an API call.
Details:
http://www.mvps.org/access/api/api0002.htm

Dir$() gets the files in a folder. Example:
strFile = Dir$("*.txt")
Do While Len(strFile) > 0
Debug.Print strFile
strFile = Dir$
Loop

TransferText imports the text file into a table.
Import into a temporary table, so you can run your validation, e.g. import
has not been done, foreign keys are correct, required fields have values,
zero-length strings are converted to Null, data types are correct, duplicate
indexes are not violated, etc. An import spec may also help.

Finally, Execute an Append query statement to append the data to the real
table, and a Delete query statement to clear out the temp table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to import a *.txt file on a monthly basis by means of macro which:

1) opens 'Get External Data/Import' menu for *.txt files in directory:

'c:/my documents/" (enabling user to choose file)

2) checks a date or item within the *.txt file (say column 1, row 2 - row

1 has column headings) and checks with the access table that it hasn't
prevoiusly been imported.