Importing Text in MS Access


I'm trying to develope a MS Access 97 program that imports data from a text file.  I need to the users to select the file from their local drive, but I want to automate importing the data into my table.

I've tried to use both a Macro (Runcommand - Import) and coding (Docmd - TransferText) to do this but both solutions are either too Manually Intensive (user has to navigate through the Import Wizard) or Automated (user can't select the file - the name and location have to be hard coded).

Does anyone have a predefined Form that looks like the "Import" window (a tree view of the local drive along with all the rest of the objects on that specific window) so the user can select the file and directory and then I can assign those values to a variable.  That way I can run the Docmd - TransferText code to automate the rest of the import data process.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


If you use the Windows API function OpenFile, or SaveFile, (both of which are complicated), the user can select files just as all apps permit, and you can get the filename, path, and whether they clicked cancel, plus you can put your own title on the dialog.

Create a module, and paste the following code into it.

(I would name it 'APIs')

Option Compare Database
Option Explicit

Type typeOpenFileName
    lStructSize     As Long
    hwndOwner       As Long
    hInstance       As Long
    strFilter       As String
    strCustomFilter As String
    nMaxCustFilter  As Long
    nFilterIndex    As Long
    strFile         As String
    nMaxFile        As Long
    strFileTitle    As String
    nMaxFileTitle   As Long
    strInitialDir   As String
    strTitle        As String
    Flags           As Long
    nFileOffset     As Integer
    nFileExtension  As Integer
    strDefExt       As String
    lCustData       As Long
    lpfnHook        As Long
    lpTemplateName  As String
End Type

Declare Function apiGetOpenFileName Lib "comdlg32.dll" _
             Alias "GetOpenFileNameA" (strcOFN As typeOpenFileName) As Boolean

Declare Function apiGetSaveFileName Lib "comdlg32.dll" _
             Alias "GetSaveFileNameA" (strcOFN As typeOpenFileName) As Boolean

Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ReadOnly = &H1
Global Const NoReadOnlyReturn = &H8000&
Global Const OverwritePrompt = &H2
Global Const HideReadonlyBox = &H4
Global Const NoChangeDir = &H8
Global Const ShowHelpBtn = &H10
Global Const EnableHook = &H20
Global Const EnableTemplate = &H40
Global Const EnableTemplateHandle = &H80
Global Const NoValidate = &H100
Global Const AllowMultiSelect = &H200
Global Const ExtensionDifferent = &H400
Global Const PathMustExist = &H800
Global Const FileMustExist = &H1000
Global Const CreatePrompt = &H2000
Global Const ShareAware = &H4000
Global Const NoTestFileCreate = &H10000
Global Const NoNetworkButton = &H20000
Global Const NoLongNames = &H40000

' New for Windows 95
Global Const OFN_Explorer = &H80000
Global Const NoDereferenceLinks = &H100000
Global Const LongNames = &H200000

Function CommonFileOpenSave( _
                            Optional ByRef Flags As Variant, _
                            Optional ByVal InitialDir As Variant, _
                            Optional ByVal Filter As Variant, _
                            Optional ByVal FilterIndex As Variant, _
                            Optional ByVal DefaultExt As Variant, _
                            Optional ByVal FileName As Variant, _
                            Optional ByVal DialogTitle As Variant, _
                            Optional ByVal hWnd As Variant, _
                            Optional ByVal OpenFile As Variant _
                           ) As Variant
' This is the entry point to call the common File Open/Save dlg.
' The parameters listed below are all optional.
' In:
'   Flags:          1 or more of the constants, Or'd together. ('+' works equally well)
'   InitialDir:     Directory in which to first look
'   Filter:         A set of file filters, set up by calling
'   AddFilterItem.  See examples.
'   FilterIndex:    1-based integer indicating which filter set to use, by default
'                   (1 if unspecified)
'   DefaultExt:     Extension to use if the user doesn't enter one.  Only useful on file saves.
'   FileName:       Default value for the file name text box.
'   DialogTitle:    Title for the dialog.
'   hWnd:           Parent window handle
'   OpenFile:       Boolean(True: "Open File" dlg;  False: "Save As" dlg)
' Out:
'   Return Value:   Either Null or the selected filename

    Dim strcOFN      As typeOpenFileName
    Dim strFileName  As String
    Dim strFileTitle As String
    Dim fResult      As Boolean

    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(Flags) Then Flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hWnd) Then hWnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    ' Set up the data structure before you call the function
    With strcOFN
        .lStructSize = Len(strcOFN)
        .hwndOwner = hWnd
        .strFilter = Filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .Flags = Flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        .hInstance = 0
        .strCustomFilter = ""
        .nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With

    ' This'll pass the data structure to the Windows API, which will
    ' use it to display the Open/Save As Dialog.
    If OpenFile Then
        fResult = apiGetOpenFileName(strcOFN)
        fResult = apiGetSaveFileName(strcOFN)
    End If

    ' The function call filled in the strFileTitle member of the structure.
    ' You'll have to write special code to retrieve that if you're interested.
    If fResult Then
        ' You might want to check the Flags member of the structure to
        ' get info about the chosen file.
        ' In this example, if you passed in a value for Flags,
        ' we'll fill it in with the outgoing Flags value.
        If Not IsMissing(Flags) Then Flags = strcOFN.Flags
        CommonFileOpenSave = TrimNull(strcOFN.strFile)
        CommonFileOpenSave = ""
    End If

End Function

Function AddFilterItem(strFilter As String, _
                       strDescription As String, _
                       Optional varItem As Variant _
                      ) As String

' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description, (like "Databases"),
' a null character, the skeleton (like "*.mdb;*.mda") and a final null character.

    If IsMissing(varItem) Then varItem = "*.*"
    AddFilterItem = strFilter & _
                    strDescription & _
                    vbNullChar & _
                    varItem & _
End Function

' To call the dlg from code, see TestIt() within this module or use the fol. ex. as a guide.
Sub HoldText()

    Dim strFilter        As String
    Dim strInputFileName As String
    Dim strSaveFileName  As String
    strFilter = AddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    strInputFileName = CommonFileOpenSave(Filter:=strFilter, _
                                          OpenFile:=True, _
                                          DialogTitle:="Please select an input file...", _
                                          Flags:=HideReadonlyBox _

' Note:  To call the Save As dlg, you can use the same wrapper function by just setting
' the OpenFile option as False. For example,

    'Ask for SaveFileName
    strFilter = AddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strSaveFileName = CommonFileOpenSave(OpenFile:=False, Filter:=strFilter, _
                        Flags:=OverwritePrompt Or ReadOnly)

End Sub

The file "MSText35.DLL" (at "C:\Windows\System") probably not installing correctly in your computer.
Run the command "RegSvr32 C:\Windows\System\MSText35.Dll" and evrytrhing should work just fine.

Good luck,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.