refresh macro for hidden data

[rabarnes]

Hi

I'm creating an order input form using excel / query. My database is in a hidden sheet and my order input sheet and order form (printout) are visible sheets. I have set the data to refresh every hour but should a user need to refresh the data immediately I have created a button for this. My code for refreshing the sheet is fine when the database sheet is visible, but it doesn't work when it's hidden.

(Sorry if this is very simple - I'm quite new to vb and I'm teaching myself as I go along but can't find the answer anywhere).

Can anyone give me an example of the correct code required?

Thanks

Rowan

 

[MarkAndrews]

Replace "Sheet1" to your hidden sheet name

Application.ScreenUpdating = False
    Sheets("Sheet1").Visible = True
    ActiveWorkbook.RefreshAll
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Application.Selection = True

 

[MarkAndrews]

Let me know how you get on

 

[rabarnes]

thanks

Hi Mark

Thanks for that, it updated the sheet fine but then inputted the word "TRUE" into my first input box on my input sheet?!

To get round it I just added a "clear contents" instruction at the end of the code you provided to get rid of it.

Thanks

Rowan

 

[MarkAndrews]

You are very welcome

One thing to bare in mind

If you have a Query (Via MS Query) which is set to update on opening the document, the refreshall VB function on workbook open can clash

I had this a few weeks back, Query set to refresh on open & remove on close, I stupidly set a workbook_open - refresh all, which counter acted the query refresh

Got around it by putting a wait in the routine, of 5 seconds, this allowed for the query to be refreshed, then refresh the entire workbook & all pivot tables

Happy to help