I've used this method frequently, but today for some reason it is not working:
From within Word, I am using VBA to access an Excel file via ADODB. SavedSystems in my code below is an Excel NAMED RANGE. This routine adds records to the worksheet, and usually the named range expands automatically when I use the objrecset.AddNew. That's important so that next time I read in the SavedSystems named range those newly added records are included.
This exact code has worked many times for me before. Now it is not. The new rows are saved just after the named range, but the named range does NOT expand to include them.
What am I missing?
DataFile = Environ("AppData") & "\Microsoft\Word\SavedSystems.xlsx"
TableName = "SavedSystems"
Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=0"";"
Set objRecSet = New ADODB.Recordset
objRecSet.Open TableName, objConnection, adOpenKeyset, adLockOptimistic
For myrow = 0 To NewForm.ListBox3.ListCount - 1
objRecSet.Fields(0) = Me.TextBox1.Text
For mycol = 0 To 4
objRecSet.Fields(mycol + 1) = NewForm.ListBox3.List(myrow, mycol)
Set objRecSet = Nothing
Set objConnection = Nothing