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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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

"RB Smissaert" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)
> 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)

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)
>> 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)
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)
>>> 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)
>>
>>
>

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

RBS

"RB Smissaert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)
>>>> 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)
>>>
>>>
>>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)
>> 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)
>
>

"RB Smissaert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> All working beautifully now:
[code snipped]

Nice work.

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

(please reply to the newsgroup)

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>
>