Want to read text files of random extensions into recordset

[Jarryd]

Hi,

This is an excerpt of some code I have been working on:
-------start------------
FileToOpen = Application.GetOpenFilename
If FileToOpen <> False Then
'Set path
path = Left(FileToOpen, InStrRev(FileToOpen, "\"))
'Open connection to file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended
Properties='text;HDR=NO;FMT=Delimited'"
'Set file
file = Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, "\"))
'Open recordset with Earnie data
rsEarnie.Open "Select * From " + file, cn, adOpenStatic, adLockReadOnly,
adCmdText
-------end------------

It works fine when the source file has an extension of "txt", but I want it
to work regardless of the extension. The file that will be selected will be
produced by our payroll package and is created as payroll.001 or
payroll.002. Rather than ask users to rename the file with a "txt"
extension, is there a way to read the data in the file as text without the
file explicitly being named as a text file? I have tried adCmdUnknown and
all the others but they don't work. I get: "Run-time error'-2147217911
(80040e09': Automation error"

Any ideas?

TIA

Jarryd

 

[jamescox]

Well, you did say *-any-* ideas...

Copy the file with the odd extension, save it as a file with a .tx
extension, read that file, then delete it. Or, if no one else is goin
to try to use the payroll files while you are messing with them, jus
rename them to something like payroll.001.txt and then rename them bac
when you are through

Lots of work - and slow - but if the .Open syntax is going to require
.txt extension, you may not find a better way. Good luck on it
though.

Actually, I searched for

jet odbc text file extension "not txt"

and got an instructive hit at

'Much ADO About Text Files
(http://msdn.microsoft.com/en-us/library/ms974559.aspx)

but I don't know if you want to get into registry editing on every P
that might need to read the payroll files..

 

[Jarryd]

Excellent response. I did try renaming and filecopy to a txt file, etc. but
I hit permissions issues cos you can't always be sure that Excel has rights
to the payroll file location. Seems to be a common problem. To be totally
honest I need to research more of the workarounds and see if there is a
solid one out there that works for all (or almost all) scenarios.

I will read the msdn article and have a think about it. I wish there was a
way to tell this idiot payroll system how to dump output files, but anyway.

Thanks again.

Jarryd

 

[Jarryd]

Hi all,

I have worked out that using the user TEMP folder to copy the file to as a
txt works. Sorting out the code now and will post when done so it can be
used if someone needs it.

Jarryd

 

[Jarryd]

Hi all,

This is what I came up with:
----------Start----------
Sub...
Dim cn As New ADODB.Connection
Dim rsEarnie As New ADODB.Recordset
Dim iLastRow As Integer
Dim sPathUser As String

FileToOpen = Application.GetOpenFilename

If FileToOpen <> False Then
'Get TEMP path and copy FileToOpen to it
sPathUser = Environ$("TEMP")
FileCopy FileToOpen, sPathUser + "\payroll.txt"

'Open connection to file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathUser &
";Extended _
Properties='text;HDR=NO;FMT=Delimited'"

'Open recordset with Earnie data
rsEarnie.Open "Select * From payroll.txt", cn, adOpenStatic, adLockReadOnly,
adCmdText
....
rsEarnie.Close
cn.Close
Kill (sPathUser + "\payroll.txt")
End If

End Sub
----------End----------

HTH someone.

Cheers,

Jarryd