Open and refresh

[percy4]

Hi Guys,

I need some help with a pretty complex (at least for me) VBA code that should do the following:

I’ve got my “masterfile” which contains 6 sheets each containing a query from another Excel file (via ODBC) and some formulas based on links to “slavefile”.

What I want is to create a macro that runs everytime I open “masterfile” and that does following:

Opens up “slavefile” (not visible to user) and after that refreshes all data while showing a msgbox stating “updating sheet”. After refresh is complete sending out a message to the user that says “update complete”.

Thanks in advance!

Regards
Per

[percy4]

[vba]Sub skarp()
Msgbox "Data will now be refreshed. It may take up to 60 seconds."
Dim ws As Worksheet
Dim qt As QueryTable
Dim sourcefile As String
sourcefile = "H:\My Documents\Masterlistan\2\historik v2.0 final test.xls"
Workbooks.Open sourcefile
Application.StatusBar = "Macro running, please wait....."
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="test"
For Each qt In ws.QueryTables
qt.BackgroundQuery = False
qt.Refresh
Next qt
Next ws
DoEvents
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", userinterfaceonly:="true"
EnableAutoFilter = "true"
Next ws
Application.ScreenUpdating = True
Msgbox "Data refreshed!"
With Workbooks sourcefile
.Save
.Close
End With
End Sub[/vba]

I've come up with above but I can't seem to get the last workbook save and close to work. I also have a problem hiding "sourcefile" when it opens. Where should I put the "activewindow.visible=false"?

Does anybody know what the problem is?

Thanks so much for your help!

Thanks in advance!
P

[Bob Phillips]

Sourcefile is a string, so you close it with

[vba]

With Workbooks(sourcefile)
.Save
.Close
End With[/vba]

Hide it after opening it, using the workbook name for the window.

[percy4]

Sourcefile is a string, so you close it with

[vba]

With Workbooks(sourcefile)
.Save
.Close
End With[/vba]

Hide it after opening it, using the workbook name for the window.

Thanks for your quick reply but that doesn't seem to do the trick. I get an error message stating "subscript out of range".

The hiding part worked as a charm. I just added
ActiveWindow.Visible = False after "open sourcefile" code.

[Bob Phillips]

Sorry, my bad. Sourcefile has the full path, you just need the workbook name.

I would set a wb object when you open it like so

[vba]
Dim wb As Workbook
Dim sourcefile As String
sourcefile = "H:\My Documents\Masterlistan\2\historik v2.0 final test.xls"
Set wb = Workbooks.Open(sourcefile)
[/vba]

then use wb to close

[vba]

Application.ScreenUpdating = True
Msgbox "Data refreshed!"
With wb
.Save
.Close
End With
Set wb = Nothing
[/vba]