How can I determine filetype/extension of files stored in OLE fields?

[Keith Brown]

I have an application that allows embedded storage of ANY chosen file
in an OLE field. The file could have been dragged-and-dropped into
the field or it might have been selected and imported programmaticall y
using the common file dialog. Regardless, I need to determine the
filetype/extension of each of these files already stored in my OLE
fields and display it for the user.

Double-clicking the raw OLE field or using the
.Verb = acOLEVerbOpen
.Action = acOLEActivate
methods will launch whatever application is associated with the type
of file stored in the OLE field, so, somehow, that information (.XLS,
..DOC, .MPG, .PDF, etc.) must be available -- but where? I want to be
able to list, in a form, my records that contain the OLE fields along
with other info describing the OLE contents, such as size (obtainable
via the FileSize function). Is there a way for me to get at the file
extension as well?

Thanks for any help you can offer!

[TC]

The fact that it can launch the appropriate application, does not
necessarily mean that it knows the name of the original file. It might just
be storing a so-called ProgID, eg. "Excel.Applicat ion". That is enough to
identify the relevant application. If the object is embedded - not linked -
I doubt that the name of the original file is stored, at all.

HTH,
TC
"Keith Brown" <ks*****@one.ne t> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

I have an application that allows embedded storage of ANY chosen file
in an OLE field. The file could have been dragged-and-dropped into
the field or it might have been selected and imported programmaticall y
using the common file dialog. Regardless, I need to determine the
filetype/extension of each of these files already stored in my OLE
fields and display it for the user.

Double-clicking the raw OLE field or using the
.Verb = acOLEVerbOpen
.Action = acOLEActivate
methods will launch whatever application is associated with the type
of file stored in the OLE field, so, somehow, that information (.XLS,
.DOC, .MPG, .PDF, etc.) must be available -- but where? I want to be
able to list, in a form, my records that contain the OLE fields along
with other info describing the OLE contents, such as size (obtainable
via the FileSize function). Is there a way for me to get at the file
extension as well?

Thanks for any help you can offer!

[Keith Brown]

"TC" <a@b.c.d> wrote in message news:<106731317 0.971034@teutho s>...

The fact that it can launch the appropriate application, does not
necessarily mean that it knows the name of the original file. It might just
be storing a so-called ProgID, eg. "Excel.Applicat ion". That is enough to
identify the relevant application. If the object is embedded - not linked -
I doubt that the name of the original file is stored, at all.

Right. I figured the full filename was no longer available and really
have no need for it. The ProgID you refer to might even be better
than an extension, since it spells out the actual name of the
associated application. I would be pretty happy if someone could show
me some code that could produce either one! Thanks for your response.

[Terry Kreft]

If you look at what's actually in the field you'll find that it contains the
filename and the full (short) path to the file.

So a nasty first stab at code to get this info could look like this

Function PathFromOLEFiel d()
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Dim loFld As DAO.Field
Dim varChunk As Variant
Dim lngCount As Long
Dim strRet As String
Dim strFile As String
Dim strPath As String
Dim intInstr As Integer

Set loDb = Access.CurrentD b
Set loRst = loDb.OpenRecord set("Table1")
Set loFld = loRst.Fields("H mmm")
Do Until loRst.EOF
varChunk = loFld.GetChunk( 70, 500)
strRet = ""
For lngCount = LBound(varChunk ) To UBound(varChunk )
strRet = strRet & Chr(varChunk(ln gCount))
Next
intInstr = InStr(strRet, Chr(0))
strFile = Left(strRet, intInstr - 1)
strRet = Mid(strRet, intInstr + 1)
intInstr = InStr(strRet, Chr(0) & Chr(0))
strPath = Left(strRet, intInstr - 1)
Debug.Print strFile
Debug.Print strPath
loRst.MoveNext
Loop
Set loFld = Nothing
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
End Function

Terry

"Keith Brown" <ks*****@one.ne t> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

"TC" <a@b.c.d> wrote in message news:<106731317 0.971034@teutho s>...

The fact that it can launch the appropriate application, does not
necessarily mean that it knows the name of the original file. It might just be storing a so-called ProgID, eg. "Excel.Applicat ion". That is enough to identify the relevant application. If the object is embedded - not linked - I doubt that the name of the original file is stored, at all.

