0

I want to add more files into my excel file. This macro works if multiselect is false, however if I try it with multiselect = true, I get an error message in row:

if attachment = false then exit sub. An error message was type mismatch.

Sub insertObject()

Dim attachment As Variant

'open more files, but not from specific folder    
attachment = Application.GetOpenFilename(FileFilter:="All Files (*.*), *.*",     MultiSelect:=True) 

'if attachment is missing then end macro
If attachment = False Then Exit Sub

'my try with OLEobjects
ActiveSheet.OLEObjects.Add(Filename:=attachment, Link:=False,  DisplayAsIcon:=True, IconFileName:="C:\WINDOWS\system32\packager.dll", IconIndex:=1, IconLabel:=attachment).Select

End Sub
Community
  • 1
  • 1
Martin
  • 19
  • 5
  • Attachment is probably a string and you are comparing with a Boolean. Try if attachment = "" – dgorti Jan 16 '17 at 22:49

1 Answers1

2

GetOpenFilename with multiselect set to true returns an array if anything is selected. If nothing is selected it returns False. The problem is if you selected anything, "attachment = false" causes an error because you can't make a comparison to an array like that. It's kind of lazy, but if this is always going to be a multi-select dialog you can just do

If IsArray(attachment) = False Then Exit Sub

Because it will always be an array if anything was selected and won't be if nothing was.

Also because it returns an array you would need to have something like

For i = LBound(attachment) To UBound(attachment)
    ActiveSheet.OLEObjects.Add Filename:=attachment(i), Link:=False, DisplayAsIcon:=True, IconFileName:="C:\WINDOWS\system32\packager.dll", IconIndex:=1, IconLabel:=attachment(i)
Next

To add all your files. Every value in the attachment array is a full filepath, so you probably want to parse the file name out for the IconLabel field too. And also set some incrementing value for the "top" or "left" fields in the OLEObject.Add method, because they'll just stack on top of each-other otherwise.

Wedge
  • 1,766
  • 1
  • 8
  • 14
  • Thank you for help and solution. Good point about incrementing value. I was thinking about it as well that it has to be solved to avoid stack of files on top of each other. – Martin Jan 20 '17 at 09:04