Ah, sorry. The WEEKNUM formula is part of the Anaylsis Toolpack. To activate it, go to Tools-->Addins and check the box next to Analysis Toolpack. The only issue is that each of your users will need to do this. For reference, we can also use code to ensure that the analysis toolpack is activated whenever a user opens the workbook.
Regarding the popup, sure thing. Press Alt+F11 to get into the VBA Editor (VBE). Find your workbook in the Project Explorer. You'll need to drill down into Microsoft Excel Objects-->Sheet 1(Query). Once you double click that, the code pane should open.
Right before the line that reads Application.CutCopyMode=False, enter the following code:
(You can obviously change the message to suit.
Msgbox "Your data has been transferred. Thank you!"