1
\$\begingroup\$

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
\$\endgroup\$
4
  • \$\begingroup\$ Please delete your current question and post it on StackOverflo website instead. The purpose of this website is to improve code which fully works. \$\endgroup\$ Jun 16 at 8:46
  • \$\begingroup\$ @BillalBegueradj, as stated in the question I posted above, the code fully works however I am looking to make it run better by being able to process more captions for larger documents hence why it is posted on StackExchange not stack overflow. Cheers. \$\endgroup\$
    – Michael
    Jun 16 at 22:16
  • \$\begingroup\$ I have not been able to successfully achieve transferring the excel data into an array. Any help would be much appreciated thank you! \$\endgroup\$ Jun 17 at 4:04
  • \$\begingroup\$ Hi Billal. Yes, my theory is that if I can transfer the data into an array rather than calling up the spreadsheet each time it will run faster and not chew up so much memory. I have not had success in doing this. The code I have above does not call up data into an array but rather directly references the spreadsheet each time. If you have any other suggestions as to how I could improve the code above to work for 1000+ photos please feel free to share. \$\endgroup\$
    – Michael
    Jun 17 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.