OLE obj to C drive through code

[Guest]

Background:
I've developed a macro that - when called from the Main Form - asks the user
to locate the "Template File", and then it runs some complicated queries to
populate the Template File. The Template File is then formated, renamed and
saved.

The problem is that this database is being passed around like the plague
(jumping from computer to computer as the different users add data to it),
and the template file is being forgotten.

For this reason I've crated a seperate table to house the template file and
saved the Excel File as an OLE object.

The Question:
How do I save the Excel file that's stored as an OLE object in a table
directly to the C drive?

 

[John Nurick]

Hi Hafeez,

I think there are two ways to go (without passing round more than one
file).

1) With the Excel template embedded in an OLE field, the only practical
way of extracting it to a disk file is to put a BoundObjectFrame control
on a form and then manipulate the controls' Verb and Action properties.
This article contains crude, antiquated sample code (I'm going to
suggest it be updated) which should get you started. It refers to Word
but can be modified for Excel:
http://support.microsoft.com/default.aspx?scid=kb;en-us;132003&Product=acc

2) For your purposes I think it would be much simpler not to _embed_ the
Excel workbook in an OLE field (with the complicated procedure for
getting it back to a disk file), but instead to store the Excel file's
_contents_ in an OLE field. The difference is that the latter does not
store all the ancillary embedding stuff, such as an image of the first
sheet of the workbook.

More importantly, writing the file to disk is a matter of doing just
that: you don't have to muck round with a form and control and instance
of Excel.