Right. I figured the full filename was no longer available and really
have no need for it. The ProgID you refer to might even be better
than an extension, since it spells out the actual name of the
associated application. I would be pretty happy if someone could show
me some code that could produce either one! Thanks for your response.

[Keith Brown]

Terry,

Thanks so much for posting the code. I tried it out and,
unfortunately, was not able to get anything intelligible as output.
(The filename value [strFile] for each field was displaying as ".8"
and the pathname was empty.) Is it possible your code is what one
would use to display the contents of an OLE field containing a link to
a file, rather than a complete embedded file? All of my OLE fields
contain embedded files ONLY.

Keith

[TC]

Where do the 70 & 500 come from? (getchunk 70,500)

TC
"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bn******** **@newsreaderg1 .core.theplanet .net...

If you look at what's actually in the field you'll find that it contains the filename and the full (short) path to the file.

So a nasty first stab at code to get this info could look like this

Function PathFromOLEFiel d()
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Dim loFld As DAO.Field
Dim varChunk As Variant
Dim lngCount As Long
Dim strRet As String
Dim strFile As String
Dim strPath As String
Dim intInstr As Integer

Set loDb = Access.CurrentD b
Set loRst = loDb.OpenRecord set("Table1")
Set loFld = loRst.Fields("H mmm")
Do Until loRst.EOF
varChunk = loFld.GetChunk( 70, 500)
strRet = ""
For lngCount = LBound(varChunk ) To UBound(varChunk )
strRet = strRet & Chr(varChunk(ln gCount))
Next
intInstr = InStr(strRet, Chr(0))
strFile = Left(strRet, intInstr - 1)
strRet = Mid(strRet, intInstr + 1)
intInstr = InStr(strRet, Chr(0) & Chr(0))
strPath = Left(strRet, intInstr - 1)
Debug.Print strFile
Debug.Print strPath
loRst.MoveNext
Loop
Set loFld = Nothing
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
End Function

Terry

"Keith Brown" <ks*****@one.ne t> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

"TC" <a@b.c.d> wrote in message news:<106731317 0.971034@teutho s>...

The fact that it can launch the appropriate application, does not
necessarily mean that it knows the name of the original file. It might just be storing a so-called ProgID, eg. "Excel.Applicat ion". That is enough to identify the relevant application. If the object is embedded - not linked - I doubt that the name of the original file is stored, at all.

Right. I figured the full filename was no longer available and really
have no need for it. The ProgID you refer to might even be better
than an extension, since it spells out the actual name of the
associated application. I would be pretty happy if someone could show
me some code that could produce either one! Thanks for your response.

[Terry Kreft]

No I wrote it using embedded files.

I did a test on it with 3 different file types (*..txt, *.zip and *.mdb) and
it works for each of these.

What file type are you embedding?

Terry

"Keith Brown" <ks*****@one.ne t> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

Terry,

Thanks so much for posting the code. I tried it out and,
unfortunately, was not able to get anything intelligible as output.
(The filename value [strFile] for each field was displaying as ".8"
and the pathname was empty.) Is it possible your code is what one
would use to display the contents of an OLE field containing a link to
a file, rather than a complete embedded file? All of my OLE fields
contain embedded files ONLY.

Keith

[Terry Kreft]

Empiricism.

By grabbing a number of different records I observed that:-
1) the filename consistently appeared at byte 70
2) the filename was terminated with a single Null character (Chr(0))
3) the filename was followed by the full path terminated by a pair of
Null characters.
4) the path was in short form and therefore the length of the path plus
file path are
unlikely to exceed 500 bytes (520 (2 * MAX_PATH ) would have been a
better bet)

but as I say it's dirty code. Which means it shows a principle.

Terry
"TC" <a@b.c.d> wrote in message news:1067394655 .92126@teuthos. ..

Where do the 70 & 500 come from? (getchunk 70,500)

TC
"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bn******** **@newsreaderg1 .core.theplanet .net...

If you look at what's actually in the field you'll find that it contains

the

filename and the full (short) path to the file.

So a nasty first stab at code to get this info could look like this

