Force synchronize list with VBA

S

Sing Chung

Dear all,
I need help on auto-sync Excel with Sharepoint. I created an Excel 2003
worksheet by right-clicking at Sharepoint site, select "export to Excel". The
worksheet created is then linked to Sharepoint. Everytime when I open the
Exce workbook, it prompts for Enable or Disable background refresh. I always
enable it and leave it open throughout the day. However, I find that it never
refresh during the day, I have to manually clicking on the 'synchronize list'
button to get it refeshed with data on the Sharepoint. I therefore think of
using VBA to force it to refresh with ontime event.
Sub Refresh()
Sheets("CMR").Activate
ThisWorkbook.ActiveSheet.UsedRange.Select
Selection.QueryTables.Refresh BackgroundQuery:=True
ThisWorkbook.Save
End Sub

When reaching the line "Selection.QueryTables.Refresh
BackgroundQuery:=True", I got the error: "Run-time error '438': Object
doesnt' support this prorperty or method."

What should I do to get around it?

Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Refresh data with macro code 0
Refreshing a SharePoint list in Excel 5
Link to a sharepoint list 3
VBA Hangs 6
VBA: Delete name range 3
VBA SharePoint List Field Type assignments 3
Refreshing query data via vba button 2
Creating a new "directory" on Sharepoint 5

Top