To store the file contents, use the old Basic binary file i/o statements
to read the file into a string (in one go if it's reasonably small, in
chunks if it's too large), and then AppendChunk() to push it into the
field. To write it back to disk, use GetChunk() to extract the data from
the field and then the Basic i/o system.

 

[Guest]

Thanks for the quick response John!
I've searched through the help and couldn't find anything on binary file
i/o. The template file contains 6-8 worksheets with less than 50 cells per
sheet worth of custom formatting - I hope that's small enough to read in all
at once.

Any suggestions on where I can find help with Implementation Strategy #2?

Thanks
Hafeez

 

[John Nurick]

Hi Hafeez,

Try looking in Help for
Open Statement
Input Function
Print # Statement

The code to read the contents of a file into a string variable will look
something like this:

Dim strFilespec As String
Dim strFileContents As String
Dim lngFN as Long

strFileSpec = "C:\folder\file.xls"
lngFN = FreeFile()
Open strFileSpec For Input As #lngFN
strFileContents = Input(LOF(lngFN), #lngFN)

'Now use AppendChunk to insert strFileContents into
'a memo field; also remember to store the filename
'in a text field in the same record.
...

Close #lngFN

There should be no problems processing files of a megabyte or more in
one chunk.

 

[Stephen Lebans]

There's code in my SaveRestoreRelationShipsWIndow MDB that shows how to
Get Long Binary data directly from a Recordset.
A search on GoogleGroups for my name and PUT or GET will show you how to
Put or Get data from a long Binary field or a file on disk.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Guest]

Hi All,

Thanks to your help, I've successfully been able to store my Excel template
file as a string in a record, however, I'm not able to get it back...

Code for storing file in table
Dim strFilespec As String
Dim strFileContents As String
Dim lngFN As Long

strFilespec = Me.Import_Path.Value
lngFN = FreeFile()
Open strFilespec For Binary As #lngFN
strFileContents = Input(LOF(lngFN), #lngFN)

If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
End If

rst.AddNew
With rst
.Fields("Name").Value = Me.Name
.Fields("Version").Value = Me.Version.Value
.Fields("Modified").Value = Me.Modified.Value
.Fields("Import_Path").Value = Me.Import_Path.Value
.Fields("Output_Path").Value = Me.Output_Path.Value
.Fields("Modifier").Value = Me.Modifier.Value
.Fields("Template").AppendChunk strFileContents
.Update
End With

Close #lngFN
Call cmd_SaveRec_Click
end code

code for retreiving file from table
Dim a() As Byte
Dim lngOLEValue As Long
Dim lngFN As Long
Dim strTemp As String

lngFN = FreeFile()
lngOLEValue = LenB(Me.Template.Value)
ReDim a(0 To lngOLEValue) ' should be -1

' Copy the contents of the OLE field to our byte array
a = Me.Template.Value

strTemp = "OLEfieldTestExcel" & ".xls"
Open strTemp For Binary Access Write As #lngFN
Put #lngFN, , a
Close #lngFN
end code

I tried to adapt Stephen's code from the "Displaying Binary Data" Post but I
have failed. I understand what I am doing to store it in the table, but I
don't understand what I need to do to get it back to disk.
Please help

Thanks

There's code in my SaveRestoreRelationShipsWIndow MDB that shows how to
Get Long Binary data directly from a Recordset.
A search on GoogleGroups for my name and PUT or GET will show you how to
Put or Get data from a long Binary field or a file on disk.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Stephen Lebans]

I never used a string to store the data.
I never used Input in any of my examples.

Stay with straight Binary data. Use Get and Put to move the data. Look
at the samples again I pointed you to.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

Hi All,

Thanks to your help, I've successfully been able to store my Excel template
file as a string in a record, however, I'm not able to get it back...

Code for storing file in table
Dim strFilespec As String
Dim strFileContents As String
Dim lngFN As Long

strFilespec = Me.Import_Path.Value
lngFN = FreeFile()
Open strFilespec For Binary As #lngFN
strFileContents = Input(LOF(lngFN), #lngFN)

If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
End If

rst.AddNew
With rst
.Fields("Name").Value = Me.Name
.Fields("Version").Value = Me.Version.Value
.Fields("Modified").Value = Me.Modified.Value
.Fields("Import_Path").Value = Me.Import_Path.Value
.Fields("Output_Path").Value = Me.Output_Path.Value
.Fields("Modifier").Value = Me.Modifier.Value
.Fields("Template").AppendChunk strFileContents
.Update
End With

Close #lngFN
Call cmd_SaveRec_Click
end code

code for retreiving file from table
Dim a() As Byte
Dim lngOLEValue As Long
Dim lngFN As Long
Dim strTemp As String

lngFN = FreeFile()
lngOLEValue = LenB(Me.Template.Value)
ReDim a(0 To lngOLEValue) ' should be -1

' Copy the contents of the OLE field to our byte array
a = Me.Template.Value

strTemp = "OLEfieldTestExcel" & ".xls"
Open strTemp For Binary Access Write As #lngFN
Put #lngFN, , a
Close #lngFN
end code

I tried to adapt Stephen's code from the "Displaying Binary Data" Post but I
have failed. I understand what I am doing to store it in the table, but I
don't understand what I need to do to get it back to disk.
Please help

Thanks

 

[Guest]

Hi Stephen,

The project I was working on is still on the back burner, but I've finally
managed to get around to looking at it one more time.
I've changed my code to match your example...

Dim arry_byte() As Byte
Dim lngTemplate As Long
Dim strFName As String
Dim lngFNumber As Long

lngFNumber = FreeFile()

lngTemplate = LenB(Me.Template.Value)
ReDim arry_byte(0 To lngTemplate) ' should be -1

' Copy the contents of the OLE field to our byte array
arry_byte = Me.Template.Value

strFName = Me.Name.Value & " V" & Me.Version.Value & ".xls"
Open strFName For Binary Access Write As #lngFNumber
Put #lngFNumber, , arry_byte
Close #lngFNumber

but I can't test it because I don't know how to get binary data in a record
in the first place :-s

I've downloaded the database you reffered to (save relationshipswindow) but
didn't find any example of Get/Put (just getchunk). I've searched google
groups and your website and have come across one routine I think I'll need to
adapt (Private Function ReadIconFile() As String). What should the data type
in my table (where I am "PUT"ting) the data be?

Hafeez

 

[Stephen Lebans]

Here's code to read a file from disk.



Private Function fReadFile(fname As String) As String


If Len(fname & vbNullString) = 0 Then
MsgBox "You Must Select a File!", vbCritical
fReadFile = ""
Exit Function
End If


Dim InFileData() As Byte
Dim FileNumIn As Long

FileNumIn = FreeFile

Open fname For Binary Access Read As FileNumIn

Dim FileLengthExtra As Long
' Get length of file.
FileLength = LOF(FileNumIn)

Dim InFileData() As Byte

ReDim InFileData(1 To (FileLength )) as Byte
' fills array with contents of original file
Get FileNumIn, , InFileData
Close FileNumIn


Me!NameOfYourField = InFileData
fReadFile = fname

' End of code snippet


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.