VBA Code for refreshing Excel Spreadsheet Query Table

[vbc22]

Hi,

I'd like to know what the vba code is for refreshing a spreadsheet query table in Excel.

The spreadsheet is actually based on a web query, which I want to import into Access using VBA. But before I import, I need to have the spreadsheet update itself. Manually, what I'd do is right-click on a cell and click "Refresh Data".

I recorded a macro to find out what the vba code would be in Excel.

Range("F633").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

So going with that, I went back to Access, and based on my Excel.Application variable as xl, I proceeded with,

xl.ActiveSheet.QueryTable.Refresh BackgroundQuery:=False

But this didn't work.

If someone knows what the correct syntax is, I'd appreciate it.

Regards.

 

[vbc22]

Well I found the solution after testing out different keywords.

The routine I concluded with was:

Private Sub updateExcelWebQuery_Click()

    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.Application")

    xl.Workbooks.Open "[[COLOR=Blue][I]path and filename goes here[/I][/COLOR]]"
    xl.Visible = True

    xl.ActiveCell.QueryTable.Refresh

End Sub

Just to make it more robust though, make a hard selection on the cell...meaning, use range(cell, row).

Regards.