PDA

View Full Version : Open and refresh



percy4
04-27-2009, 01:10 AM
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
04-29-2009, 12:21 AM
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

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
04-29-2009, 12:32 AM
Sourcefile is a string, so you close it with



With Workbooks(sourcefile)
.Save
.Close
End With

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

percy4
04-29-2009, 01:33 AM
Sourcefile is a string, so you close it with



With Workbooks(sourcefile)
.Save
.Close
End With

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
04-29-2009, 03:10 AM
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


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)


then use wb to close



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