Refresh SQL data link by macro instead of excels connection wizard

[mcinnes01]

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

Private Sub Workbook_Open()
Call Module2.SaveSrv
End Sub

Module 1

Sub CloseRoutine()
Dim UpdateBK

Set UpdateBK = Workbooks("UPDATER")

With UpdateBK
    .Save
    .Close
End With

End Sub

Module 2

Sub SaveSrv()
Dim MasterBk As Workbook
Dim UpdateBK As Workbook
Dim MSsht As Worksheet
Dim USsht As Worksheet

Set UpdateBK = Workbooks("UPDATER")
Set USsht = UpdateBK.Worksheets("STAFF")

Application.ScreenUpdating = False

Set MasterBk = Workbooks.Open("C:\Users\amcinnes\Desktop\TOOL BOX\DEVELOPMENT\SAR MASTER")
Set MSsht = MasterBk.Worksheets("MASTER")


MSsht.Range("A2:I65536").ClearContents

With USsht
     .Range("A2:I65536").Copy Destination:=MSsht.Range("A2")
End With

With MasterBk
    .Save
    .Close
End With

On Error Resume Next
Call Module3.EMAILLIST

End Sub

Module 3

Sub EMAILLIST()

    Dim cell As Object
    Dim NR As Long
    Dim tagerror As String
    Dim Email_Send_To, Email_Send_From, Email_Subject, Email_Body As String
    Dim strUserEmail As String
    Dim strFirstClassPassword As String
    Dim errPar As String
    Dim iMsg As Object
    Dim iConfig As Object
    Dim sConfig As Variant
    Dim Row As Integer
       
    
        
    strUserEmail = "test@email.ac.uk"
    strFirstClassPassword = "password"

    Set iMsg = CreateObject("CDO.Message")
    Set iConfig = CreateObject("CDO.Configuration")
    iConfig.Load -1
    Set sConfig = iConfig.Fields
        
        With sConfig
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.0.5" 'Name or IP of remote SMTP server
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25  'Server Port
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = strUserEmail
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strFirstClassPassword
            .Update
        End With
     
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
   

 '-----------------------------------------------------------------------------

    Email_Send_To = "test@email.ac.uk"
    Email_Send_From = "test@email.ac.uk"
    Email_Subject = "SAR UPLOAD"
    Email_Body = "Please upload to replace SAR MASTER.xls"

'-----------------------------------------------------------------------------
      

    

    With iMsg
        Set .Configuration = iConfig
    End With
    
            iMsg.To = Email_Send_To
            iMsg.From = Email_Send_From
            iMsg.Subject = Email_Subject
            iMsg.Textbody = Email_Body
            iMsg.AddAttachment "C:\Users\amcinnes\Desktop\TOOL BOX\DEVELOPMENT\SAR MASTER.xls"
            iMsg.Send

          On Error GoTo tagerror


If ActiveSheet.Range("a1") = "" Then
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True

    
Else
    Call Module1.CloseRoutine
End If

clean_up:
    With Application
       .EnableEvents = True
       .ScreenUpdating = True
    End With
        Call Module1.CloseRoutine

    
tagerror:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description & " at " & Err.Source, vbCritical
    Resume clean_up
    
End Sub

[johncassell]

Not looked fully at your code but I had this same problem. I had a macro that refreshed a query and then emailed it but the email was created before the query refreshed. The only way I've found around it is to pause the macro for x seconds using this code

Sub Pause(PauseTimeInSeconds)
' Pause for x seconds
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + PauseTimeInSeconds
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub

and if you wanted to pause for 5 seconds then you would put this in your code

Pause (5)

Maybe theres a way around it but Im not aware of it.

John

[mcinnes01]

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

Set X.qt = ActiveWorkbook.Sheets("STAFF").QueryTables(1)

Workbook

Private Sub Workbook_Open()
Call Module4.InitializeSQL
End Sub

Module 4

Dim X As New Class1

