Pass an ADODB.Stream to an Excel object

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

LVL 26
Alan WarrenApplications DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flavoCommented:
Heya Alan!

You do know you can connect to Excel with ADO

http://www.connectionstrings.com/
0
will_scarlet7Commented:
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
flavoCommented:
Will,

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

Dave
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alan WarrenApplications DeveloperAuthor Commented:
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
will_scarlet7Commented:
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
will_scarlet7Commented:
Sorry Alan, this seems more advanced than my amateur knowledge.
0
flavoCommented:
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
flavoCommented:
>> 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
flavoCommented:
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
Alan WarrenApplications DeveloperAuthor Commented:
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
flavoCommented:
>>I believe .net can do it - sheesh!

You can call a managed dll from Acces..
0
Alan WarrenApplications DeveloperAuthor Commented:
hehe, can ya whip one up for me Dave
0
flavoCommented:
0
flavoCommented:
I have some stuff to do here, but i sure could if you can wait till tomorrow night..
0
Alan WarrenApplications DeveloperAuthor Commented:
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
flavoCommented:
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
flavoCommented:
Just remembered about this... Still having trouble Alan?
0
flavoCommented:
No luck Alan.....

;-(
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alan WarrenApplications DeveloperAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.