×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

(OP)
Hello,

I recorded a macro to do what's on the Subject line but each time I have to select a particular file from a folder. What I want is to have a macro with a do loop that can embed multiple file in a series of designated cells, with no need for the user to pick from a folder.

Here is the code that I recorded.

Thanks.


Sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="AcroExch.Document.2015", Link:=False _
, DisplayAsIcon:=True, IconFileName:= _
"C:\WINDOWS\Installer\{AC76BA86-7AD7-FFFF-7B44-AE0F06755100}\PDFFile_8.ico", _
IconIndex:=0, IconLabel:="Adobe Acrobat Document").Activate
End Sub

RE: How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

Would this be what you are looking for?

You can also try this. smile


---- Andy

There is a great need for a sarcasm font.

RE: How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

(OP)
Hey Andy,
It works. Thanks.
But I did some modification. For instance, I have 2 Excel files in a folder. I tried to embed those 2 files in the tab, "Object". Somehow I got a '1004' because I have 4 files listed, not 2. The file No.3 and 4 have something like "~$" as prefix attached to those 2 files and now I have 4. So I need only to take the files without "~$".

Here is the code. The next will be to figure out the way of editing the text in the icons.
thanks.


Sub AddOlEObject()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook

Folderpath = "C:\Users\pl04512\Documents\pnc\Franktest\Aja\tmp\SIL"
Set fso = CreateObject("Scripting.FileSystemObject")
NoOfFiles = fso.GetFolder(Folderpath).Files.Count
Set listfiles = fso.GetFolder(Folderpath).Files

For Each fls In listfiles
Counter = Counter + 1
Range("A" & Counter).Value = fls.Name
strCompFilePath = Folderpath & "\" & Trim(fls.Name)
If strCompFilePath <> "" And InStr(strCompFilePath, "$") = 0 Then

'MsgBox strCompFilePath

ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:=False, DisplayAsIcon:=True, _
IconIndex:=1, IconLabel:=strCompFilePath).Select
Sheets("Object").Activate
Sheets("Object").Range("i" & ((Counter - 1) * 3) + 1).Select
End If
Next

'mainWorkBook.Save
End Sub

RE: How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

>The file No.3 and 4 have something like "~$" as prefix attached to those 2 files and now I have 4. So I need only to take the files without "~$".

When you open any Excel file, MicroSoft creates (a back-up?) of your open file with ~S in front. This file disappears when you close your original Excel file.

> the way of editing the text in the icons.
Isn’t that this part: IconLabel:=strCompFilePath ?

Please, format your code as CODE


---- Andy

There is a great need for a sarcasm font.

RE: How to embed files like PDF, TXT file and zipped files on an Excel spreadsheet?

Quote (feipezi)

Somehow I got a '1004' because I have 4 files listed, not 2.
There is another reason of this error. You should have additional error description, can you post it? At which line the error occurs? You may have wrong full path&name string or problem with Sheets("Object") activating and next accessing cell you try to fill in.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close