Microsoft VBA - attach multiple files

Question

I have a question related to VBA Code. The main idea is that the user is able to attach files to Excel sheet via button. The code opens up the browser and allows user to choose multiple files from the location of their choice. The program inserts the files as icons to a specific area on the sheet. Specific area is a single row from J column to O column.

• The program allows the user to choose maximum of 5 files
• If user inserts wrong files, there is a delete file(s) button. When user chooses one or more files attached to the sheet and presses the "delete selected files"-button, the program deletes them.
• The program allows the user to insert, remove and reinsert files with no problem.

The issue here is, that when user does not use the "delete selected file(s9)"-button and removed the files via keyboard delete button, there will be an empty space left on the sheet between other attached files.

Right now the code inserts a hidden letter to the cell the file(icon) is inserted to. When using the "delete selected cells" button, it removes the letter and everything works well.

I chose this hidden letter -tactic because I didn't find a solution to link the icons to the cell its inserted to.

I need solution that:

• links the files(icons) to the cell they are inserted to so when the file is removed (no matter via button or keyboard) the cell is removed as well and there will be no blank space left between other inserted files.
• or an alternative way that the inserted files will stay in order, no blank spaces and everything works well when inserting, deleting and reinserting files

Here's the code I have now for the "attach file" -button:

Public Sub Attach_File()

ActiveSheet.Unprotect 

Application.ScreenUpdating = False 

Dim Rng As Range 

Dim fpath As Variant 

Dim i As Integer 

Dim filePath As Variant 

Dim objC As Object 

Dim selectedFilesCount As Integer 

'If error occurs 

If IsError(Application.Caller) Then 

    MsgBox "The Insert\_Document2 macro must be called from a button click.", \_ 

    vbCritical 

    Exit Sub 

End If 

Set Rng = Range("G6:U8").Find(What:="UPLOADS").Offset(4, 0) 

On Error Resume Next 

fpath = Application.GetOpenFilename("All Files,\*.\*", Title:="Select file", MultiSelect:=True) 'Opens up the browser & allows the user to choose multiple files 

selectedFilesCount = UBound(fpath) - LBound(fpath) + 1 

If selectedFilesCount > 5 Then 'If user chooses over 5 files from browser --> alert dialog is shown 

    MsgBox "The maximum number of attachment is 5." 

    Exit Sub 

End If 

For i = 1 To UBound(fpath) 'Inserts the chosen files to "Uploads" box = loops through each chosen file & inserts them with following features 

    If Rng.Value = "" Then 

        Rng.Select 

    Else 

        Rng.End(xlToRight).Offset(0, 1).Select 

    End If 

    Rng.Value = "m" 

    Rng.Font.ColorIndex = 2 

    Set objC = ActiveSheet.OLEObjects.Add( \_ 

    Filename:=fpath(i), \_ 

    Link:=False, \_ 

    DisplayAsIcon:=True, \_ 

    IconFileName:="explorer.exe", \_ 

    IconIndex:=0, \_ 

    IconLabel:=extractFileName(fpath(i))) 

    Set Rng = Rng.Offset(0, 1) 

    objC.Locked = False 

    objC.Height = 250 

    objC.Width = 65 

Next i 

'now the code adds a letter under each inserted file --> cell has value <> "" --> helps it \_ 

to identify the blank cells where to insert new files 

Application.ScreenUpdating = True

ActiveSheet.Protect 

End Sub

Public Function extractFileName(filePath) 'Shortens the file names once inserted

Dim i As Integer

For i = Len(filePath) To 1 Step -1 

    If Mid(filePath, i, 1) = "\" Then 

    extractFileName = Mid(filePath, i + 1, Len(filePath) - i + 1) 

    Exit Function 

    End If 

Next 

End Function

Answer 1

Thank you. I will give this a go and I'll give you a sign if I need further guidance. :)

Answer 2

I need a solution where the code identifies which cells (in the chosen range) already have objects on them and when adding more files, they won't be inserted on top of the previously inserted files.

Also when deleting the file via delete-button it would remove the empty cell as well.

OK, I'll give you a start:

a) Any object that you have in a sheet is inside the Shapes collection. Simple scenario:

So the first step is to detect where these shapes are located.

b) Any shape object has two properties that are helpful, TopLeftCell and BottomRightCell. Both returns a Range object and if we combine them, we know what area the shape covers.

Sub Test()
Dim Ws As Worksheet
Dim Sh As Shape

Set Ws = ActiveSheet
For Each Sh In Ws.Shapes
Debug.Print Range(Sh.TopLeftCell, Sh.BottomRightCell).Address(0, 0)
Next
End Sub

c) I assume that you have a fixed top left cell, in this example A1. We can use a code similar as above, check each BottomRightCell to get the maximum row and column. After that we know the used range of the shapes in this example is A1:H13

d) We can run a loop on each column of the used range and check if some of our shapes intersects which this column.

https://learn.microsoft.com/en-us/office/vba/api/excel.application.intersect

if there is no intersection "remember" the column and combine all the found columns using Union

https://learn.microsoft.com/en-us/office/vba/api/excel.application.union

e) Delete all found columns if any

f) Do the same for rows

That's it.

I think you will enjoy writing this code yourself. If you need more help, give me a sign.

Andreas.

Answer 3

Hello Andreas,

a) The file is protected but the user is still able to select files and so able to delete them the way he wants.

b) Yes exactly. The problem is that I don't know how. I have searched for a way but have not yet found one. Any tips for that?

As I wrote in the post, I need a solution where I can let go of the idea of inserting a letter behind the inserted file. That idea is clumsy and does not work very well.

I need a solution where the code identifies which cells (in the chosen range) already have objects on them and when adding more files, they won't be inserted on top of the previously inserted files.

Also when deleting the file via delete-button it would remove the empty cell as well.

Answer 4

The issue here is, that when user does not use the "delete selected file(s9)"-button and removed the files via keyboard delete button, there will be an empty space left on the sheet between other attached files.

Application.ScreenUpdating = True

ActiveSheet.Protect

End Sub

Counter questions:

a) How should the user be able to delete a file manually if the sheet and so the embedded file(s) are protected?

b) Who cares? You can write a code that determines the objects or their position in the sheet and deletes the empty cells between.

Andreas.

Answer 5

Hi NeedHelpVBA,

I carefully went through your question.

The main problem is that the function of the button “Delete” is difference with the keyboard “Delete”.

There is no way to change the keyboard "delete" function.
As workaround, you can add a notice in your worksheet to suggest user to delete file using the button instead of the keyboard “delete”.

Also, if I find some solution to disable the Keyboard "delete" by macro, I will add it here.

Best Regards,
Snow Lu