PDA

View Full Version : Solved: Excel 2007 _Can I store a HELP .txt file inside and .xlsm file?



frank_m
01-15-2011, 10:33 PM
Excel 2007

Would I be able to store store a HELP .txt or .rtf file inside and .xlsm file?

I do not need to access it programmaticly, I merely want the file to be available in case of am emergency.
I figured that seeing that an .xlsm can be unzipped and contains other types of files and folders this might be possible.

Edit: of course if it could be opened programmaticly that would be all the better

Thanks

Paul_Hossler
01-16-2011, 11:21 AM
I've used a blank worksheet to hold 'support' files, etc.

Insert, Object, Create From File. I usually leave 'Link to File" unchecked, and "Display as Icon" checked, but you can experiment

I did find that txt files (at least) seem like they can only be displayed as an icon regardless of setting, but PDF's will show as icon or the document

If you double click the icon, the supporting app opens in a new window

I hope someone can come up with a way to just 'read' the embedded file. I'd find that useful

My workwaround is to put the reference material into cells in a hidden worksheet, which is easily accessable to VBA

Paul

frank_m
01-16-2011, 07:15 PM
Thanks Paul.

I'm sure what you provided me with will get me going in a good direction.

Blade Hunter
01-16-2011, 08:04 PM
I usually use a form, you can make it pretty if you do need to display it :).

Paul_Hossler
01-16-2011, 08:57 PM
Actually, I do use a form to display the content of the cells by reading the cells into a list box on the form

Paul

GTO
01-18-2011, 10:49 AM
...I hope someone can come up with a way to just 'read' the embedded file. I'd find that useful

My workwaround is to put the reference material into cells in a hidden worksheet, which is easily accessable to VBA

Paul

Hi Paul,

Hope I'm not misunderstanding... While this will not read the embedded object directly, would saving it temporarily help?

Adapted from: http://www.sqldrill.com/excel/programming-vba-vb-c-etc/293335-pasting-embedded-ole-objects-specific-filename.html


Option Explicit

Sub exa1()
Dim FSO As Object ' FileSystemObject
Dim fsoFol As Object ' Folder
Dim fsoStream As Object ' TextStream
Dim fsoFile As Object ' File
Dim myShell As Object
Dim sFilNam As String
Dim shp As Shape

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoFol = FSO.GetFolder(ThisWorkbook.Path & "\")

If Not FSO.FolderExists(ThisWorkbook.Path & "\tempfol") Then
FSO.CreateFolder ThisWorkbook.Path & "\tempfol"
Else
sFilNam = Dir$(ThisWorkbook.Path & "\tempfol\*")
Do While Not sFilNam = vbNullString
Kill ThisWorkbook.Path & "\tempfol\" & sFilNam
sFilNam = Dir$
Loop
End If

Set shp = Sheet1.Shapes("Object 1")
shp.Copy

Set myShell = CreateObject("Shell.Application")
myShell.Namespace(CVar(ThisWorkbook.Path & "\tempfol\")).Self.InvokeVerb "Paste"
Set myShell = Nothing

Set fsoFile = FSO.GetFile(ThisWorkbook.Path & "\tempfol\" & Dir$(ThisWorkbook.Path & "\tempfol\*"))

Set fsoStream = fsoFile.OpenAsTextStream(ForReading)

Do While Not fsoStream.AtEndOfStream
'do whatever
Debug.Print fsoStream.ReadLine
Loop
fsoStream.Close

Set fsoFile = Nothing
Set fsoStream = Nothing

sFilNam = Dir$(ThisWorkbook.Path & "\tempfol\*")
Do While Not sFilNam = vbNullString
Kill ThisWorkbook.Path & "\tempfol\" & sFilNam
sFilNam = Dir$
Loop

FSO.DeleteFolder ThisWorkbook.Path & "\tempfol"
End Sub

Not well tested, but seems to work: pray2:

Mark