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

[feipezi]

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

 

[Andrzejek]

Would this be what you are looking for?

You can also try this.


---- Andy

There is a great need for a sarcasm font.

 

[feipezi]

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

 

[Andrzejek]

>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: [tt]IconLabel:=[blue]strCompFilePath[/blue] [/tt]?

Please, format your code as [tt]CODE[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[combo]

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