Solved

How to read Excel Sheet saved in Access Database from VB6 front end

Posted on 2001-07-26
28
277 Views
Last Modified: 2007-12-19
I created a table with One field as "ole object".
Opened an excel document and copied some cells and pasted on the field as first record.
saved and closed the mdb.

NOW, I want to read the field and assign to an Object of type WorkSheet, How to do it.

SOS
0
Comment
Question by:ashunigam
  • 12
  • 10
  • 5
  • +1
28 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6322656
Here is an example of Access to Excel
http://www.microsoft.com/accessdev/articles/comothap.htm#Setting

Sub FindMedian()
   Dim appXL As Excel.Application
   Dim dbs As Database, rst As Recordset
   Dim strSQL As String, intI As Integer
   Dim sngArray() As Single, sngMedian As Single

   ' Create SQL string.
   strSQL = "SELECT DISTINCTROW UnitPrice FROM Products ORDER BY UnitPrice;"
   ' Return reference to current database.
   Set dbs = CurrentDb
   ' Open dynaset-type recordset.
   Set rst = dbs.OpenRecordset(strSQL)
   ' Populate recordset.
   rst.MoveLast
   ' Return to beginning of recordset.
   rst.MoveFirst
   ' Redimension array based on recordset size.
   ReDim sngArray(0 To rst.RecordCount - 1)
   ' Populate array with values from recordset.
   For intI = 0 To UBound(sngArray)
      sngArray(intI) = rst!UnitPrice
      rst.MoveNext
   Next
   ' Create new instance of Excel Application object.
   Set appXL = CreateObject("Excel.Application")
   ' Pass array to Excel's Median function.
   sngMedian = appXL.Application.Median(sngArray())
   Debug.Print sngMedian
   ' Close Excel.
   appXL.Quit
   ' Free object variables.
   Set appXL = Nothing
   Set dbs = Nothing
End Sub

0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6322664
Good samples here

ACC: Using Automation to Transfer Data to Microsoft Excel
http://support.microsoft.com/support/kb/articles/Q129/3/04.ASP

0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6322685
SO if I understand, you have an access table with an ole object field. You need to place an OLE control on a form and open a recordset to the access table, bind the ole control to the recordset that you create and the OLE object will be displayed in the ole control, you can use the .DoVerb "Edit" method of the ole control to make this an editable object.

Personally though I would always recommend against storing such objects in a table. It tends to get messy when attempting to use them and also it tends to bloat the database considerably. My personal preference would be to store the filename only in a field and use GetObject to instantiate the file using the original application. This way it is much easier to keep control of. The only problem then of course is that the file can be deleted outside of the application/database but that can usually be handled by setting appropriate permissions on the folder if running NT/2000.
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6322763
Hi Tim Cottee

I am saving it to database as I don't want it to be modified by the user. My OS is NT4.

Could you provide some code snippet to connect the field with Ole control.

And

Is here any way to avoid ole control and do someting like
set Excel.Application.Workbooks("Ttt").Sheets("XXX") = RS(0).xxx

Thanks
Ashutosh

0
 
LVL 3

Author Comment

by:ashunigam
ID: 6322778
read
set Excel.Application.Workbooks("Ttt").Sheets("XXX") = RS(0).xxx

As
dim oExcel as new Excel.Application
set oExcel.Workbooks("Ttt").Sheets("XXX") = RS(0).xxx

