Retrieving .xls object from Access DB

L

luckygeekboy

New member
Joined
Oct 20, 2003
Messages
4
Location
Cleveland, Ohio
How can I set a reference to a workbook returned from a query against an Access 2002 DB containing Excel workbooks stored as .xls files?

I have manually populated a table field of data-type: OLE Object with an Excel spreadsheet from within Access 2002.

I just have no idea how to deal with the recordset obj that is returned when I query the field. Here is what I initially tried (ugly as it is):

PHP: 
objWorksheet = CType(objRS.Fields("ExcelTemplate1").Value, Excel.Workbook).Worksheets(1)

This returned an invalid cast exception...

So I tried this to see what I was getting back from the Query:
PHP: 
 Dim obj As Object = objRS.Fields("ExcelTemplate1").Value

Upon examining the resulting Obj, it was basically as array of digits.

I should perhaps add that Im using VB.NET 2003, OfficeXP, with MSs OfficeXP PIAs.

Ive researched this problem fairly vigorously without success, so I would appreciate any help that anyone can provide.

Thank You!
 
The cast exception is caused because you are returning a BLOB from the db. I dont know the exact conversion code but look for formatting blob data in Office 2003 API help or msdn. I know there are examples for images, the same format / code should hold true for excel, with a few refference changes ala (??? as Image = ??? as ExcelFile?).
 
Yes, Ive found a few examples dealing with converting blobs to .bmps, etc...

I have also found that Access does not return blobs sequentially, like SQL does, but as one array of bytes. Trying what I found pertaining to .bmps, this is what Ive come up with:

PHP: 
Dim fs As New FileStream("C:\Quotes\Test.xls", FileMode.CreateNew)
 Create the writer for the blob data
        Dim w As New BinaryWriter(fs)
        Dim arr() As Byte
        While Not objRS.EOF
            arr = objRS.Fields("ExcelTemplate1").Value
            objRS.MoveNext()
        End While

         Write data to Test.xls
        Dim i As Integer
        For i = 0 To arr.Length() - 1
            w.Write(arr(i))
        Next
        w.Close()
        fs.Close()

This creates a file that is roughly 3X larger than it should be. I can see remnants of the original Excel file, but most of it is garbage. Im not sure if Access returns header information, requiring an offset, or some other technique that is not easily stumbled upon.
Any ideas??? Ive spent hours researching this online, and still nodda... Please feel free to share any ideas...
 
<<<
How can I set a reference to a workbook returned from a query against an Access 2002 DB containing Excel workbooks stored as .xls files?
>>>
Could you please clarify your question/task - Im unsure I understand what is requested?:

- 1. youve an MS Access 2002 database...
- 2. ...this database has a table with OLE field...
- 3. ... you store MS Excel workbooks (.xls files) into this OLE field - one workbook per one row of your table...
- 4. ...now you wanted to open and manipulate MS Excel Workbook stored in, say, the OLE field of the first row of MS Access table...

Something like that or I completely missing what is requested?

Shamil
 
Shamil, Id be happy to clarify what Im trying to do...

What you wrote is exactly what I would like to do. I manually (using point & click within Access) fill one OLE field with a .xls file. I then connect to the db via VB.NET code, query the table to return a one record recordset containing that single OLE table field. When I examine the recordset object, it contains a one dimension array containing integers. My problem is that I dont know what to do next to open this recordset object in Excel in its original spreadsheet form?

I have researched this tremendously all over the net and any book that I could find at the bookstore. The only information that Ive found says that Access adds a custom header to its OLE fields that must be removed, but since its a proprietary header, no one knows how to.

Any suggestions wil be greatly appreciated!

Thank You for your time!

Richard
 
Richard,

What you see in internal representation of MS Excel workbook (or its link - did you insert a link to MS Excel workbook into OLE field?).

Please clarify more what is your task? - it may happen that you will not need to store MS Excel workbooks in MS Access tables - this way you "bloat" MS Access database and there should eb a clear reason to do that or not.

Shamil
 
Shamil,

Sorry for the delay in responding back to you... It has been a very busy time, and I simply lost track of time.

Yes, I put the actual workbook file (.xls) into an OLE Object field, not a link to the file.

You are correct, such files will bloat the DB. I will share with you my reasons for wanting to place them in the DB, as I believe that you may be correct in supposing that this may not be the best solution.

The application itself requires multiple Word or Excel templates, which become populated using MS automation at run time. These templates are health insurance forms that contain an extensive amount of formatting. They will also require periodic updating, once the application is released. There are three primary reasons I considered placing them within the applications DB, which the application requires regardless of where the templates go.

One, since they are proprietary, I want to ensure that they could not be accessed in their native state by someone looking for a shortcut to duplicating the applications function. Placing them in a locked DB would do that.

Second, I want to eliminate the possibility that the templates could be accidentally written over, once they had data placed into them, something that can easily happen. I also want to protect them from being accidentally erased by the user.

Third, when a change requires that some templates be updated, I felt it would be easiest to update them on each clients machine by creating an update application available over the internet, or on a CD, that would simply replace each file within the users DB.

I should also let you know that this application will be sold to insurance agents for use in their business. Therefoe, after the sale, I will have no control over the environment in which the software will be used. Thus, I need to bundle and protect the templates from both piracy and accidents, while at the same time making them easily updatable.

Thanks again, I appreciate the thoroughness with which you are regarding my question!

As an aside, tell me how the weather is St Petersburg? It is well below 0C here in Cleveland, and the snow will be coming soon!

Regards

- Richard
 
Last edited by a moderator:
Back
Top