0

I am working with two workbooks. I am inserting data in one and the other is taking part of this data via Excel query, thus I have to open the second one every time I want to refresh the information, as I am sharing it with other users.

In few words, I would like to remotely refresh the second workbook when I am updating the main one so I do not have to continuously open, refresh and close.

Let's assume that both workbooks are in the same folder in Dropbox, for example.

Thanks a lot!

Senzar
  • 69
  • 2
  • 11
  • id code the 2nd to refresh on open. – Nathan_Sav Nov 10 '16 at 10:39
  • It already refreshes automatically when opening – Senzar Nov 10 '16 at 10:48
  • I would suggest to clarify what you have done so far to solve your problem so we can better help you. Have you written any code yet ? This can be usually be done by an open-update-save-close approach like in the following [link](http://stackoverflow.com/questions/12951946/excel-vba-open-workbook-perform-actions-save-as-close) I would also suggest you to read the [How to ask question of SO](http://stackoverflow.com/help/how-to-ask) . – Pierre Chevallier Nov 10 '16 at 11:03
  • You want it to update the other users table if it's open? – Nathan_Sav Nov 10 '16 at 11:04

1 Answers1

1

I have found a way, however it does not work in Mac OS as queries are not supported (I did not know about this).

For those who it might help, below the code:

Sub RefreshWB()
Application.ScreenUpdating = False
ActiveWorkbook.Save
Dim wb As Excel.Workbook
Set wb = Application.Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "workbook.xlsx")
wb.Sheets("Sheet 1").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
wb.Save
wb.Close
Application.ScreenUpdating = True
End Sub
Senzar
  • 69
  • 2
  • 11