automate open text file

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

RB Smissaert

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
 
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 said:
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
Click to expand...

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 -----
 
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
 
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
 
Aah, can see now, need acLinkDelim (5).

RBS

RB Smissaert said:
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
Click to expand...
 
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
 
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
 
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
 
Back
Top