Refreshing a SharePoint list in Excel

[Impecunious]

Hi,

I've got a list in an Excel file that's linked to SharePoint. If I
right click anywhere in the list and choose List > Discard Changes and
Refresh, the list refreshes fine. However, if I use the VBA
equivalent - ListObjects("List1").Refresh (the code that the macro
recorder even creates) - I get an application-defined error '1004)
when the refresh appears to be wrapping up.

Can anyone explain? This is really bizarre and I don't want to have
to do this manually.

Thanks!

 

[Barb Reinhardt]

What line of code does it error out on?

Barb Reinhardt

 

[Impecunious]

What line of code does it error out on?

Barb Reinhardt








- Show quoted text -

Hi! It errors out on the refresh command itself, which in this case
is the line "ActiveSheet.ListObjects("List1").Refresh.

What's peculiar is that I've created another file with different lists
but based on the same approach and I can programmatically refresh
without a problem. However, I'd really like to fix this particular
file that has the problem because it's a high-priority file.

Thanks!

 

[Barb Reinhardt]

Try something

before that line, put this

Dim myList as ListObject

for each myList in activesheet.listobjects
Debug.print myList.name
next mylist

Do you have "List1" listed?

Barb Reinhardt

 

[Impecunious]

Try something

before that line, put this

Dim myList as ListObject

for each myList in activesheet.listobjects
   Debug.print myList.name
next mylist

Do you have "List1" listed?

Barb Reinhardt








- Show quoted text -

Hi Barb,

I do, so I don't think it's an issue with using the wrong list name.
As I mentioned above, the line actually executes (that is, the list
seems to be refreshing over several seconds), but near the time that
it should be wrapping up, I get the error.

Thanks!

 

[Barb Reinhardt]

I just recorded a macro to syncronize the lists. Does this work?

ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.