Refresh SQL data link by macro instead of excels connection wizard

Hi,

I have finally got round to linking all my spreadsheets up to our oracle database through an access SQL query, one problem I have with one workbook is that the on open macro completes before the workbook updates its SQL link.

The SQL is set up through excels normal external data link wizard and runs when you open the workbook. I need a way to trigger the SQL refresh from the macro below and make sure the macro doesn't continue until the SQL refresh is complete.

Code for this workbook is below, the SQL link is on the workbook called "UPDATER", the workbook called "MASTER SAR" is the file that is ultimately updated. For security and database connection issues I cannot have the SQL query on the "MASTER SAR" workbook.

Workbook

Please Login or Register  to view this content.

Module 1

Please Login or Register  to view this content.

Module 2

Please Login or Register  to view this content.

Module 3

Please Login or Register  to view this content.

Thanks for that, I have been hunting around and haven't got anything working yet but it seems I may be able to use an afterrefresh command.

My only issue is im not sure about qt querytables as an object and my code errors on that.

Also I haven't ever used a class module and I'm not sure why I would need to use one or what they are for:

This is what I have so far it will call module2.savesrv once the sql update has success....

The code errors on module4 on line

Please Login or Register  to view this content.

Workbook

Please Login or Register  to view this content.

Module 4

Please Login or Register  to view this content.

Class 1

Please Login or Register  to view this content.

You won't need any class module.

Assuming a querytable in A1
After refreshing cell A1 will have some value.
So you can use:

Please Login or Register  to view this content.

I'm not a clairvoyant. It's hard debugging non published code. Please post your 'slightly' adapted code, preferably in a real workbook.

Why so complicated ?

Please Login or Register  to view this content.

If it runs fine on your PC I think it proves the code is correct.
Use all the debugging tools (F8, window direct, local variables) to see what's wrong on the other computer.

You'd better not use Activeworkbook but a correct reference to the workbook:

Sub SaveSrv()
-------
.Value=Workbooks("UPDATER.xls").Worksheets("STAFF").Range("A2:I65536")
--------
End Sub

Why do you want to close Excel ?
I think this is 'over the top' as a separate Sub ( it does the same as yours)

Please Login or Register  to view this content.

i find it easier to think of each part of the code as a separate module at first, then put them all together. I want to completely close excel as this workbook is just an updater the other workbook,

it is opened by a scheduler then the data on the sheet updates via the sql query, the other workbook is then opened, the data is copied over, then that work book is saved and closed. The new file is then emailed to an inbox where it can be uploaded to the intranet (some drupal watcher import module to made for this) and then the update workbook is saved and excel is completely closed until the next time the scheduler runs.

Will the .close True work to close excel entirely?

I have tried a few various methods to quit the application, but it seems to create an error (0) then resume (20) i think error.

I have use application.quit an api method can't remember of the top of my head, but they all have the same problem. One thing I will say is that this is running on a windows 2003 server and I have a feeling the scheduler opens excel then opens the updater workbook from inside that, so if I just use .close there is a book1 left open in the background. The problem with this is that the schedule is left running as the process needs teminating.

I think I rememeber faguely the other method i tried which was something like kill/f/mi "excel.exe" im not sure, but it didn't work, it did close excel but it created a backup file in the wrong format and saved it in the folder where the workbook is saved. The backup file was of the SAR spreadsheet named with a long number like 700008872, any ideas?