Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Help (Access 2000 SR1)

    Can anyone assist with the following? I have a text file which is an export of Active Directory information. The ability to format the export is limited and produces the results similar to the example below. (See attached file for actual example.)

    Members of local group [sharename-1subdirectory-1]:

    John.Smith
    Jane.Smith
    John.Black

    Members of local group [sharename-1subdirectory-2]:

    Jane.Doe
    John.Doe

    I need to import this into a table in Access in the following format:

    Field1: ShareName
    Field2: UserName


    Does anyone know if this is possible to achieve?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Help (Access 2000 SR1)

    The following VBA routine uses DAO, so you must select Tools| References... in the Visual Basic Editor, and make sure that the reference to the Microsoft DAO 3.6 Object Library is ticked, then click OK.

    The code assumes that you have imported or linked the text file as a table named MemberList, with one text field named Field1. It also assumes that you have already created an empty table tblMemberList with two text fields Field1 and Field2 of sufficient length to hold the longest sharename and username.

    Insert a new module and copy the following module into it. Adapt the names as needed, then run the code by clicking somewhere in the procedure and pressing F5.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub ImportText()
    ' Declare variables
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim strPath As String
    Dim intPosLeft As Integer
    Dim intposRight As Integer

    ' Set error handling
    On Error GoTo ErrHandler

    ' Assign object variables
    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset("MemberList", dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("tblMemberList", dbOpenDynaset)

    ' Loop through the lines of the text file
    Do While Not rstIn.EOF
    ' Do we have a new sharename?
    If Left(rstIn!Field1, 10) = "Members of" Then
    ' Extract sharename
    intPosLeft = InStr(rstIn!Field1, "[")
    intposRight = InStr(rstIn!Field1, "]")
    strPath = Mid(rstIn!Field1, intPosLeft, intposRight - intPosLeft + 1)
    ElseIf Not (Trim(rstIn!Field1) = "") Then
    ' Otherwise, if not on an empty line, add a new record
    rstOut.AddNew
    rstOut!Field1 = strPath
    rstOut!Field2 = Trim(rstIn!Field1)
    rstOut.Update
    End If
    ' Move on
    rstIn.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rstOut.Close
    rstIn.Close
    Set rstOut = Nothing
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Help (Access 2000 SR1)

    Your great.... the code works PERFECTLY .... thank you very much for the quick response!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •