Saving user data in an Excel workbook

Question

Is it possible to save a large amount of data (about 1-2 mb) in an Excel workbook?

Ideally, this data should be tied to a worksheet in a book.
CustomProperties are unlikely to support big data.

My data can be presented in the following forms: binary, xml, string.

Thanks...

Answer 1

Yes, you can store string and XML in Excel cells. For binary, you'd better not save it in Excel, but if you needed OLE (object binding and nesting) then be an option. You can do this by saving the binary as a file outside of Excel and then pasting it as an OLE object:

Dim mySht As Worksheet Dim oleFileName as String oleFile = "MyXmlDoc.xml" Set mySht = ActiveWorkbook.ActiveSheet mySht.Shapes.AddOLEObject Filename:=Environ$("Appdata") & _ "\MyFolder\" & oleFile, _ Link:=False, DisplayAsIcon:=True 

This worked for us for certain types of generic file type. But we never did this for raw binary data. Usually we put a Word or PDF document in a spreadsheet. You also risk corrupting the book or losing binary data. In our case, the OLE object will be clicked by a user who had Wordperfect instead of Word, or they were running Linux / Mac, and the embedded document did not open.

This makes the Excel file pretty large with every added object added. This is an old technology with its quirks.

Answer 2

You can add a VBA module to the workbook for your data and encode your data in regular ASCII strings (for example, using Base64 encoding). The receiving code will look like this:

 Dim x(1000) As String Sub InitData() x(0) = "abcdefghijk...." x(1) = "123456789......" '...' End Sub 

You can also save these lines in a worksheet rather than in a VBA module if you prefer this.

To perform encoding / decoding, look here:

How can I base64 efficiently encode a string using Excel VBA?

Base64 Encode String in VBScript

http://www.source-code.biz/snippets/vbasic/12.htm

Answer 3

If your information goes to files, you can read the file in binary mode and put it in a cell. Then at the end or the beginning you save your file name so that you can easily restore the file. Here is the code to get you started:

 Sub readBin() Dim iFreeFile As Integer, bTemporary As Byte iFreeFile = FreeFile Open "\temp.bin" For Binary Access Read As iFreeFile Do While Not EOF(intFileNum) Get iFreeFile, , bTemporary //read byte from file Cells(1, 1) = bTemporary //save in cell Loop Close iFreeFile End Sub