0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6322789
ashunigam, Look at the second link I posted, it has everything you need!  :)
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6322795
Private Sub Command0_Click()

      Dim DB As Database, Rs As Recordset
      Dim i As Integer, j As Integer
      Dim RsSql As String
      Dim CurrentValue As Variant
      Dim CurrentField As Variant
      Dim Sheet As Object

      Set DB = DBEngine.Workspaces(0).Databases(0)

      ' *** Use the following line for Microsoft Access 7.0 only ***
      ' RsSql = "SELECT * FROM [Order Details] WHERE [OrderId]<10249;"

      ' *** Use the following line for Microsoft Access 2.0 only ***
      ' RsSql = "SELECT * FROM [Order Details] WHERE [Order Id]<10001;"

      Set Rs = DB.OpenRecordset(RsSql, DB_OPEN_DYNASET)
      Set Sheet = CreateObject("Excel.Sheet")

      j = 1

      ' Loop through the Microsoft Access field names and create
      ' the Microsoft Excel labels.
      For i = 0 To Rs.Fields.Count - 1
        CurrentValue = Rs.Fields(i).Name
        Sheet.cells(j, i + 1).Value = CurrentValue
      Next i

      j = 2

      ' Loop through the Microsoft Access records and copy the records
      ' to the Microsoft Excel spreadsheet.
      Do Until Rs.EOF
         For i = 0 To Rs.Fields.Count - 1
            CurrentField = Rs(i)
         Sheet.cells(j, i + 1).Value = CurrentField
      Next i

         Rs.MoveNext
         j = j + 1
      Loop

      ' Print the Microsoft Excel spreadsheet.
      Sheet.PrintOut
      Set Sheet = Nothing

    End Sub
In Microsoft Access 97:
      Private Sub Command0_Click()

      Dim DB As Database, Rs As Recordset
      Dim i As Integer, j As Integer
      Dim RsSql As String
      Dim CurrentValue As Variant
      Dim CurrentField As Variant
      Dim Workbook As Object
      Dim xlApp As Object
      Dim Sheet As Object

      Set DB = DBEngine.Workspaces(0).Databases(0)

      RsSql = "SELECT * FROM [Order Details] WHERE [OrderId]<10249;"

      Set Rs = DB.OpenRecordset(RsSql, DB_OPEN_DYNASET)
      Set xlApp = CreateObject("Excel.Application")
      xlApp.workbooks.Add
      Set Sheet = xlApp.activeworkbook.sheets(1)
      j = 1

      ' Loop through the Microsoft Access field names and create
      ' the Microsoft Excel labels.
      For i = 0 To Rs.Fields.Count - 1
          CurrentValue = Rs.Fields(i).Name
          Sheet.cells(j, i + 1).Value = CurrentValue
      Next i

      j = 2

      ' Loop through the Microsoft Access records and copy the records
      ' to the Microsoft Excel spreadsheet.
      Do Until Rs.EOF
          For i = 0 To Rs.Fields.Count - 1
              CurrentField = Rs(i)
              Sheet.cells(j, i + 1).Value = CurrentField
          Next i
          Rs.MoveNext
          j = j + 1
      Loop

      ' Print the Microsoft Excel spreadsheet.
      Sheet.PrintOut

      ' Close workbook without saving.
      xlApp.activeworkbook.saved = True
      Set Sheet = Nothing
      xlApp.Quit
      Set xlApp = Nothing

    End Sub
0
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 300 total points
ID: 6323027
hearing...
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6323099
Hi Dave

I have Excel worksheet saved inside database. My problem is to retreive it and use it.

In your solution you are getting data from database and and assigning to instance of Excel application.

In my case the data as well as format of the excel is saved in database, whereas, and in your solution the data is saved and excel sheet is probably in xls file.

Hope I am able to clarify my problem.

Waiting for solution.
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6323893
So you saved the sheet as a data object?
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6323945
Yes.

To replicate it you need to Create a table with one field Ole Object type. and you can save the excel sheet there.

I would be highly oblidged if somebody could give me a code to retrive the field and assign to object pointer.

If you check the data returned by the Recordset object, it is binary code for the xls file.

Can anybody help me out.....experts! I am sure somebody can.
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6324044
One more thing I am using ADO and the mdb file is having "Database Password"
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6324574
If it is binary, why don't you save to disk and open from there?
I mean, store the contentents of filed in a byte array variable and wripte it to a file in disk, something like...


dim xlsBinary() as byte

'save the contents of field to xlsBinary

dim ff as integer

ff=freefile
open "c:\myexls.xls" for binary as #ff
   put #ff,,xlsBinary
close #ff

Am i correct?
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6324580
where reads....
"I mean, store the contentents of filed in a byte array variable and wripte it to a file in disk, something
like..."


should read:
I mean, store the contents of field in a byte array variable and write it to a file in disk, something
like...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:ashunigam
ID: 6327120
Hi Richie

