Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pass an ADODB.Stream to an Excel object

Posted on 2004-10-25
19
Medium Priority
?
2,452 Views
Last Modified: 2012-05-05
Hi

Anyone know how to pass an ADODB.Stream to an Excel object without using the .SaveToFile method?

Function getTempFolder() As String
  Dim fso As FileSystemObject
  Set fso = New Scripting.FileSystemObject
  getTempFolder = fso.GetSpecialFolder(TemporaryFolder).Path
  Set fso = Nothing
End Function


Public Sub adoStream2Excel()

Dim mstrPath As String

  Dim objRs As ADODB.Recordset
  Dim objFile As Scripting.File
 
  Dim objXl As Excel.Application
  Dim objWkb As Excel.Workbook
  Dim objSht As Excel.Worksheet
 
  Dim strSQL As String
  Dim objStream As ADODB.Stream

  If gsTempFolder = "" Then
    gsTempFolder = getTempFolder & "\"
  End If

  strSQL = "SELECT ID, Filename, [Size], Created, FileBinary FROM tblFiles Where ID=5"
 
  Set objRs = New ADODB.Recordset

  objRs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

  With objRs

    While Not .EOF
      Set objStream = New ADODB.Stream
      With objStream
        .Type = adTypeBinary
        .Open
        If Not IsNull(objRs.Fields("FileBinary").Value) Then
          .Write objRs.Fields("FileBinary").Value
         
          ' instantiate an excel object and throw the stream at excel
          Set objXl = CreateObject("EXCEL.APPLICATION")
          Set objXl = New Excel.Application
          objXl.DisplayAlerts = False
          objXl.Visible = False
          Set objWkb = objXl.Workbooks.Open(objStream.Read)    ' this bit
          With objXl
            .Visible = True
            With objWkb
            End With
          End With
          ' .SaveToFile gsTempFolder & objRs.Fields("Filename").Value, adSaveCreateOverWrite
          ' mstrPath = gsTempFolder & objRs.Fields("Filename").Value
        End If
        .Close
      End With
      .MoveNext
    Wend
    .Close
  End With

End Sub



Alan

0
Comment
Question by:Alan Warren
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 3
19 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12398542
Heya Alan!

You do know you can connect to Excel with ADO

http://www.connectionstrings.com/
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12398587
Alan,

What about using the Print # method? That will out put your data to in raw format to whatever file name you give it, so if your ADODB.Stream is a Excel document that you've stored in your table (That is my guess from your code) and you print the value to a file, I think it would output an excel file.

Something like this:

Open "C:\YourExcelFile.xls" For Output As 1
Print #1, objStream
Close 1
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398608
Will,

hmm... Im guessing that will only work for a csv?? but ive been wrong before (oh sooo many times!)

Dave
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 26

Author Comment

by:Alan Warren
ID: 12398623
Hi Dave,

Yeah thats not what I am looking for mate, well I dont think so anyhow.

The Excel file is in the stream, If  I was using ASP I could Response.BinaryWrite objRs.Fields("FileBinary").Value and have the workbook open in the browser.

Looking for a way to cut out the browser, go straight to the Excel object that is instantiated WithEvents so I can do stuff like update the BLOB when user closes the Excel object.


Sam, I can use the Stream.SaveToFile method to do the same, was hoping to do this without actually creating a file in the local filesystem.
I believe .net can do it - sheesh!


Alan
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12398629
Hi Dave,
Looks like you made it to this side of the world. Hope the wheather is treating you well.

I don't know about using the Print # method for creating binary objects, but I have used it for creating .rtf files from rtf fields. I am just as likely (or more) as you to be wrong on this issue though since I have not tried it.

God bless!

Sam
0
 
LVL 15

Assisted Solution

by:will_scarlet7
will_scarlet7 earned 800 total points
ID: 12398640
Sorry Alan, this seems more advanced than my amateur knowledge.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398643
I had an app i did that connected to Excel an got the data into Access... I dont have it on me (and i'm teriable with ADO sorry), but all you need to do is connet to Excel and use it just like Access

The table name = sheet name + $ tacked onto the end.

Field name is the top row.

You can then run a Update / Append query on it!

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398652
>> Looks like you made it to this side of the world. Hope the wheather is treating you well.

Not yet mate... February... Still in Brisbane, damn HOT Brisbane.. It was 34 today... really sticky too..

>> Sorry Alan, this seems more advanced than my amateur knowledge.

i wouldnt say that mate, just something that you have yet to deal with... :-)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398661
hmmm.. looks like i didnt read you Q right Alan... Forgot about the blobs and the like...  maybe i too have left my small "realm" of knowledge...
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 12398693
Beyond me too guys, thats why I asked the Q.

I do vaguely remember doing something like this once before, I remember being pleased with myself that I did it without writing to disk, but I cant remember if it was Excel or XML.

Alan

0
 
LVL 34

Expert Comment

by:flavo
ID: 12398713
>>I believe .net can do it - sheesh!

You can call a managed dll from Acces..
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 12398719
hehe, can ya whip one up for me Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398720
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398723
I have some stuff to do here, but i sure could if you can wait till tomorrow night..
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 12398734
Im in no rush Dave, but before you spend too much time on it, check back here and see how things are progressing.

Alan
0
 
LVL 34

Expert Comment

by:flavo
ID: 12398748
It will be good to do something else... Nearly finished the job im doing now, maybe 3-4hrs work to go...

Access BE with VB .Net FE with CR reports...  Great learning exp...  After about 1yr with .Net i can finally do things in about the same time i can in VB 6, if not faster... :-)

0
 
LVL 34

Expert Comment

by:flavo
ID: 12532316
Just remembered about this... Still having trouble Alan?
0
 
LVL 34

Accepted Solution

by:
flavo earned 1200 total points
ID: 12592374
No luck Alan.....

;-(
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 12688144
Hi fellas,

it's beyond me too - will let you know if ever I sort it out, thanks for your input, most appreciated.


Alan ":0)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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