Runtime Errors with VBA for attaching files to workbooks.

[scecnic74uk]

I have a problem with the code below - sometimes! it works for 3 or 4 times in a row then give me a runtime error.. I copied it from a retired member of my staff excel sheets and wanted to use it for up to 10 files to attach - he only used it for 1 file, here's my code...

Sub AddSomething_Click()

    Dim newFile As Variant
    Dim fileCount As Integer
    Dim offsetRow As Integer
    Dim offsetCol As Integer
   
    fileCount = Range("l7").Value
    offsetCol = 1
    If fileCount = 0 Then
        Range("F25").Select
        newFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert")
        ActiveSheet.OLEObjects.Add Filename:=newFile, Link:=False, DisplayAsIcon:=True, IconFileName:= _
        "C:\WINDOWS\Installer\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", IconIndex:=0, IconLabel:=newFile
        fileCount = fileCount + 1
        Range("l7").Select
        ActiveCell.FormulaR1C1 = fileCount
        newFile = Null
        Exit Sub
    End If
    If fileCount > 0 Then
        offsetRow = 0
        offsetCol = 2
        If fileCount >= 2 Then
            offsetRow = 0
            offsetCol = fileCount * 2
        End If
        If fileCount >= 5 Then
            offsetRow = 5
            offsetCol = 0
            If fileCount = 6 Then
                offsetCol = 2
            End If
            If fileCount > 6 Then
                offsetCol = (fileCount - offsetRow) * 2
            End If
            If fileCount >= 10 Then
            MsgBox "You can only add 10 files", vbInformation
            Exit Sub
            End If
        End If
        Range("F25").Select
        ActiveCell.Offset(offsetRow, offsetCol).Range("a1").Select
        newFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert")
        ActiveSheet.OLEObjects.Add Filename:=newFile, Link:=False, DisplayAsIcon:=True, IconFileName:= _
        "C:\WINDOWS\Installer\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", IconIndex:=0, IconLabel:=newFile
        fileCount = fileCount + 1
        Range("l7").Select
        ActiveCell.FormulaR1C1 = fileCount
        newFile = Null
        Exit Sub
    End If
End Sub

 

[davesexcel]

What do you mean by attaching files to a workbook?

 

[scecnic74uk]

probably better word for it is embed. in work we use excel for method statements for carrying out tasks. when writing the method statements i'd like to be able to attach (embed) files to the workbook such as pdf's, word documents and so forth... but sometimes when i add files it comes up with a runtime error? see picture of my issue.. also the debug. if i press end and run macro again it works for 1 or 2 times more and then i get a run time error again!

 

[davesexcel]

You can place links into a worksheet that will open a file. You can't place an actual file into a worksheet.

 

[offthelip]

It is possible that this could be a memory problem or a problem with office 365 trying to synchronise. So I have two suggestions:
1: Try running with OFffice 365 off line. See if it makes any difference
2: Open a task manager window and see if the memory that EXCEL is using grows enormously. If it does, I would suggest splitting the loop control to another subroutine and pass the filename and cell to the the subroutine that embeds the file to see if that clears all the temporary objects excel probably creates in between each iteration.

 

[scecnic74uk]

Thanks offthelip, you inadvertently pointed me in right direction. There was no issue with memory usage but I seen a couple of background processes that started when I selected the files I wanted to attach. I just waited for the background processes to finish and it let place all the files I wanted into the worksheet, issue fixed! Thanks. Now that I know I have to wait before I can attach files - how do I put a time delay in the code, to prevent other less experienced computer users from experiencing the same problem?