Sub InitializeSQL()

Set X.qt = ActiveWorkbook.Sheets("STAFF").QueryTables(1)
ActiveWorkbook.RefreshAll

End Sub

Class 1

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    If Success Then
        Call Module2.SaveSrv
    Else
        Exit Sub
    End If
End Sub

[sailepaty]

Search for the Enable background refresh property of your connections and be sure that is unchecked.

In Excel 2007 is in:

Data->Connections->
Select your connections
Properties

Regards

[mcinnes01]

Hi, thanks I have already done that, I think the issue is that I am refering to a query table,

1 i am not sure what a query table is as I have never used one ( unless it is the thing all that is created when the external data link is refreshed)

2 If this is the thing that is created when I refresh the external data link then I have no idea why I can't refer to in vba.

It seems like the Querytables(1) doesn't exist?

[sailepaty]

Hi, thanks I have already done that

If the Background is set correctly this may work.

 
Sub InitializeSQL()
    ActiveWorkbook.Connections("YOUR CONNECTION NAME").Refresh
End Sub

Regards

[mcinnes01]

That seems to work as far as triggering the refresh from a macro which is great! Do you know how I can now do a module that does the afterrefresh and does this need a class module? I read up a little on class modules and more or less understand the purpose of them, but I am struggling applying it as I haven't used one before.

[snb]

You won't need any class module.

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

Sub simple_snb()
  [A1].clearcontents
  sheets(1).querytables(1).refresh False
  do
    doevents
  loop unitl [A1]<>""
End Sub

[mcinnes01]

how do I find out what the query table is called?

Is the query table the tabale the data comes in from the external source?

I tried this, a slight adaptation of what you said and I got the error: compile error: wrong number of arguments or invalid property assignment

[snb]

Why so complicated ?

Sub SaveSrv()
  With Workbooks.Open("C:\Users\amcinnes\Desktop\TOOL BOX\DEVELOPMENT\SAR MASTER")
    with .Worksheets("MASTER").Range("A2:I65536")
       .clearcontents
       .Value=Workbooks("UPDATER").Worksheets("STAFF").Range("A2:I65536")
    end with
    .Close True
  End With
End Sub

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.

[mcinnes01]

I have it working on the server now, but I have had to change the code so it doesn't call the updater workbook by its name, but by activework. I dont know if this is necessarily the best way of refering to things, but it seems to work:

example

Sub SaveSrv()
Dim MasterBk As Workbook
Dim UpdateBK As Workbook
Dim MSsht As Worksheet
Dim USsht As Worksheet

Set UpdateBK = Activeworkbook
Set USsht = UpdateBK.Worksheets("STAFF")

Application.ScreenUpdating = False

Set MasterBk = Workbooks.Open("X:\PAYROLL\PAYROLL PROCESSING - NEW\ANDYS PROJECTS\NEW TOOL BOX\SAR.xls")
Set MSsht = MasterBk.Worksheets("MASTER")


MSsht.Range("A2:I65536").ClearContents

With USsht
     .Range("A2:I65536").Copy Destination:=MSsht.Range("A2")
End With

With MasterBk
    .Save
    .Close
End With

On Error Resume Next
Call Module3.EMAILLIST

End Sub

My other issue is on the closeroutine it doesn't completely close excel just the workbook. I have tried the application.quit, but this results in an error (0) and it gets stuck in an error loop requiring a ctrl alt delete on excel.

Any ideas?

Sub CloseRoutine()
Dim UpdateBK

Set UpdateBK = Workbooks("UPDATER")

With UpdateBK
    .Save
    .Close
End With
End Sub

[snb]

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

[mcinnes01]

Thanks snb, I will make that change when I get back on the server later.

[mcinnes01]

any ideas to deal with the application.quit?

[snb]

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

Sub CloseRoutine()
   Workbooks("UPDATER.xls").Close True
End Sub

[mcinnes01]

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?

[mcinnes01]

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?