Embeeding large files into your workbook without increasing the size of the workbook !

[Jaafar Tribak]

Doing some research on how to have files completly invisible , i came accross the topic of Alternate Data Streams (ADS) files.

After some testing, I discovered an interesting thing about these ADS files (apart from being truly hidden) and that is they can be easily attached to standard files , folders and even an exe without increasing the size of the latters !

So, in theory,this means that i can attach , for example, a large video/graphic file to a workbook without bloating the workbook beyond what it should be.

example : (tested on Win XP XL 2003)

The following code attaches a 25 Mb flash video to the current workbook without bloating the workbook an inch. In fact , the workbook size remained 34 Kb !

Option Explicit
 
Sub Test()
 
    'embbed a flash video into this workbook.
    Call EmbeedFile(ByVal "C:\MyLargeFile.flv")
 
End Sub
 
Private Sub EmbeedFile(ByVal PathName As String)
 
    Dim Bytes() As Byte
    Dim lFileNum As Integer
    Dim sDatfile As String
 
    ReDim Bytes(1 To FileLen(PathName))
 
    lFileNum = FreeFile
    Open PathName For Binary Access Read As #lFileNum
    Get #lFileNum, , Bytes
    Close #lFileNum
 
 
    sDatfile = ThisWorkbook.FullName & ":ADS_file.dat"
 
    lFileNum = FreeFile
    Open sDatfile For Binary As #lFileNum
    Put #lFileNum, 1, Bytes
    Close lFileNum
 
End Sub

Now to play the Video file stored in the attached ADS file, you just run the LoadFile Sub below :

(Note that you no longer need the initial Video file . You can even delete it from your drive as it is now stored withing your workbook.)

Option Explicit
 
Private Declare Function lOpen Lib "kernel32" _
Alias "_lopen" _
(ByVal lpPathName As String, _
ByVal iReadWrite As Long) As Long
 
Private Declare Function lclose Lib "kernel32" _
Alias "_lclose" _
(ByVal hFile As Long) As Long
 
Private Declare Function GetFileSize Lib "kernel32" _
(ByVal hFile As Long, _
lpFileSizeHigh As Long) As Long
 
Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
 
Private Const OF_READ As Long = &H0
Private Const SW_SHOWMAXIMIZED As Long = 3
 
[COLOR=seagreen]'change this (file extens) const to suit !!!!!!!!![/COLOR]
Private Const FILE_EXTENSION As String = ".flv"
 
Sub LoadFile()
 
    Dim Bytes() As Byte
    Dim FileNum As Integer
    Dim lFileHndl As Long
    Dim lPointer As Long
    Dim lSizeOftheFile As Long
    Dim lpFSHigh As Long
    Dim sDatfile As String
 
    sDatfile = ThisWorkbook.FullName & ":ADS_file.dat"
 
    lPointer = lOpen(sDatfile, OF_READ)
 
    lSizeOftheFile = GetFileSize(lPointer, lpFSHigh)
 
    ReDim Bytes(1 To lSizeOftheFile)
 
    lclose lPointer
 
    FileNum = FreeFile
    Open sDatfile For Binary Access Read As #FileNum
    Get #FileNum, , Bytes
    Close #FileNum
 
    FileNum = FreeFile
    Open Environ("Temp") & "\Loaded" & FILE_EXTENSION For Binary As #FileNum
    Put #FileNum, 1, Bytes
    Close #FileNum
 
    ShellExecute 0, _
    "Open", Environ("Temp") & "\Loaded" & FILE_EXTENSION, vbNullString, _
    vbNullString, SW_SHOWMAXIMIZED
 
End Sub

Now the bad news : When you copy the workbook to another Drive/Machine or send it by email, the workbook gets stripped of the ADS file and the video cannot be played of course. You can still copy the workbook within the same Drive and still work though.

IMO, this is interesting stuff so I am still looking to see if there is a way to keep the ADS file when copied to other machines.That would be really useful when distributing workbooks.

Anyone trying the code , they will first need to pass a valid Path\filename to the EmbeedFile Sub in the first code and change the FILE_EXTENSION Constante in the second code to reflect the actual embeeded file extension.

Regards.

 

[Legacy 98055]

Jafaar, I think you have a link and not a file. How could it be possbile to add a 25 meg file that only leaves a 34k footprint?

 

[Jaafar Tribak]

Jafaar, I think you have a link and not a file. How could it be possbile to add a 25 meg file that only leaves a 34k footprint?

Hi Tom.

It's a hidden linked stream file which i think must reside somewhere in the current Drive that's probably why the workbook loses the link when copied onto a different machine. This is unfortunate as the workbook can't be distubuted together with the ADS file.

the only other benefits of wanting to link a workbook to an ADS file are :

1- Conviniently Store sensitive info no matter how large (ADS are very hidden)
2- having a small size workbook .

Regards.