Function PathFromOLEFiel d()
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Dim loFld As DAO.Field
Dim varChunk As Variant
Dim lngCount As Long
Dim strRet As String
Dim strFile As String
Dim strPath As String
Dim intInstr As Integer

Set loDb = Access.CurrentD b
Set loRst = loDb.OpenRecord set("Table1")
Set loFld = loRst.Fields("H mmm")
Do Until loRst.EOF
varChunk = loFld.GetChunk( 70, 500)
strRet = ""
For lngCount = LBound(varChunk ) To UBound(varChunk )
strRet = strRet & Chr(varChunk(ln gCount))
Next
intInstr = InStr(strRet, Chr(0))
strFile = Left(strRet, intInstr - 1)
strRet = Mid(strRet, intInstr + 1)
intInstr = InStr(strRet, Chr(0) & Chr(0))
strPath = Left(strRet, intInstr - 1)
Debug.Print strFile
Debug.Print strPath
loRst.MoveNext
Loop
Set loFld = Nothing
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
End Function

Terry

"Keith Brown" <ks*****@one.ne t> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

"TC" <a@b.c.d> wrote in message news:<106731317 0.971034@teutho s>...
> The fact that it can launch the appropriate application, does not
> necessarily mean that it knows the name of the original file. It

might just

> be storing a so-called ProgID, eg. "Excel.Applicat ion". That is

enough to

> identify the relevant application. If the object is embedded - not

linked -

> I doubt that the name of the original file is stored, at all.

Right. I figured the full filename was no longer available and really
have no need for it. The ProgID you refer to might even be better
than an extension, since it spells out the actual name of the
associated application. I would be pretty happy if someone could show
me some code that could produce either one! Thanks for your response.

[TC]

"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bn******** **@newsreaderm1 .core.theplanet .net...

Empiricism.

By grabbing a number of different records I observed that:-
1) the filename consistently appeared at byte 70
2) the filename was terminated with a single Null character (Chr(0))
3) the filename was followed by the full path terminated by a pair of
Null characters.
4) the path was in short form and therefore the length of the path plus
file path are
unlikely to exceed 500 bytes (520 (2 * MAX_PATH ) would have been a
better bet)

but as I say it's dirty code. Which means it shows a principle.
Maybe! But there is tons of dirty space inside many MS file structures. So
the appearance of particular data, at a particular place, on a few
occasions, could easily arise from random causes.

TC


Terry
"TC" <a@b.c.d> wrote in message news:1067394655 .92126@teuthos. ..

Where do the 70 & 500 come from? (getchunk 70,500)

TC
"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bn******** **@newsreaderg1 .core.theplanet .net...

If you look at what's actually in the field you'll find that it contains

the

filename and the full (short) path to the file.

So a nasty first stab at code to get this info could look like this

Function PathFromOLEFiel d()
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Dim loFld As DAO.Field
Dim varChunk As Variant
Dim lngCount As Long
Dim strRet As String
Dim strFile As String
Dim strPath As String
Dim intInstr As Integer

Set loDb = Access.CurrentD b
Set loRst = loDb.OpenRecord set("Table1")
Set loFld = loRst.Fields("H mmm")
Do Until loRst.EOF
varChunk = loFld.GetChunk( 70, 500)
strRet = ""
For lngCount = LBound(varChunk ) To UBound(varChunk )
strRet = strRet & Chr(varChunk(ln gCount))
Next
intInstr = InStr(strRet, Chr(0))
strFile = Left(strRet, intInstr - 1)
strRet = Mid(strRet, intInstr + 1)
intInstr = InStr(strRet, Chr(0) & Chr(0))
strPath = Left(strRet, intInstr - 1)
Debug.Print strFile
Debug.Print strPath
loRst.MoveNext
Loop
Set loFld = Nothing
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
End Function

Terry

"Keith Brown" <ks*****@one.ne t> wrote in message
news:3c******** *************** ***@posting.goo gle.com...
> "TC" <a@b.c.d> wrote in message news:<106731317 0.971034@teutho s>...
> > The fact that it can launch the appropriate application, does not
> > necessarily mean that it knows the name of the original file. It might just
> > be storing a so-called ProgID, eg. "Excel.Applicat ion". That is enough to
> > identify the relevant application. If the object is embedded - not
linked -
> > I doubt that the name of the original file is stored, at all.
>
> Right. I figured the full filename was no longer available and

