after macro run, close file

[Donkeygirl]

I am automating an excel file by using command buttons place on an empty sheet in the file, meant as a menu. I have a macro which opens a second excel file on command button click and refreshes the queries to update the data for me. I am looking for how to save and close the second file once the repfreshing is done for all of the sheets in the second workbook. Keep in mind that the file supplemental sheets.xls is open, on command chart data.xls was opened and this is the code that refreshes the data. The only thing missing is the command to save and close chart data.xls ONCE the refreshing is done. Otherwise a message comes up that the refreshing must keep going or close and end refreshing.
I hope someone can help.

Refreshquerydatas is the macro that is run on click:

Sub Refreshquerydatas()
'
' Refreshquerydatas Macro
' refresh microsoft queries, which updates the quarterly data
'

'
ChDir "H:\is\Quarterly"
Workbooks.Open FileName:="\\CC1\SYS2\BOE\DATA\is\Quarterly\chart data.xls"
Cells.Select
Range("A1:BR1650&quot.Activate
Selection.QueryTable.Refresh BackgroundQuery:=True
Sheets("MALE&quot.Select
Cells.Select
Range("A1:BR1650&quot.Activate
Selection.QueryTable.Refresh BackgroundQuery:=True
Sheets("CITY&quot.Select
Cells.Select
Range("A1:BR1650&quot.Activate
Selection.QueryTable.Refresh BackgroundQuery:=True
Sheets("TOWNS&quot.Select
Cells.Select
Range("A1:BR1650&quot.Activate
Selection.QueryTable.Refresh BackgroundQuery:=True
Sheets("FEMALE&quot.Select
Cells.Select
Range("A1:BR1650&quot.Activate
Selection.QueryTable.Refresh BackgroundQuery:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("COUNTS&quot.Select
Cells.Select
Range("A1:BR1650&quot.Activate

End Sub

 

[LoNeRaVeR]

ISoperator:

If the "chart data.xls" is still selected you won't need to activate it again, but it must be the active sheet to close it using [tt]ActiveWorkbook.Close[/tt]. Add this code where you would like to close and save the "chart data.xls" file.
[tt]
Windows("chart data.xls&quot.Activate
ActiveWorkbook.Close SaveChanges:=True
[/tt]
Regards,

LoNeRaVeR

 

[Donkeygirl]

Thank you. I knew it was something easy like that. i have been learning as I go, and I have gotten pretty far. Te first thing you learn with vba though, is that there is so much to learn. Every single command and object, property, and method.
Today is my birthday, so thanks for making it good.

 

[LoNeRaVeR]

ISoperator:

Happy Birthday!!!

I know what you mean about 'learning as you go'. I would have to say I know more than most, but I learn new things every day! One of these days I'm going to start putting all I know on my web site. I'm glad that this did the trick. As for every object, property, and method, I don't try and memorize them. I just let VBA tell me what's available, and after a while they just become habit. One thing I read long ago about VBA that helps a great deal is that if you read the statements from right to left they make more sense. Try that when reading code. It helps.

Have a great day! It's YOUR day!

LoNeRaVeR

 

[Donkeygirl]

Thanks a lot. And let me know when you put your wisdom on your website! I am investing in a VBA book this weekend, and I am always looking for good references.
I am almost done with this automation. My boss is supposed to be coming over to look at what I have to tackle last. Basically I knew nothing when I started. I had never looked at code. But now I have automated access apps, I'm working with excel automation, and I have gone into applications that other people created to refine and update them. It is really great.
If you want to hear my last task, I will leave it here for you. But do not feel you are being asked to take your time. You've already helped me a lot.

I have this spreadsheet in my supplemental sheets which is all automated to refresh its data and the datas queries, check totals, and by user form, update the spread sheets text and dates; it also prints out the needed graphics.
I will say at this point, it will be good to know that this is voter registration enrollment numbers. They are published quarterly.
On the spreadsheet, there is a section for the total enrollment for that quarter
Just after that section is a short section with four numbers. 1) is the change in enrollment from this quarter and last, 2) is the the difference in registered democrats fro mthe last enrollment to this one, 3) is the republican difference, and 4) is the difference for those registered 'blank' or without any party.
Before update all of the last quarter information would be there, I am thinking. My only hesitation is that the live links prompt a message box on open (excel's own automation, not my work) and says yes update links or no don't.

***In other words I need to figure out how to write code to take the difference between data before update and after.

I hope you can help, but if not, do not worry, I am copying an pasting this to post on message boards where ever I can.

Thanks again for your help.

ISoperator

 

[LoNeRaVeR]

ISoperator:

You've done incredibly well for just getting started with VBA. I have a large library of Excel and Excel VBA books, and I highly recommend the following for a beginning Excel VBA book:

Using Excel Visual Basic for Applications
by Elisabeth Boonin
Price: $24.99
Publisher: Que
ISBN: 0789703254
Paperback - 432 pages (January 1996)

Now for your questions. You can turn off the request box by using:
[tt]
Application.AskToUpdateLinks = False
[/tt]
This will update your links without prompting the user.

There are many ways to take before and after data and compare the two. I suggest making a copy of your data as values on a hidden sheet with the date of the update. You can then find the difference between your old and new data by comparing the two.

Keep up the great work!

LoNeRaVeR

 

[Donkeygirl]

Thanks I will add the request update to false. Other than that I decided to make the automation run so that the user will update that page on their own. This means that it wil take them 15 minutes to produce the quarterly instead of 10. I can live with that.
haha. Thanks for the encouragement. I was thinking of getting the overall VBA developer's handbook, only because I do more Access than excel. I would want the whole thing because I might want to automate a powerpoint show I am producing. I like being able to create automation in all of the office apps, and this might help me in all instances. I have the Access 97 developers handbook, and that is pretty good.
Well, thanks for all of your help!
IS