Saving to file and Ole control(Tim's suggestion) are wise suggestion. But don't you think there should be a way to do it more elegantly?

In tim's suggestion I had 2 problems
1) It requires DAO control and I am not keen on adding Database control to my form and I don't know how to supply Database Password in DAO. (If somebody could tell me how to do this bit [Taking care of Database Password] could give some points as thanks too, as I am feeling to lazy to try it)

2) I personally don't like OLE control (It is not a very serious problem ;) )

If you could find a way to do it would be great achivement for you and a great help to me. I am trying feverishly for last 2 days, but some thing is missing ....

NOTE: To apply database password, you need to open mdb file in Exclusive mode. Then Goto Tools\Security\Set Database Password and do it.
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6327141
It would be great If you could provide some working sample for whatever way you suggest.

Thanks
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6327494
1) To your opendatabase method add this line on the option parameter:
";pwd=password"
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6327531
I disagee with ole field also.

For password with ADO, there is a link:

http://support.microsoft.com/support/kb/articles/Q240/2/22.asp
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6327710
and...
Placing a file into an Access database:
http://www.developersdomain.com/vb/codesnippets/database.htm
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6327736
"NOW, I want to read the field and assign to an Object of type WorkSheet, How to do it."
Using code from last link you could do something like this:

'set a reference to microsoft excel object library

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook

Set xlApp = New Excel.aplication

' here use getBLOB function and
' saved file. I used myworkbook.xls, you use yours
Set wb = xlApp.Workbooks.Open("c:\myworkbook.xls")

0
 
LVL 3

Author Comment

by:ashunigam
ID: 6328807
Hi Ritchie

See, this OLE component is assigning object to itself directly using "Database Control". So internally it is using some mechanism where it does not need to save file to disk (I hope).

If we dig in more we could use similar mechanism to retrieve data directly to Excel.Workbook object.

If you feel it is too difficult and point are not worth justifying the efforts then I can increase points.

Please try to do it on your machine with one table having excel sheet in it (the method to do it I already explained and is explained in Help for Access)

Waiting.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6329296
There's no problem regarding points.
I will give a shoot.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6329319
One question:
If Ole object is what you use in front end to display excel sheet, it doesn't supports ADO data control, you are limited to old data control (DAO) only.
I did this test:
Create a database and a table. Table has two fields:
Name (text): put some text here
OLEObj (Ole): Pasted partially copied excel sheet

If you try to link ADO Data Control to those fields, OLE control doesn't works and you get an error.
If you use old Data Control, and links both fields, it show your excel sheet stored in table of database.

So, what we wanna do...?
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6330571
Yes with OLE object's we need Database object control which apparently uses DAO. This is one of my objections.
Overthat the other point I don't like is the way it works, ie you double click it and you get its borders ugly like it is in design time (So does not feel seamless)

BUT, what can be done by a control and DAO can be done with ADO (Its my feeling, in case MS has not degraded the ADO in power in this reference)

Thanks
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6330631
"ie you double click it and you get its borders
ugly like it is in design time (So does not feel seamless)"
That's the point! So Ole Control works! If you double click on it, you could edit the contents.

OLE control doesn't support ADO. Sorry.
May be displaying the contents (for that field) in datagrid control.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6330634
0
 
LVL 3

Author Comment

by:ashunigam
ID: 6337977
I think it is a too heavy a problem for the level of expertise of experts in this forum.

As a thanks to taking interest in my problem I am giving points to Richie.

Giving average grade because the effort put to resolve the problem was superficial (searching possible solution over web and just posting the Ideas without testing, Incedently the Ideas were already tried by me like Tim cotee's OLE control and Ritchie's saving to file, no body was willing to even write a small code to test their Idea's functioning)

Some of the experts didn't take the pain to understand the problem even and posted the solution :)

I got a better round about to my problem. But still wondering how to do it without a round about in VB6.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6340715
There is no "direct" solution to your problem since we are working with a "file" and not a simple data.
Maybe with an api like Copymemory we could get this work but i'am not sure.
The approach "savetodisk" would work fine but if the end user "rolls" over the recorset with movenext-moveprevious rapidly, we get a performance hit.
I suggest you not to show the excel file by default, instead let end-user the oprtunity to choose, maybe with a button.
It's a pity to hear that we didn't put effort on your problem.
Cheers
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now