Solved

Pass an ADODB.Stream to an Excel object

Posted on 2004-10-25
2,382 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
Question by:Alan Warren
    19 Comments
     
    LVL 34

    Expert Comment

    by:flavo
    Heya Alan!

    You do know you can connect to Excel with ADO

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

    Expert Comment

    by:will_scarlet7
    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
    Will,

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

    Dave
    0
     
    LVL 26

    Author Comment

    by:Alan Warren
    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
    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
    Sorry Alan, this seems more advanced than my amateur knowledge.
    0
     
    LVL 34

    Expert Comment

    by:flavo
    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
    >> 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
    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
    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
    >>I believe .net can do it - sheesh!

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

    Author Comment

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

    Expert Comment

    by:flavo
    0
     
    LVL 34

    Expert Comment

    by:flavo
    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
    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
    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
    Just remembered about this... Still having trouble Alan?
    0
     
    LVL 34

    Accepted Solution

    by:
    No luck Alan.....

    ;-(
    0
     
    LVL 26

    Author Comment

    by:Alan Warren
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    857 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

    18 Experts available now in Live!

    Get 1:1 Help Now