Removing A Query

Miffo · May 10th 2007

Hello all!

In my workplace I've been put in charge of deliviring a couple of reports. They are all done in MSSQL, and so far no problem. The problem is when I'm reformatting it in Excel.
I start with an empty spreadsheet (with only the macro behind it) that runs the reports, reformat it and save it to another completly empty spreadsheet. This way I wont get any problem with file already exists or anything like that. My problem is though, that the file with the macro is supposed to stay empty. Everytime I'm running the reports it has to be closed, and to close it I have to save it so I wont get the annoying "The file has been changed, do you want to save it". When I save it the last query is saved with it, and it builds up, so if I'm on vacation the file will be huuuuge when I come back since no one has emptied the lingering query.

My question is now:
How do I delete a query before I save the file
OR
How do I close the file without saving it?

Any option would suit me as the macro file should remain constant and not change at all.

Any help here would be greatly appreciated.

Kind regards
/Henrik

carlmack · May 10th 2007

Re: Removing A Query

To close the workbook

Code

ThisWorkbook.Saved = True
ThisWorkbook.Close

HTH
Carl

Miffo · May 10th 2007

Re: Removing A Query

Hi!

Thanks for the suggestion, but the problem is that you solution only closes the workbook, not the application.
I have to close down everythng when its done, and if I do this Excel will still stay open, with a new session opening next time the macro runs. It will run once every hour and if I'm away for 2 weeks I'll have 24*14 sessions of Excel open = memory hogging on server.

I actually tried that once, but after the command

Code

Thisworkbook.Close

the macro stops running, and I wont be able to do the last step:

Code

Application.Close

I also tried with the combination:

Code

ThisWorkbook.Saved =  True
Application.Close

but it still gives me the "Do you want to save"-message then.

Thanks anyway...

carlmack · May 11th 2007

Re: Removing A Query

To close the app also try :

Code

ThisWorkbook.Saved = True 
Application.Quit

RowanS · May 12th 2007

Re: Removing A Query

Hi

I am guessing that when you say you want to delete the query, you are actually wanting to remove the data from the sheet. If this is the case try something like this.

Code

Sub ClearAllData()
    With Sheets("QueryResultSet")
        .Range("A1:V9999").ClearContents
    End With
End Sub

You could call this function as you exit the sheet.
Cheers

Rowan

Removing A Query

Participate now!

Share