I have a macro written in word for transferring captions written in excel to a corresponding photograph in a word document. At the moment it runs however I am finding that it slows down over time the more captions it has to process. As such it is useful for up to approximately 200 photographs however I need it to process 1000+. I believe that it is because I am referencing the excel workbook rather than transferring the data into an array however I have not been able to successfully achieve transferring the excel data into an array. Any help would be much appreciated thank you!
sub Cybernet2() 'Add excel object library tools>References Dim intCount As Integer Dim i As Integer Dim doc As Word.Document Dim path As String Dim oex As Object Dim exWb As Object Dim ws As Object Dim rng As Excel.Range Dim rngsort As Excel.Range Dim rw As Word.Range Dim lr As Long Dim xPic As InlineShape Application.ScreenUpdating = False Application.DisplayStatusBar = False Set oex = CreateObject("Excel.Application") oex.Visible = False path = ActiveDocument.ContentControls(2).Range.Text Set exWb = oex.workbooks.Open(path, , True) Set ws = exWb.Worksheets(1) 'lr = ws.Cells(Rows.Count, "B").End(xlUp).Row 'Set rngsort = ws.Range(Cells(1, "b"), Cells(lr, "b")) Set doc = ActiveDocument For i = doc.InlineShapes.Count To 1 Step -1 Set rw = doc.InlineShapes(i).Range doc.InlineShapes(i).Select Selection.InsertCaption "Photograph", ":" & ws.Cells(i, "b").Value, "", wdCaptionPositionBelow, 0 Next i End Sub