Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

automate open text file

Discussion in 'Information Technology' started by RB Smissaert, Jul 28, 2006.

  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Want to automate this simple sequence from Excel VBA:
    Start Access, but no need to be visible.
    File
    Open text file.
    Comma delimited, first row are field names.
    Save as .mdb in specified folder.

    Seems simple, but I can't see anything that does it.
    I understand that unfortunately Access doesn't have the option to record a
    macro.

    Thanks for any advice.

    RBS
     
  2. Loading...


  3. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Looks this is heading the right direction:

    Sub Text2Access(strFile As String)

    Dim oAccess As Object
    Const acImportDelim As Long = 0

    Set oAccess = CreateObject("Access.Application")

    With oAccess
    .DoCmd.TransferText acImportDelim, , _
    "test", _
    "C:\TestFile.txt", True
    End With

    End Sub

    Application.DoCmd.TransferText acImportDelim, , _
    "test", _
    "C:\TestFile.txt", True

    works in Access, but it doesn't in Excel. There is no error and something is
    happening though.
    If it is indeed opening the text file in Access how do I save it as a .mdb
    file?
    Thanks for any advice.


    RBS



    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl...
    > Want to automate this simple sequence from Excel VBA:
    > Start Access, but no need to be visible.
    > File
    > Open text file.
    > Comma delimited, first row are field names.
    > Save as .mdb in specified folder.
    >
    > Seems simple, but I can't see anything that does it.
    > I understand that unfortunately Access doesn't have the option to record a
    > macro.
    >
    > Thanks for any advice.
    >
    > RBS
     
  4. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl
    > Want to automate this simple sequence from Excel VBA:
    > Start Access, but no need to be visible.
    > File
    > Open text file.
    > Comma delimited, first row are field names.
    > Save as .mdb in specified folder.
    >
    > Seems simple, but I can't see anything that does it.
    > I understand that unfortunately Access doesn't have the option to
    > record a macro.
    >
    > Thanks for any advice.
    >
    > RBS


    Try something like this:

    '----- start of example code -----
    Dim strTextFilePath As String
    Dim strMDBPath As String
    Dim strTableName As String
    Dim I As Integer

    Dim appAccess As Object

    '*** Here, I've hard-coded the name\path of the file to be imported.
    strTextFilePath = "C:\Temp\TestImport.csv"

    strTableName = Dir(strTextFilePath) ' initially

    ' Allow for possible file name without extension.
    I = InStrRev(strTextFilePath, ".")
    If I = 0 Then
    strMDBPath = strTextFilePath & ".mdb"
    Else
    strMDBPath = Left(strTextFilePath, I - 1) & ".mdb"
    strTableName = _
    Left(strTableName, InStrRev(strTableName, ".") - 1)
    End If

    Set appAccess = CreateObject("Access.Application")

    With appAccess
    .NewCurrentDatabase strMDBPath
    .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    .CloseCurrentDatabase
    .Quit
    End With

    Set appAccess = Nothing
    '----- start of example code -----

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  5. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Thanks.
    I simplified it a bit as I don't need the path checking etc:

    Sub Text2Access(strTextFilePath As String, _
    strMDBPath As String, _
    strTableName)

    Dim appAccess As Object

    Set appAccess = CreateObject("Access.Application")

    With appAccess
    .NewCurrentDatabase strMDBPath
    .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    .CloseCurrentDatabase
    .Quit
    End With

    Set appAccess = Nothing

    End Sub

    Sub tester()

    Dim strTextFile As String
    Dim strMDBFile As String
    Dim strTable As String

    strTextFile = "C:\TempTables\ResultFile.txt"
    strMDBFile = "C:\TempTables\ResultFile.mdb"
    strTable = "TestTable"

    Text2Access strTextFile, strMDBFile, strTable

    End Sub

    It worked first time, but second time nil happened, although no error.
    Any suggestions?


    RBS



    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:uxjIFGgeGHA.2068@TK2MSFTNGP02.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl
    >> Want to automate this simple sequence from Excel VBA:
    >> Start Access, but no need to be visible.
    >> File
    >> Open text file.
    >> Comma delimited, first row are field names.
    >> Save as .mdb in specified folder.
    >>
    >> Seems simple, but I can't see anything that does it.
    >> I understand that unfortunately Access doesn't have the option to
    >> record a macro.
    >>
    >> Thanks for any advice.
    >>
    >> RBS

    >
    > Try something like this:
    >
    > '----- start of example code -----
    > Dim strTextFilePath As String
    > Dim strMDBPath As String
    > Dim strTableName As String
    > Dim I As Integer
    >
    > Dim appAccess As Object
    >
    > '*** Here, I've hard-coded the name\path of the file to be imported.
    > strTextFilePath = "C:\Temp\TestImport.csv"
    >
    > strTableName = Dir(strTextFilePath) ' initially
    >
    > ' Allow for possible file name without extension.
    > I = InStrRev(strTextFilePath, ".")
    > If I = 0 Then
    > strMDBPath = strTextFilePath & ".mdb"
    > Else
    > strMDBPath = Left(strTextFilePath, I - 1) & ".mdb"
    > strTableName = _
    > Left(strTableName, InStrRev(strTableName, ".") - 1)
    > End If
    >
    > Set appAccess = CreateObject("Access.Application")
    >
    > With appAccess
    > .NewCurrentDatabase strMDBPath
    > .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    > .CloseCurrentDatabase
    > .Quit
    > End With
    >
    > Set appAccess = Nothing
    > '----- start of example code -----
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  6. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    OK, works fine now now, thanks again.
    Noticed that if I do this manually by starting Access I get a linked table,
    so the .mdb file is much smaller.
    When I do it in VBA I get the full data and file is much bigger.
    How would I get the linked table via VBA?

    RBS

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:e7uL8XgeGHA.764@TK2MSFTNGP05.phx.gbl...
    > Thanks.
    > I simplified it a bit as I don't need the path checking etc:
    >
    > Sub Text2Access(strTextFilePath As String, _
    > strMDBPath As String, _
    > strTableName)
    >
    > Dim appAccess As Object
    >
    > Set appAccess = CreateObject("Access.Application")
    >
    > With appAccess
    > .NewCurrentDatabase strMDBPath
    > .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    > .CloseCurrentDatabase
    > .Quit
    > End With
    >
    > Set appAccess = Nothing
    >
    > End Sub
    >
    > Sub tester()
    >
    > Dim strTextFile As String
    > Dim strMDBFile As String
    > Dim strTable As String
    >
    > strTextFile = "C:\TempTables\ResultFile.txt"
    > strMDBFile = "C:\TempTables\ResultFile.mdb"
    > strTable = "TestTable"
    >
    > Text2Access strTextFile, strMDBFile, strTable
    >
    > End Sub
    >
    > It worked first time, but second time nil happened, although no error.
    > Any suggestions?
    >
    >
    > RBS
    >
    >
    >
    > "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    > news:uxjIFGgeGHA.2068@TK2MSFTNGP02.phx.gbl...
    >> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl
    >>> Want to automate this simple sequence from Excel VBA:
    >>> Start Access, but no need to be visible.
    >>> File
    >>> Open text file.
    >>> Comma delimited, first row are field names.
    >>> Save as .mdb in specified folder.
    >>>
    >>> Seems simple, but I can't see anything that does it.
    >>> I understand that unfortunately Access doesn't have the option to
    >>> record a macro.
    >>>
    >>> Thanks for any advice.
    >>>
    >>> RBS

    >>
    >> Try something like this:
    >>
    >> '----- start of example code -----
    >> Dim strTextFilePath As String
    >> Dim strMDBPath As String
    >> Dim strTableName As String
    >> Dim I As Integer
    >>
    >> Dim appAccess As Object
    >>
    >> '*** Here, I've hard-coded the name\path of the file to be imported.
    >> strTextFilePath = "C:\Temp\TestImport.csv"
    >>
    >> strTableName = Dir(strTextFilePath) ' initially
    >>
    >> ' Allow for possible file name without extension.
    >> I = InStrRev(strTextFilePath, ".")
    >> If I = 0 Then
    >> strMDBPath = strTextFilePath & ".mdb"
    >> Else
    >> strMDBPath = Left(strTextFilePath, I - 1) & ".mdb"
    >> strTableName = _
    >> Left(strTableName, InStrRev(strTableName, ".") - 1)
    >> End If
    >>
    >> Set appAccess = CreateObject("Access.Application")
    >>
    >> With appAccess
    >> .NewCurrentDatabase strMDBPath
    >> .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    >> .CloseCurrentDatabase
    >> .Quit
    >> End With
    >>
    >> Set appAccess = Nothing
    >> '----- start of example code -----
    >>
    >> --
    >> Dirk Goldgar, MS Access MVP
    >> www.datagnostics.com
    >>
    >> (please reply to the newsgroup)
    >>
    >>

    >
     
  7. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Aah, can see now, need acLinkDelim (5).

    RBS

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:u9PCRggeGHA.5016@TK2MSFTNGP04.phx.gbl...
    > OK, works fine now now, thanks again.
    > Noticed that if I do this manually by starting Access I get a linked
    > table, so the .mdb file is much smaller.
    > When I do it in VBA I get the full data and file is much bigger.
    > How would I get the linked table via VBA?
    >
    > RBS
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:e7uL8XgeGHA.764@TK2MSFTNGP05.phx.gbl...
    >> Thanks.
    >> I simplified it a bit as I don't need the path checking etc:
    >>
    >> Sub Text2Access(strTextFilePath As String, _
    >> strMDBPath As String, _
    >> strTableName)
    >>
    >> Dim appAccess As Object
    >>
    >> Set appAccess = CreateObject("Access.Application")
    >>
    >> With appAccess
    >> .NewCurrentDatabase strMDBPath
    >> .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    >> .CloseCurrentDatabase
    >> .Quit
    >> End With
    >>
    >> Set appAccess = Nothing
    >>
    >> End Sub
    >>
    >> Sub tester()
    >>
    >> Dim strTextFile As String
    >> Dim strMDBFile As String
    >> Dim strTable As String
    >>
    >> strTextFile = "C:\TempTables\ResultFile.txt"
    >> strMDBFile = "C:\TempTables\ResultFile.mdb"
    >> strTable = "TestTable"
    >>
    >> Text2Access strTextFile, strMDBFile, strTable
    >>
    >> End Sub
    >>
    >> It worked first time, but second time nil happened, although no error.
    >> Any suggestions?
    >>
    >>
    >> RBS
    >>
    >>
    >>
    >> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    >> news:uxjIFGgeGHA.2068@TK2MSFTNGP02.phx.gbl...
    >>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl
    >>>> Want to automate this simple sequence from Excel VBA:
    >>>> Start Access, but no need to be visible.
    >>>> File
    >>>> Open text file.
    >>>> Comma delimited, first row are field names.
    >>>> Save as .mdb in specified folder.
    >>>>
    >>>> Seems simple, but I can't see anything that does it.
    >>>> I understand that unfortunately Access doesn't have the option to
    >>>> record a macro.
    >>>>
    >>>> Thanks for any advice.
    >>>>
    >>>> RBS
    >>>
    >>> Try something like this:
    >>>
    >>> '----- start of example code -----
    >>> Dim strTextFilePath As String
    >>> Dim strMDBPath As String
    >>> Dim strTableName As String
    >>> Dim I As Integer
    >>>
    >>> Dim appAccess As Object
    >>>
    >>> '*** Here, I've hard-coded the name\path of the file to be imported.
    >>> strTextFilePath = "C:\Temp\TestImport.csv"
    >>>
    >>> strTableName = Dir(strTextFilePath) ' initially
    >>>
    >>> ' Allow for possible file name without extension.
    >>> I = InStrRev(strTextFilePath, ".")
    >>> If I = 0 Then
    >>> strMDBPath = strTextFilePath & ".mdb"
    >>> Else
    >>> strMDBPath = Left(strTextFilePath, I - 1) & ".mdb"
    >>> strTableName = _
    >>> Left(strTableName, InStrRev(strTableName, ".") - 1)
    >>> End If
    >>>
    >>> Set appAccess = CreateObject("Access.Application")
    >>>
    >>> With appAccess
    >>> .NewCurrentDatabase strMDBPath
    >>> .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    >>> .CloseCurrentDatabase
    >>> .Quit
    >>> End With
    >>>
    >>> Set appAccess = Nothing
    >>> '----- start of example code -----
    >>>
    >>> --
    >>> Dirk Goldgar, MS Access MVP
    >>> www.datagnostics.com
    >>>
    >>> (please reply to the newsgroup)
    >>>
    >>>

    >>

    >
     
  8. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    All working beautifully now:

    Public Function FileFromPath(ByVal strFullPath As String, _
    Optional bExtensionOff As Boolean = False) _
    As String

    Dim FPL As Long 'len of full path
    Dim PLS As Long 'position of last slash
    Dim pd As Long 'position of dot before exension
    Dim strFile As String

    On Error GoTo ERROROUT

    FPL = Len(strFullPath)
    PLS = InStrRev(strFullPath, "\", , vbBinaryCompare)
    strFile = Right$(strFullPath, FPL - PLS)

    If bExtensionOff = False Then
    FileFromPath = strFile
    Else
    pd = InStr(1, strFile, ".", vbBinaryCompare)
    FileFromPath = Left$(strFile, pd - 1)
    End If

    Exit Function
    ERROROUT:

    On Error GoTo 0
    FileFromPath = ""

    End Function

    Sub Text2Access(strTextFile As String, _
    Optional strMDBPath As String, _
    Optional strTableName As String, _
    Optional bLink As Boolean)

    Dim appAccess As Object
    Dim lImportType As Long

    If Len(strMDBPath) = 0 Then
    strMDBPath = Replace(strTextFile, Right$(strTextFile, 3), "mdb", 1, 1)
    End If

    If Len(strTableName) = 0 Then
    strTableName = FileFromPath(strTextFile, True)
    End If

    If bLink Then
    lImportType = 5
    Else
    lImportType = 0
    End If

    Set appAccess = CreateObject("Access.Application")

    With appAccess
    .NewCurrentDatabase strMDBPath
    .DoCmd.TransferText lImportType, , strTableName, strTextFile, True
    .CloseCurrentDatabase
    .Quit
    End With

    Set appAccess = Nothing

    End Sub

    Sub tester()

    Dim strTextFile As String

    strTextFile = "C:\TempTables\ResultFile.txt"

    Text2Access strTextFile, , , True

    End Sub


    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:uxjIFGgeGHA.2068@TK2MSFTNGP02.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl
    >> Want to automate this simple sequence from Excel VBA:
    >> Start Access, but no need to be visible.
    >> File
    >> Open text file.
    >> Comma delimited, first row are field names.
    >> Save as .mdb in specified folder.
    >>
    >> Seems simple, but I can't see anything that does it.
    >> I understand that unfortunately Access doesn't have the option to
    >> record a macro.
    >>
    >> Thanks for any advice.
    >>
    >> RBS

    >
    > Try something like this:
    >
    > '----- start of example code -----
    > Dim strTextFilePath As String
    > Dim strMDBPath As String
    > Dim strTableName As String
    > Dim I As Integer
    >
    > Dim appAccess As Object
    >
    > '*** Here, I've hard-coded the name\path of the file to be imported.
    > strTextFilePath = "C:\Temp\TestImport.csv"
    >
    > strTableName = Dir(strTextFilePath) ' initially
    >
    > ' Allow for possible file name without extension.
    > I = InStrRev(strTextFilePath, ".")
    > If I = 0 Then
    > strMDBPath = strTextFilePath & ".mdb"
    > Else
    > strMDBPath = Left(strTextFilePath, I - 1) & ".mdb"
    > strTableName = _
    > Left(strTableName, InStrRev(strTableName, ".") - 1)
    > End If
    >
    > Set appAccess = CreateObject("Access.Application")
    >
    > With appAccess
    > .NewCurrentDatabase strMDBPath
    > .DoCmd.TransferText 0, , strTableName, strTextFilePath, True
    > .CloseCurrentDatabase
    > .Quit
    > End With
    >
    > Set appAccess = Nothing
    > '----- start of example code -----
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  9. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:OlYPasgeGHA.564@TK2MSFTNGP02.phx.gbl
    > All working beautifully now:

    [code snipped]

    Nice work.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  10. MH

    MH
    Expand Collapse
    Guest

    Can you give some more information about what your ultimate goal is?

    It may seem simple but with a database you have to set up your table in
    advance of importing which means knowing the datatypes in advance etc. as
    opposed to Excel which will let you import any data into any cell (and then
    it changes it to the datatype Excel thinks it should be!)

    If you provide some more information I'm sure someone will be able to point
    you in the right direction.

    MH


    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl...
    > Want to automate this simple sequence from Excel VBA:
    > Start Access, but no need to be visible.
    > File
    > Open text file.
    > Comma delimited, first row are field names.
    > Save as .mdb in specified folder.
    >
    > Seems simple, but I can't see anything that does it.
    > I understand that unfortunately Access doesn't have the option to record a
    > macro.
    >
    > Thanks for any advice.
    >
    > RBS
     
  11. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    My goal is simple: Display a plain comma separated text file in an Access
    table.
    Actually, I have figured it out and working perfectly fine. Access is doing
    a good job
    in setting the data types for the different fields, without me telling it
    how to do it.
    It has in fact nothing to do with Excel, except that I run the VBA code from
    Excel.
    See posted code.

    RBS


    "MH" <noway@nohow.co.uk> wrote in message
    news:%234hsEY3eGHA.3588@TK2MSFTNGP02.phx.gbl...
    > Can you give some more information about what your ultimate goal is?
    >
    > It may seem simple but with a database you have to set up your table in
    > advance of importing which means knowing the datatypes in advance etc. as
    > opposed to Excel which will let you import any data into any cell (and
    > then it changes it to the datatype Excel thinks it should be!)
    >
    > If you provide some more information I'm sure someone will be able to
    > point you in the right direction.
    >
    > MH
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23Uknw4deGHA.5040@TK2MSFTNGP03.phx.gbl...
    >> Want to automate this simple sequence from Excel VBA:
    >> Start Access, but no need to be visible.
    >> File
    >> Open text file.
    >> Comma delimited, first row are field names.
    >> Save as .mdb in specified folder.
    >>
    >> Seems simple, but I can't see anything that does it.
    >> I understand that unfortunately Access doesn't have the option to record
    >> a macro.
    >>
    >> Thanks for any advice.
    >>
    >> RBS

    >
    >
     

Share This Page