|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Extract ole object using vb from access dbHi
In my access table one of the field data type OLE Object. and its contain word document file and omni file. I need to save this file into local folder. any idea how to do this thru vb? Thanks Bala "Bala" <B***@discussions.microsoft.com> wrote in message How much code do you want? I'm going to assume you can already read this news:CCC8EC0F-AD36-42A2-86A7-1794B70C8684@microsoft.com... > In my access table one of the field data type OLE Object. and its contain > word document file and omni file. I need to save this file into local > folder. any idea how to do this thru vb? data into a byte array (and if you can't search back a few threads for one with"BLOB" in the title) and just go for the writing part: Dim nFile as Integer nFile = FreeFile() Open <PathToFile> For Binary Access Write As #nFile Put #nFile, , MyByteArray() Close #nFile On Thu, 20 Oct 2005 10:33:12 -0700, "Bala" <B***@discussions.microsoft.com> wrote: ¤ Hi¤ ¤ In my access table one of the field data type OLE Object. and its contain ¤ word document file and omni file. I need to save this file into local ¤ folder. any idea how to do this thru vb? If it's stored as an OLE file then the OLE header needs to be stripped from the file. See if the following works for you: Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ (lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long) Type PT Width As Integer Height As Integer End Type Type OBJECTHEADER Signature As Integer HeaderSize As Integer ObjectType As Long NameLen As Integer ClassLen As Integer NameOffset As Integer ObjectSize As PT OleInfo As String * 256 End Type Function GetOLEField() Dim cnn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim strFilePath As String cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=e:\My Documents\db1 XP.mdb;" & _ "Jet OLEDB:Engine Type=4;" strSQL = "Select OLEField from Table1 WHERE [record ID]=3" rs.Open strSQL, cnn strFilePath = GetFile(rs.Fields("OLEField"), "c:\temp\") rs.Close cnn.Close End Function Private Function GetFile(ByVal ADOField As ADODB.Field, _ tmpPath As String) As String Dim Arr() As Byte Dim ObjHeader As OBJECTHEADER Dim Buffer As String Dim ObjectOffset As Long Dim FileOffset As Long Dim FileHeaderOffset As Integer Dim FileStream() As Byte Dim i As Long Dim tmpDoc 'Resize the array, then fill it with 'the entire contents of the field ReDim Arr(ADOField.ActualSize) Arr() = ADOField.GetChunk(ADOField.ActualSize) 'Copy the first 19 bytes into a variable of the ' defined type OBJECTHEADER CopyMemory ObjHeader, Arr(0), 19 'Determine where the header ends ObjectOffset = ObjHeader.HeaderSize + 1 'Grab enough bytes after the OLE header to get file header Buffer = "" For i = ObjectOffset To ObjectOffset + 512 Buffer = Buffer & Chr(Arr(i)) Next i 'Make sure the class of the object is Word Document If Mid(Buffer, 12, 13) = "Word.Document" Then FileHeaderOffset = InStr(Buffer, "ÐÏ") If FileHeaderOffset > 0 Then 'Calculate the beginning of the document FileOffset = ObjectOffset + FileHeaderOffset - 1 'Move document into its own array ReDim FileStream(UBound(Arr) - FileOffset) CopyMemory FileStream(0), Arr(FileOffset), UBound(Arr) - FileOffset + 1 'Document file path tmpImg = tmpPath & "WordFile.doc" Open tmpDoc For Binary As #1 For i = 0 To UBound(FileStream) Put #1, , FileStream(i) Next i Close #1 GetFile = tmpDoc End If End If End Function Paul ~~~~ Microsoft MVP (Visual Basic) thanks. got it.
bala Show quoteHide quote "Paul Clement" wrote: > On Thu, 20 Oct 2005 10:33:12 -0700, "Bala" <B***@discussions.microsoft.com> wrote: > > ¤ Hi > ¤ > ¤ In my access table one of the field data type OLE Object. and its contain > ¤ word document file and omni file. I need to save this file into local > ¤ folder. any idea how to do this thru vb? > > If it's stored as an OLE file then the OLE header needs to be stripped from the file. See if the > following works for you: > > Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ > (lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long) > > Type PT > Width As Integer > Height As Integer > End Type > > Type OBJECTHEADER > Signature As Integer > HeaderSize As Integer > ObjectType As Long > NameLen As Integer > ClassLen As Integer > NameOffset As Integer > ObjectSize As PT > OleInfo As String * 256 > End Type > > Function GetOLEField() > > Dim cnn As New ADODB.Connection > Dim rs As New ADODB.Recordset > Dim strSQL As String > Dim strFilePath As String > > cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ > "Data Source=e:\My Documents\db1 XP.mdb;" & _ > "Jet OLEDB:Engine Type=4;" > > strSQL = "Select OLEField from Table1 WHERE [record ID]=3" > > rs.Open strSQL, cnn > > strFilePath = GetFile(rs.Fields("OLEField"), "c:\temp\") > > rs.Close > cnn.Close > > End Function > > Private Function GetFile(ByVal ADOField As ADODB.Field, _ > tmpPath As String) As String > > Dim Arr() As Byte > Dim ObjHeader As OBJECTHEADER > Dim Buffer As String > Dim ObjectOffset As Long > Dim FileOffset As Long > Dim FileHeaderOffset As Integer > Dim FileStream() As Byte > Dim i As Long > Dim tmpDoc > > 'Resize the array, then fill it with > 'the entire contents of the field > ReDim Arr(ADOField.ActualSize) > Arr() = ADOField.GetChunk(ADOField.ActualSize) > > 'Copy the first 19 bytes into a variable of the > ' defined type OBJECTHEADER > CopyMemory ObjHeader, Arr(0), 19 > > 'Determine where the header ends > ObjectOffset = ObjHeader.HeaderSize + 1 > > 'Grab enough bytes after the OLE header to get file header > Buffer = "" > > For i = ObjectOffset To ObjectOffset + 512 > Buffer = Buffer & Chr(Arr(i)) > Next i > > 'Make sure the class of the object is Word Document > If Mid(Buffer, 12, 13) = "Word.Document" Then > FileHeaderOffset = InStr(Buffer, "ÃÃ") > If FileHeaderOffset > 0 Then > 'Calculate the beginning of the document > FileOffset = ObjectOffset + FileHeaderOffset - 1 > > 'Move document into its own array > ReDim FileStream(UBound(Arr) - FileOffset) > CopyMemory FileStream(0), Arr(FileOffset), UBound(Arr) - FileOffset + 1 > > 'Document file path > tmpImg = tmpPath & "WordFile.doc" > > Open tmpDoc For Binary As #1 > > For i = 0 To UBound(FileStream) > Put #1, , FileStream(i) > Next i > > Close #1 > > GetFile = tmpDoc > > End If > > End If > > End Function > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) > Hi Paul,
Your post was very educational. The approach worked for me for Word and PowerPoint documents but I ran into problems with Excel and PDF documents (both apps of which appear to be OLE registered) and OLE Packaged documents. Is there something you can think of that is a generic way to access/dissect/process ole wrapper and data information? Perhaps a class/structure similar to OBJECTHEADER that works for anything in an OLE field? What we want to do is programmatically loop through records in an Access table (each of which has an OLE field with an embedded document) and write that document as its original file back on to the hard disk. There has to be a standard/generic way of doing this because Microsoft seems to be able to, i.e. a user can always get ole/package information or open the document by double-clicking. Just a tidbit for other users...Your example below is for VB 6. I am using VB .Net. I am posting the equivalent code below: Private Function GetHeader(Field) As OBJECTHEADER Dim ObjHeader As OBJECTHEADER Dim sar(20) As Byte Dim MyGC As GCHandle Dim Arr() As Byte ReDim Arr(Field.ActualSize) Arr = Field.GetChunk(Field.ActualSize) Array.Copy(Arr, 0, sar, 0, 20) MyGC = GCHandle.Alloc(sar, GCHandleType.Pinned) 'Marshals data from an unmanaged block of memory to a newly 'allocated managed object of the specified type. ObjHeader = Marshal.PtrToStructure(MyGC.AddrOfPinnedObject, ObjHeader.GetType) Return ObjHeader End Function Thanks Tony P.S. In the OBJECTHEADER definition my compiler didn't like: OleInfo As String * 256 What does the '* 256' mean and what is OleInfo supposed to be? Show quoteHide quote "Paul Clement" wrote: > On Thu, 20 Oct 2005 10:33:12 -0700, "Bala" <B***@discussions.microsoft.com> wrote: > > ¤ Hi > ¤ > ¤ In my access table one of the field data type OLE Object. and its contain > ¤ word document file and omni file. I need to save this file into local > ¤ folder. any idea how to do this thru vb? > > If it's stored as an OLE file then the OLE header needs to be stripped from the file. See if the > following works for you: > > Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ > (lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long) > > Type PT > Width As Integer > Height As Integer > End Type > > Type OBJECTHEADER > Signature As Integer > HeaderSize As Integer > ObjectType As Long > NameLen As Integer > ClassLen As Integer > NameOffset As Integer > ObjectSize As PT > OleInfo As String * 256 > End Type > > Function GetOLEField() > > Dim cnn As New ADODB.Connection > Dim rs As New ADODB.Recordset > Dim strSQL As String > Dim strFilePath As String > > cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ > "Data Source=e:\My Documents\db1 XP.mdb;" & _ > "Jet OLEDB:Engine Type=4;" > > strSQL = "Select OLEField from Table1 WHERE [record ID]=3" > > rs.Open strSQL, cnn > > strFilePath = GetFile(rs.Fields("OLEField"), "c:\temp\") > > rs.Close > cnn.Close > > End Function > > Private Function GetFile(ByVal ADOField As ADODB.Field, _ > tmpPath As String) As String > > Dim Arr() As Byte > Dim ObjHeader As OBJECTHEADER > Dim Buffer As String > Dim ObjectOffset As Long > Dim FileOffset As Long > Dim FileHeaderOffset As Integer > Dim FileStream() As Byte > Dim i As Long > Dim tmpDoc > > 'Resize the array, then fill it with > 'the entire contents of the field > ReDim Arr(ADOField.ActualSize) > Arr() = ADOField.GetChunk(ADOField.ActualSize) > > 'Copy the first 19 bytes into a variable of the > ' defined type OBJECTHEADER > CopyMemory ObjHeader, Arr(0), 19 > > 'Determine where the header ends > ObjectOffset = ObjHeader.HeaderSize + 1 > > 'Grab enough bytes after the OLE header to get file header > Buffer = "" > > For i = ObjectOffset To ObjectOffset + 512 > Buffer = Buffer & Chr(Arr(i)) > Next i > > 'Make sure the class of the object is Word Document > If Mid(Buffer, 12, 13) = "Word.Document" Then > FileHeaderOffset = InStr(Buffer, "ÃÃ") > If FileHeaderOffset > 0 Then > 'Calculate the beginning of the document > FileOffset = ObjectOffset + FileHeaderOffset - 1 > > 'Move document into its own array > ReDim FileStream(UBound(Arr) - FileOffset) > CopyMemory FileStream(0), Arr(FileOffset), UBound(Arr) - FileOffset + 1 > > 'Document file path > tmpImg = tmpPath & "WordFile.doc" > > Open tmpDoc For Binary As #1 > > For i = 0 To UBound(FileStream) > Put #1, , FileStream(i) > Next i > > Close #1 > > GetFile = tmpDoc > > End If > > End If > > End Function > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) > On Mon, 7 Nov 2005 17:07:02 -0800, "Tony" <T***@discussions.microsoft.com> wrote: ¤ Hi Paul,¤ ¤ Your post was very educational. The approach worked for me for Word and ¤ PowerPoint documents but I ran into problems with Excel and PDF documents ¤ (both apps of which appear to be OLE registered) and OLE Packaged documents. ¤ For Excel, you need to look for Excel.Sheet. I haven't tried a PDF file. ¤ Is there something you can think of that is a generic way to ¤ access/dissect/process ole wrapper and data information? Perhaps a ¤ class/structure similar to OBJECTHEADER that works for anything in an OLE ¤ field? ¤ I believe that the class name of the document is stored in the OLE header information although I've never looked at the specifications for embedded documents so I can't really help with the OLE info. From the class name the host application can then determine the native application for the document through its registry entries and then launch the app with the document. ¤ What we want to do is programmatically loop through records in an Access ¤ table (each of which has an OLE field with an embedded document) and write ¤ that document as its original file back on to the hard disk. ¤ ¤ There has to be a standard/generic way of doing this because Microsoft seems ¤ to be able to, i.e. a user can always get ole/package information or open the ¤ document by double-clicking. You wouldn't be the first one to try this. However, I've looked at some of these embedded documents and can't seem to find a consistency as to how the OLE wrapper is created - at least not without use of the OLE API. Trying to scrape off the OLE wrapper through VB code seems to necessitate exact knowledge of the wrapper contents for each document type. ¤ P.S. In the OBJECTHEADER definition my compiler didn't like: ¤ OleInfo As String * 256 ¤ ¤ What does the '* 256' mean and what is OleInfo supposed to be? ¤ It's just fixed space to pad out the buffer to the proper length. Paul ~~~~ Microsoft MVP (Visual Basic) |
|||||||||||||||||||||||
Other interesting topics