I have a macro that I wrote recently to populate a table in a word document from excel data,
I used this script to pull the data from excel using labels,
Private Sub CommandButton1_Click() Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook Set exWb = objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expenses.xlsx") ThisDocument.Label1.Caption = exWb.Sheets("Sheet1").Cells(12, 2) exWb.Close Set exWb = Nothing End Sub
The thing is, i'd like to loop through many labels...Labels1 to Labels36, and each time the cell location changes from 12,2 to 13,2 etc. How would I do this using an for loop or something similar?
And better yet, is there a way to create the word table on the fly? I may not know the total number of rows, it could be 12, 16 or 36 depending on the excel data. Is it possible to create a label in the loop?
Also, is it possible for the user to write down in the word document the file path, then have the vba script pull the path from there and then use it in the script?
