4

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

Ideally, this data should be tied with a worksheet in the workbook.
CustomProperties are unlikely to support large data.

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

Thanks...

pnuts
  • 58,317
  • 11
  • 87
  • 139
Andrew Shirokoff
  • 41
  • 2
  • Cells in Excel supports well text, formulas and numbers. Why do you want to use excel to store binary data? It'll be very difficult to use it inside Excel ... – Dr. belisarius Nov 19 '10 at 16:20

3 Answers3

0

If your information comes in files, you can read the file in binary mode and put it in a cell. Then in the end or in the beginning you would save your filename so you could easily reconstruct the file. Here is some code for you to begin with:

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
Danilo Leite Ribeiro
  • 51
  • 1
  • 2
0

Yes, you could store string and XML in Excel cells. For binary you'd be better off not saving it inside Excel, but if you had to then OLE (object linking and embedding) could be an option. You could do so by saving the binary as a file outside of Excel and then inserting it as a 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

It worked fine for us for certain types of common filetype. But we never did it for raw binary data. Usually we put a Word Document or PDF in the spreadsheet. You also run the risk of possibly corrupting the workbook or losing the binary data. In our case the OLE object would be clicked on by a user that had Wordperfect instead of Word or they ran Linux / Mac and the embedded document wouldn't open.

It does make the Excel File get rather large with every embedded object you add. It's an old technology with its quirks.

Charles Byrne
  • 834
  • 2
  • 11
  • 20
0

You could add a VBA module to the workbook for your data and encode your data in normal ASCII strings (for example, using Base64 encoding). Resulting code would look like this:

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


End Sub

You can also store these strings in a sheet instead of a VBA module, line-by-line, if you prefer this.

To accomplish the encoding / decoding, look here:

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

Base64 Encode String in VBScript

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

Community
  • 1
  • 1
Doc Brown
  • 19,739
  • 7
  • 52
  • 88
  • Nice solution. It worth to mention that storing data in the vb module is limited by the VBA compiler to a few kilo bytes of data, whereas storing to the sheet supports megabytes with no issue. Le more data is stored per cell the less time is required to saves the spreadsheet to the disk. 4 kb per cell works fine for me. Base64 does not result in smaller files than Hexadecimal an is simpler to implement. I suppose this is due to the compression of worksheets. – Pierre Dec 07 '14 at 14:15