Query Refresh On File Open

[MDNova]

Hi,
I have no real background in programming but am very excel/access/sql savvy. My skill set just doesn't include VBA (yet..). I am trying to find the right code that will allow my queries to an MS access data base to refresh when the workbook is opened without the user being prompted by the query refresh dialog box to "Enable automatic refresh" or "Disable automatic refresh".

This is the code I've come up with from searching around forums here and tooling around a bit myself.

Private Sub WorkbookOpen()
Dim wks As Worksheet
Dim qt As QueryTable
For Each wks In ActiveWorkbook.Worksheets
For Each qt In wks.QueryTables
qt.RefreshOnFileOpen = True
Next qt
Next wks
End Sub

I still can't get it to work the way I want as admittedly I really just don't have the VBA knowledge I need. Any help would be much appreciated. Thanks.

 

[ElkySS]

While in VBE, under "Microsoft Excel Objects" you should see a list of your sheets and one called "This Workbook" Double click "This Woorkbook" then change the Object drop down (located in the top left of the module pane) from "(General)" to "Workbook" Excel will put:
Private Sub Workbook_Open()

End Sub
in the module window by default. Then just add your Msgbox code so it looks like
Private Sub Workbook_Open()
Dim wks As Worksheet
Dim qt As QueryTable
For Each wks In ActiveWorkbook.Worksheets
For Each qt In wks.QueryTables
qt.RefreshOnFileOpen = True
Next qt
Next wks
End Sub

Save, exit, and reload.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am assuming that the part in the middle works...<o></o>

 

[MDNova]

Thanks, ElkySS. That's actually very helpful for me in beginning to understand VBE more. It looks like I need to refine my code more though as the dialog box asking me whether I want to enable or disable the auto refresh still pops up. Do you know of any code that will edit the query security settings to always enable the automatic refresh without the dialog box popping up?