really > have no need for it. The ProgID you refer to might even be better
> than an extension, since it spells out the actual name of the
> associated application. I would be pretty happy if someone could show > me some code that could produce either one! Thanks for your response.

[Terry Kreft]

If you're trying to start an argument then go ahead, you're only arguing
with yourself.

I refer you back to my original posting.

Take note of the phrase "... a nasty first stab at code ..." .

This was meant to imply that
1) the code cited was not tested to any great degree but that it did
work on the tests carried out.
2) it would need work on it to polish and improve it , even to possibly
disprove it as a method.

Of course if you are in the know on how an OLE Object field in Access stores
the object and the relevant references to it then go ahead and enlighten us,
otherwise we're stuck with the empirical approach.
Terry

"TC" <a@b.c.d> wrote in message news:1067475808 .597311@teuthos ...

"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bn******** **@newsreaderm1 .core.theplanet .net...

Empiricism.

By grabbing a number of different records I observed that:-
1) the filename consistently appeared at byte 70
2) the filename was terminated with a single Null character (Chr(0))
3) the filename was followed by the full path terminated by a pair of
Null characters.
4) the path was in short form and therefore the length of the path plus

file path are
unlikely to exceed 500 bytes (520 (2 * MAX_PATH ) would have been a better bet)

but as I say it's dirty code. Which means it shows a principle.

Maybe! But there is tons of dirty space inside many MS file structures. So
the appearance of particular data, at a particular place, on a few
occasions, could easily arise from random causes.

TC

Terry
"TC" <a@b.c.d> wrote in message news:1067394655 .92126@teuthos. ..

Where do the 70 & 500 come from? (getchunk 70,500)

TC
"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bn******** **@newsreaderg1 .core.theplanet .net...
> If you look at what's actually in the field you'll find that it

contains the
> filename and the full (short) path to the file.
>
> So a nasty first stab at code to get this info could look like this
>
> Function PathFromOLEFiel d()
> Dim loDb As DAO.Database
> Dim loRst As DAO.Recordset
> Dim loFld As DAO.Field
> Dim varChunk As Variant
> Dim lngCount As Long
> Dim strRet As String
> Dim strFile As String
> Dim strPath As String
> Dim intInstr As Integer
>
> Set loDb = Access.CurrentD b
> Set loRst = loDb.OpenRecord set("Table1")
> Set loFld = loRst.Fields("H mmm")
> Do Until loRst.EOF
> varChunk = loFld.GetChunk( 70, 500)
> strRet = ""
> For lngCount = LBound(varChunk ) To UBound(varChunk )
> strRet = strRet & Chr(varChunk(ln gCount))
> Next
> intInstr = InStr(strRet, Chr(0))
> strFile = Left(strRet, intInstr - 1)
> strRet = Mid(strRet, intInstr + 1)
> intInstr = InStr(strRet, Chr(0) & Chr(0))
> strPath = Left(strRet, intInstr - 1)
> Debug.Print strFile
> Debug.Print strPath
> loRst.MoveNext
> Loop
> Set loFld = Nothing
> loRst.Close
> Set loRst = Nothing
> Set loDb = Nothing
> End Function
>
>
>
> Terry
>
> "Keith Brown" <ks*****@one.ne t> wrote in message
> news:3c******** *************** ***@posting.goo gle.com...
> > "TC" <a@b.c.d> wrote in message news:<106731317 0.971034@teutho s>... > > > The fact that it can launch the appropriate application, does not > > > necessarily mean that it knows the name of the original file. It

might

> just
> > > be storing a so-called ProgID, eg. "Excel.Applicat ion". That is

enough

> to
> > > identify the relevant application. If the object is embedded - not > linked -
> > > I doubt that the name of the original file is stored, at all.
> >
> > Right. I figured the full filename was no longer available and really > > have no need for it. The ProgID you refer to might even be better
> > than an extension, since it spells out the actual name of the
> > associated application. I would be pretty happy if someone could show > > me some code that could produce either one! Thanks for your response. >
>