tleforge
asked on
Access OLE Objects (Word docs, Excel, etc..) in DB
I posted this in the general section, too.
I have a database that contains an OLEObject field. This field can contain Word documents, Excel Spreadsheets, etc... I need to know:
1) How to correctly retrieve the Documents name without having to retrieve the entire document itself.
2) How to retrieve the Document itself (preferably using the name from number 1
I'm creating an application that gives the user an explorer like interface. The tree might look something like this:
Root
|_Word Documents
|_Excel Spreadsheets
|_etc...
If they click on Word documents then I would like to retrieve only the names into a listview to the right of the tree. If they double click on one of the names I would then like to bring the document up into either an instance of Word or a Word-compatible OCX.
Anyone have any ideas? The points are VERY negotiable (up to 400 for a complete answer)
I have a database that contains an OLEObject field. This field can contain Word documents, Excel Spreadsheets, etc... I need to know:
1) How to correctly retrieve the Documents name without having to retrieve the entire document itself.
2) How to retrieve the Document itself (preferably using the name from number 1
I'm creating an application that gives the user an explorer like interface. The tree might look something like this:
Root
|_Word Documents
|_Excel Spreadsheets
|_etc...
If they click on Word documents then I would like to retrieve only the names into a listview to the right of the tree. If they double click on one of the names I would then like to bring the document up into either an instance of Word or a Word-compatible OCX.
Anyone have any ideas? The points are VERY negotiable (up to 400 for a complete answer)
I am sorry I click by mistake. Answer is continued:
else
set objexcel=new excel.applicatio
objexcel.open rs!file_name
objexcel.visible=true
endif
Secondway:
Just store the file name which I think you are doing now. Parse the file name string to know whether the extension is .doc or .xls. Rest is I beleive the same.
else
set objexcel=new excel.applicatio
objexcel.open rs!file_name
objexcel.visible=true
endif
Secondway:
Just store the file name which I think you are doing now. Parse the file name string to know whether the extension is .doc or .xls. Rest is I beleive the same.
ASKER
It looks promising, I'm going to try that out. I'll get back to you ASAP. It may take me the weekend though. Sorry about the delay.
Thank you for your quick reply.
P.S. I don't mind storing the TYPE of this object, but I was just wondering if you knew of a way that I could get that from the object itself or if I could even get at the Document properties that are a part of all Office9x documents, without actually opening it. Again, thank you.
Thank you for your quick reply.
P.S. I don't mind storing the TYPE of this object, but I was just wondering if you knew of a way that I could get that from the object itself or if I could even get at the Document properties that are a part of all Office9x documents, without actually opening it. Again, thank you.
So far as I know There is no generic thing like 'Office Document'. hence it almost always becomes necessary to bind to a particular object.
ASKER
mukvij,
I apologize for taking so long to get back...
I tried your suggestion and couldn't get it to work. Admittedly, I've been away from VB for a while, working with Delphi so I may be forgetting something.
I tried the code like you said and, of course, found that I needed to declare ObjWord. At first I declared it as an Object and when I ran it an error came up saying that the "Object did not support this method" (I'm assuming the open method). I then declared it as a Word.application (after adding the reference) and now it comes back saying "Method or data member not found" (again, on the open method).
Am I missing something?
I apologize for taking so long to get back...
I tried your suggestion and couldn't get it to work. Admittedly, I've been away from VB for a while, working with Delphi so I may be forgetting something.
I tried the code like you said and, of course, found that I needed to declare ObjWord. At first I declared it as an Object and when I ran it an error came up saying that the "Object did not support this method" (I'm assuming the open method). I then declared it as a Word.application (after adding the reference) and now it comes back saying "Method or data member not found" (again, on the open method).
Am I missing something?
Add the reference to Word Object library and following is the sample code. For Excel Replece Documents by Workbooks.:
Dim objWord As Word.Application
Set objWord = New Word.Application
objWord.Documents.Open "d:\abc\xyz.doc"
objWord.Visible = True
Dim objWord As Word.Application
Set objWord = New Word.Application
objWord.Documents.Open "d:\abc\xyz.doc"
objWord.Visible = True
ASKER
mukvij,
Remember though, I'm not coming at this from a "document stored in a directory" standpoint, but instead from a "document stored in a database".
The "objWord.Documents.Open rs!WordDocument" statement gives me a Type Mismatch error.
Remember though, I'm not coming at this from a "document stored in a directory" standpoint, but instead from a "document stored in a database".
The "objWord.Documents.Open rs!WordDocument" statement gives me a Type Mismatch error.
What does WordDocument field store. Does it store path of word file?
ASKER
It's an OLE Object field. It holds the actual document itself. I didn't want to get into the mess of storing the documents in directory structures. I've been there and with a potential of thousands of documents it gets pretty ugly.
I need to be able to store the Word documents in a database. The documents themselves, not paths to the documents.
I need to be able to store the Word documents in a database. The documents themselves, not paths to the documents.
I am sorry I did not understand you correctly. hence I wasted your time. All Information I passed was dependant on paths.
I really do not think it is possible. Only way I can think of is having a OLE Container control on your form. From there you can do whatever you want to do. Because if you want to open document contained in access field in seperate word window. It will not be linked. Heve a OLE containter control and set its SourceDoc property.
I think that would help.
I really do not think it is possible. Only way I can think of is having a OLE Container control on your form. From there you can do whatever you want to do. Because if you want to open document contained in access field in seperate word window. It will not be linked. Heve a OLE containter control and set its SourceDoc property.
I think that would help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It might take me a while to test your code, but from what I've read it looks REAL promising. Let me ask you one more question... The RTF control that comes with VB is not quite as feature rich as MSWord is and thus does not except drawings, tables, etc... that Word can place in the documents. I'm wondering if I were to find an RTF that was feature complete enough do you think this could still work? I could probably go the route of bring Word INTO my app and using it as my RTF control, but I was wanting to stay away from that slow, clunky option if possible.
Also, do you know of a way to get this to work with other file types (see original question up top)? Basically, I need to be able to store just about anything in a BLOB or some binary field and then retrieve it.
I'm not trying to get out of awarding you the points (I probably will anyhow depending on how my tests go), but I was just wondering if you could also help with the rest of the question and maybe supply a more generic solution.
Thanks for all your hard work.
Also, do you know of a way to get this to work with other file types (see original question up top)? Basically, I need to be able to store just about anything in a BLOB or some binary field and then retrieve it.
I'm not trying to get out of awarding you the points (I probably will anyhow depending on how my tests go), but I was just wondering if you could also help with the rest of the question and maybe supply a more generic solution.
Thanks for all your hard work.
Here is another set of functions to read and write any binary file. This e.g is with Access db but it should also work with SQL db with some modifications. Probably you will need to add another field in db saying what type of object is stored in the field and then just read the data in similar file type. e.g if the object is a word document then read the data in a .doc file and open with word, if the object is an bmp file read in a .bmp file and read with Paintbrush or image control.
The e.g is very big.
Reading, Storing, & Writing Binary Large Objects (BLOBs)
You can store large data objects (such as sound, video, or graphic data) in a field with the OLE Object data type in a Microsoft Access table. Some large binary data objects cannot be represented, however, if they do not have an OLE server that understands the data being stored. You can also store copies of executable program files or other non-OLE data in a Microsoft Access table. This type of data is referred to as a binary large object bitmap (BLOB).
MORE INFORMATION
The following example contains two sample user-defined functions that you can use to manage large amounts of binary data in a field with the OLE Object data type. The user-defined functions are ReadBLOB() and WriteBLOB().
The ReadBLOB() function reads a binary disk file and stores it in an OLE Object field.
The WriteBLOB() function writes binary data stored in an OLE Object field to a disk file.
This example demonstrates how to copy a binary file into an OLE Object field and then write it back out to a new disk file:
Create a new module called BLOB and enter the following lines in the module's Declarations section:
Option Explicit
Const BlockSize = 32768
NOTE: if you are working with Microsoft Access 2.0 you will need to include the following definitions after the two previous ones:
Const dbOpenTable = DB_OPEN_TABLE
Const acSysCmdInitMeter = SYSCMD_INITMETER
Const acSysCmdUpdateMeter = SYSCMD_UPDATEMETER
Const acSysCmdRemoveMeter = SYSCMD_REMOVEMETER
Enter the following code in the module.
NOTE: This following technique will not work for Microsoft Access 1.x. For Microsoft Access 1.x you will need to modify the code so you will use Table variables instead of Recordset variables and the OpenTable function instead of OpenRecordset.
'*************************
' FUNCTION: ReadBLOB()
'
' PURPOSE:
' Reads a BLOB from a disk file and stores the contents in the
' specified table and field.
'
' PREREQUISITES:
' The specified table with the OLE object field to contain the
' binary data must be opened in Visual Basic code (Access Basic
' code in Microsoft Access 2.0 and earlier) and the correct record
' navigated to prior to calling the ReadBLOB() function.
'
' ARGUMENTS:
' Source - The path and filename of the binary information
' to be read and stored.
' T - The table object to store the data in.
' Field - The OLE object field in table T to store the data in.
'
' RETURN:
' The number of bytes read from the Source file.
'*************************
Function ReadBLOB(Source As String, T As Recordset, _
sField As String)
Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_ReadBLOB
' Open the source file.
SourceFile = FreeFile
Open Source For Binary Access Read As SourceFile
' Get the length of the file.
FileLength = LOF(SourceFile)
If FileLength = 0 Then
ReadBLOB = 0
Exit Function
End If
' Calculate the number of blocks to read and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' SysCmd is used to manipulate status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
FileLength \ 1000)
' Put first record in edit mode.
T.MoveFirst
T.Edit
' Read the leftover data, writing it to the table.
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter
' Read the remaining blocks of data, writing them to the table.
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter
Next i
' Update the record and terminates function.
T.Update
RetVal = SysCmd(acSysCmdRemoveMeter
Close SourceFile
ReadBLOB = FileLength
Exit Function
Err_ReadBLOB:
ReadBLOB = -Err
Exit Function
End Function
'*************************
' FUNCTION: WriteBLOB()
'
' PURPOSE:
' Writes BLOB information stored in the specified table and field
' to the specified disk file.
'
' PREREQUISITES:
' The specified table with the OLE object field containing the
' binary data must be opened in Visual Basic code (Access Basic
' code in Microsoft Access 2.0 or earlier) and the correct
' record navigated to prior to calling the WriteBLOB() function.
'
' ARGUMENTS:
' T - The table object containing the binary information.
' sField - The OLE object field in table T containing the
' binary information to write.
' Destination - The path and filename to write the binary
' information to.
'
' RETURN:
' The number of bytes written to the destination file.
'*************************
Function WriteBLOB(T As Recordset, sField As String, _
Destination As String)
Dim NumBlocks As Integer, DestFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_WriteBLOB
' Get the size of the field.
FileLength = T(sField).FieldSize()
If FileLength = 0 Then
WriteBLOB = 0
Exit Function
End If
' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' Remove any existing destination file.
DestFile = FreeFile
Open Destination For Output As DestFile
Close DestFile
' Open the destination file.
Open Destination For Binary As DestFile
' SysCmd is used to manipulate the status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, _
"Writing BLOB", FileLength / 1000)
' Write the leftover data to the output file.
FileData = T(sField).GetChunk(0, LeftOver)
Put DestFile, , FileData
' Update the status bar meter.
RetVal = SysCmd(acSysCmdUpdateMeter
' Write the remaining blocks of data to the output file.
For i = 1 To NumBlocks
' Reads a chunk and writes it to output file.
FileData = T(sField).GetChunk((i - 1) * BlockSize _
+ LeftOver, BlockSize)
Put DestFile, , FileData
RetVal = SysCmd(acSysCmdUpdateMeter
((i - 1) * BlockSize + LeftOver) / 1000)
Next i
' Terminates function
RetVal = SysCmd(acSysCmdRemoveMeter
Close DestFile
WriteBLOB = FileLength
Exit Function
Err_WriteBLOB:
WriteBLOB = -Err
Exit Function
End Function
'*************************
' SUB: CopyFile
'
' PURPOSE:
' Demonstrates how to use ReadBLOB() and WriteBLOB().
'
' PREREQUISITES:
' A table called BLOB that contains an OLE Object field called
' Blob.
'
' ARGUMENTS:
' Source - The path and filename of the information to copy.
' Destination - The path and filename of the file to write
' the binary information to.
'
' EXAMPLE:
' CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
'*************************
Sub CopyFile(Source As String, Destination As String)
Dim BytesRead As Variant, BytesWritten As Variant
Dim Msg As String
Dim db As Database
Dim T As Recordset
' Open the BLOB table.
Set db = CurrentDb()
Set T = db.OpenRecordset("BLOB", dbOpenTable)
' Create a new record and move to it.
T.AddNew
T.Update
T.MoveLast
BytesRead = ReadBLOB(Source, T, "Blob")
Msg = "Finished reading """ & Source & """"
Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
MsgBox Msg, 64, "Copy File"
BytesWritten = WriteBLOB(T, "Blob", Destination)
Msg = "Finished writing """ & Destination & """"
Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
MsgBox Msg, 64, "Copy File"
End Sub
Create the following new table and then save it as BLOB:
Table: BLOB
------------------------
Field Name: Blob
Data Type: OLE Object
With the BLOB module open in Design view, click Debug Window (or Immediate Window in Microsoft Access 2.0 or earlier) on the View menu.
Type the following line in the Debug window, and then press ENTER:
CopyFile "c:\windows\winfile.hlp", _
"c:\windows\winfil_1.hlp"
The ReadBLOB() and WriteBLOB() functions copy the Microsoft Windows Help file to the Blob field in the BLOB table and then from there to a disk file called Winfil_1.hlp
The e.g is very big.
Reading, Storing, & Writing Binary Large Objects (BLOBs)
You can store large data objects (such as sound, video, or graphic data) in a field with the OLE Object data type in a Microsoft Access table. Some large binary data objects cannot be represented, however, if they do not have an OLE server that understands the data being stored. You can also store copies of executable program files or other non-OLE data in a Microsoft Access table. This type of data is referred to as a binary large object bitmap (BLOB).
MORE INFORMATION
The following example contains two sample user-defined functions that you can use to manage large amounts of binary data in a field with the OLE Object data type. The user-defined functions are ReadBLOB() and WriteBLOB().
The ReadBLOB() function reads a binary disk file and stores it in an OLE Object field.
The WriteBLOB() function writes binary data stored in an OLE Object field to a disk file.
This example demonstrates how to copy a binary file into an OLE Object field and then write it back out to a new disk file:
Create a new module called BLOB and enter the following lines in the module's Declarations section:
Option Explicit
Const BlockSize = 32768
NOTE: if you are working with Microsoft Access 2.0 you will need to include the following definitions after the two previous ones:
Const dbOpenTable = DB_OPEN_TABLE
Const acSysCmdInitMeter = SYSCMD_INITMETER
Const acSysCmdUpdateMeter = SYSCMD_UPDATEMETER
Const acSysCmdRemoveMeter = SYSCMD_REMOVEMETER
Enter the following code in the module.
NOTE: This following technique will not work for Microsoft Access 1.x. For Microsoft Access 1.x you will need to modify the code so you will use Table variables instead of Recordset variables and the OpenTable function instead of OpenRecordset.
'*************************
' FUNCTION: ReadBLOB()
'
' PURPOSE:
' Reads a BLOB from a disk file and stores the contents in the
' specified table and field.
'
' PREREQUISITES:
' The specified table with the OLE object field to contain the
' binary data must be opened in Visual Basic code (Access Basic
' code in Microsoft Access 2.0 and earlier) and the correct record
' navigated to prior to calling the ReadBLOB() function.
'
' ARGUMENTS:
' Source - The path and filename of the binary information
' to be read and stored.
' T - The table object to store the data in.
' Field - The OLE object field in table T to store the data in.
'
' RETURN:
' The number of bytes read from the Source file.
'*************************
Function ReadBLOB(Source As String, T As Recordset, _
sField As String)
Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_ReadBLOB
' Open the source file.
SourceFile = FreeFile
Open Source For Binary Access Read As SourceFile
' Get the length of the file.
FileLength = LOF(SourceFile)
If FileLength = 0 Then
ReadBLOB = 0
Exit Function
End If
' Calculate the number of blocks to read and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' SysCmd is used to manipulate status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
FileLength \ 1000)
' Put first record in edit mode.
T.MoveFirst
T.Edit
' Read the leftover data, writing it to the table.
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter
' Read the remaining blocks of data, writing them to the table.
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter
Next i
' Update the record and terminates function.
T.Update
RetVal = SysCmd(acSysCmdRemoveMeter
Close SourceFile
ReadBLOB = FileLength
Exit Function
Err_ReadBLOB:
ReadBLOB = -Err
Exit Function
End Function
'*************************
' FUNCTION: WriteBLOB()
'
' PURPOSE:
' Writes BLOB information stored in the specified table and field
' to the specified disk file.
'
' PREREQUISITES:
' The specified table with the OLE object field containing the
' binary data must be opened in Visual Basic code (Access Basic
' code in Microsoft Access 2.0 or earlier) and the correct
' record navigated to prior to calling the WriteBLOB() function.
'
' ARGUMENTS:
' T - The table object containing the binary information.
' sField - The OLE object field in table T containing the
' binary information to write.
' Destination - The path and filename to write the binary
' information to.
'
' RETURN:
' The number of bytes written to the destination file.
'*************************
Function WriteBLOB(T As Recordset, sField As String, _
Destination As String)
Dim NumBlocks As Integer, DestFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_WriteBLOB
' Get the size of the field.
FileLength = T(sField).FieldSize()
If FileLength = 0 Then
WriteBLOB = 0
Exit Function
End If
' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' Remove any existing destination file.
DestFile = FreeFile
Open Destination For Output As DestFile
Close DestFile
' Open the destination file.
Open Destination For Binary As DestFile
' SysCmd is used to manipulate the status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, _
"Writing BLOB", FileLength / 1000)
' Write the leftover data to the output file.
FileData = T(sField).GetChunk(0, LeftOver)
Put DestFile, , FileData
' Update the status bar meter.
RetVal = SysCmd(acSysCmdUpdateMeter
' Write the remaining blocks of data to the output file.
For i = 1 To NumBlocks
' Reads a chunk and writes it to output file.
FileData = T(sField).GetChunk((i - 1) * BlockSize _
+ LeftOver, BlockSize)
Put DestFile, , FileData
RetVal = SysCmd(acSysCmdUpdateMeter
((i - 1) * BlockSize + LeftOver) / 1000)
Next i
' Terminates function
RetVal = SysCmd(acSysCmdRemoveMeter
Close DestFile
WriteBLOB = FileLength
Exit Function
Err_WriteBLOB:
WriteBLOB = -Err
Exit Function
End Function
'*************************
' SUB: CopyFile
'
' PURPOSE:
' Demonstrates how to use ReadBLOB() and WriteBLOB().
'
' PREREQUISITES:
' A table called BLOB that contains an OLE Object field called
' Blob.
'
' ARGUMENTS:
' Source - The path and filename of the information to copy.
' Destination - The path and filename of the file to write
' the binary information to.
'
' EXAMPLE:
' CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
'*************************
Sub CopyFile(Source As String, Destination As String)
Dim BytesRead As Variant, BytesWritten As Variant
Dim Msg As String
Dim db As Database
Dim T As Recordset
' Open the BLOB table.
Set db = CurrentDb()
Set T = db.OpenRecordset("BLOB", dbOpenTable)
' Create a new record and move to it.
T.AddNew
T.Update
T.MoveLast
BytesRead = ReadBLOB(Source, T, "Blob")
Msg = "Finished reading """ & Source & """"
Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
MsgBox Msg, 64, "Copy File"
BytesWritten = WriteBLOB(T, "Blob", Destination)
Msg = "Finished writing """ & Destination & """"
Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
MsgBox Msg, 64, "Copy File"
End Sub
Create the following new table and then save it as BLOB:
Table: BLOB
------------------------
Field Name: Blob
Data Type: OLE Object
With the BLOB module open in Design view, click Debug Window (or Immediate Window in Microsoft Access 2.0 or earlier) on the View menu.
Type the following line in the Debug window, and then press ENTER:
CopyFile "c:\windows\winfile.hlp", _
"c:\windows\winfil_1.hlp"
The ReadBLOB() and WriteBLOB() functions copy the Microsoft Windows Help file to the Blob field in the BLOB table and then from there to a disk file called Winfil_1.hlp
ASKER
WOW!!! Sorry it took so long... I've been on a business trip to Mauai. Thanks a ton!!! and here's the 400 points I promised for a complete answer.
Again, Wow!!!
Again, Wow!!!
ASKER
Hmmmm... agarwal_rahul, did you get 400 points. I increased it and then sent it along, but I don't think MY points decreased by 400. Let me know and I'll try to rectify if needed.
I think I got the 400 points. Thanks a lot.
First Way:
Have one more field in database 'Object Type'. Which stores whether it is excel or word doc and another field stores the file name associated. So when you click at a node on TreeView control you can just show values from file_name field and when you somebody double-click at a value in ListView control you actually load the object.
Sample code for loading
if rs!ObjectTYpe="DOC" then
set objword=new word.application
objword.open rs!filename
else