TransferText / Import

[magmike]

I've been using DoCmd.TransferText to import a prenamed text file,
however, would like to giv the user the ability to specify the file to
be imported, including the ability to select multiple files to import/
append to table at once. Is this possible?

Thanks in advance for your help!

magmike

 

[Tom van Stiphout]

On Sat, 1 May 2010 20:29:56 -0700 (PDT), magmike <[email protected]>
wrote:

Sure it is. Open a standard File/Open dialog to ask the user for the
file name, then use that value in DoCmd.TransferText. Sample code can
be found in many places, including:
http://www.tek-tips.com/faqs.cfm?fid=2484

-Tom.
Microsoft Access MVP

 

[magmike]

Sure it is. Open a standard File/Open dialog to ask the user for the
file name, then use that value in DoCmd.TransferText. Sample code can
be found in many places, including:http://www.tek-tips.com/faqs.cfm?fid=2484

-Tom.
Microsoft Access MVP

I seem to be having difficulty. Not only is the code not allowing the
user to select more than one file (for example, by holding the CTRL
button and clicking on multiple files), but it is also not passing the
file name to the TransferText line either. Please tell me what I have
done wrong.

I created the class module as the linked source suggested, named
clsCommonDialog and then here is my code under the button on my form:


Private Sub cmdImportAflac_Click()

Dim cmdlgOpenFile As New clsCommonDialog
Dim FileName As String 'full file name
Const clngFilterIndexAll = 5
cmdlgOpenFile.Filter = "Text Files (*.txt)|*.txt|DBF Files (DBF)|*.dbf|
All Files (*.*)|*.*"
cmdlgOpenFile.FilterIndex = clngFilterIndexAll
'this is where the dialog opens
cmdlgOpenFile.ShowOpen

'returns your full file name.
FileName = cmdlgOpenFile.FileName

'hence no len, no name...
If Len(FileName) = 0 Then Exit Sub

On Error Resume Next
Dim stSpec, stTable, stFileName, stMessage, stSQL As String
Dim lngResponse As Long
stSpec = "Aflac Import Spec"
stTable = "Aflac"
stFileName = FileName
stMessage = "You are about to import one or more aflac billing files
into the database. Before this can be done, the old bills must be
cleared out of the database. Is this okay?"
stSQL = "DELETE * from Aflac"

lngResponse = MsgBox(stMessage, vbOKCancel, "Warning!")
If lngResponse = 2 Then
Exit Sub
End If

DoCmd.RunSQL stSQL
DoCmd.TransferText acImportDelim, stSpec, stTable, stFileName
DoCmd.RepaintObject acForm, "Form!2010 Eligibility"
DoCmd.Requery

Me.cmdPreview.SetFocus
DoCmd.Requery
DoCmd.OpenTable stTable, acViewNormal
End Sub

any help is appreciated!

 

[Tom van Stiphout]

The file/open dialog certainly has the ability to allow you to select
more than one file. You need to dive in the details of the
documentation, and you will find the cdlOFNAllowMultiselect flag (see
same article). "Or" this together with the other flags and the option
is enabled. I'm sure you can find out how the multiple files are
returned.

-Tom.
Microsoft Access MVP

 

[Mark Andrews]

Here's some code that will help (just code snippets), hopefully you get the
idea and it helps.

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

-------------------------
'get single file or multiple files (by using ctrl click to select
multiple) and import them in tblImportDiscrepancy
strFiles = GetMultipleExcelFiles(CurrentDBDir, "Ctrl-Click for
Multi-Select")
astrFiles = Split(strFiles, vbNullChar)
If UBound(astrFiles) < LBound(astrFiles) Then
MsgBox "No files were selected"
ElseIf UBound(astrFiles) = LBound(astrFiles) Then
'MsgBox "One file was selected. File (with path) is: " &
astrFiles(LBound(astrFiles))
Call ImportASingleLogFile(astrFiles(LBound(astrFiles)))
Else
J = LBound(astrFiles)
For i = J + 1 To UBound(astrFiles)
Call ImportASingleLogFile(astrFiles(J) & "\" & astrFiles(i))
Next i
End If
-------------------------
Public Function GetMultipleExcelFiles(Optional varDirectory As Variant,
Optional varTitleForDialog As Variant) As Variant
' This function opens a filedialog for selecting one or multiple Excel files
' See TestIT for ways of calling this function

Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
Dim varFileList() As String

' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_HIDEREADONLY Or
ahtOFN_NOCHANGEDIR Or ahtOFN_ALLOWMULTISELECT Or ahtOFN_EXPLORER
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

'Note: if you need a different filter I recommend making another
function
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS*")

varFileName = ahtCommonFileOpenSave(OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)

If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If

GetMultipleExcelFiles = varFileName

End Function