refresh QueryTablesand add delay

[hrm1220]

I'm very new to vb script. I've looked on the internet and haven't quite found what I need. I have a script that works for the most part, but it doesn't give excel time to refresh all the QueryTables (takes about 3min to refresh). So, I'm wondering if there is code to put in for delay before it saves the file? Or is there another way to do this?

sub main
 dim objWorkbook
    
    '======================================================    
    myPath = "C:\test"
    
 
    'The Excel file to be converted
    strInput = myPath& "VF_h.xls"

    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = FALSE
    objExcel.Visible = True
     
    Set objWorkbook = objExcel.Workbooks.Open(strInput)
     
    Call objWorkbook.RefreshAll()
   
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit


end sub

Any help is most appreciated.thank you

 

[tsuji]

If you want refreshall() to be perform synchronously, you set the backgrounquery property to false.
ref

http://msdn.microsoft.com/en-us/library/bb213270.aspx

 

[hrm1220]

thank you for that.
when it's running it shows that it's completed, but when I open the file the MSQuery tables are not refreshed.

This script is run in the middle of the night.
My next question is how do I enable the data connection through code?

here's what I have so far:

    strInput = myPath & "test lookup.xls"
startTime = Now
'boolreadonly=True
'H = Hours
'N = Minutes
'S = Seconds
endTime = DateAdd("N",3,startTime)
set objFSO = CreateObject("Scripting.FileSystemObject")
set objFile = objFSO.GetFile(strInput)

x=0
    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = FALSE
    objExcel.Visible = False
    Set objWorkbook = objExcel.Workbooks.Open(strInput,,False)


With objWorkbook.Connections("Connection").ODBCConnection
    .EnableRefresh
    .Refresh
End With

With objWorkbook.Connections("Connection1").ODBCConnection
    .EnableRefresh
    .Refresh
End With
        
  
    Do Until Now >= endTime
       x=x+1
    Loop
 objWorkbook.Save 




   objWorkbook.Close
   objExcel.Application.Quit

 

[HughLerwill]

I cannot comment on your code in general however I think .EnableRefresh is a Property not a Method so if you are wishing to set (Let) it you should be using .EnableRefresh = True

 

[tsuji]

[1] This can be real busy.
Do Until Now >= endTime
x=x+1
Loop
You can replace it by this if you really mean for wscript.
[tt] wscript.sleep 3*60*1000 'it should not be needed, see [3.2]
[/tt]
[2] You should not put .DisplayAlerts=false at the position that far atop. You should put it just before .Save and promptly reset it to .DisplayAlerts=true thereafter.

[3] On the connection part
[3.1] Do as HughLerwill mentioned judiciously.
[3.2] Since you use ODBCConnection object, you should take full profit of its functionality exposed, in particular, use its .refreshing property
[tt]
With objWorkbook.Connections("Connection").ODBCConnection
.EnableRefresh[red]=true[/red]
[red]'[/red][blue].backgroundquery=false 'either this[/blue]
.Refresh
[blue]do while .refreshing 'or this loop
wscript.sleep 500
loop[/blue]
End With

With objWorkbook.Connections("Connection1").ODBCConnection
.EnableRefresh[red]=true[/red]
[red]'[/red][blue].backgroundquery=false 'either this[/blue]
.Refresh
[blue]do while .refreshing 'or this loop
wscript.sleep 500
loop[/blue]
End With

[red]'[/red]Do Until Now >= endTime
[red]'[/red]+1
[red]'[/red]Loop

[blue]objExcel.DisplayAlerts=false[/blue]
objWorkbook.Save
[blue]objExcel.DisplayAlerts=true[/blue]
[/tt]

 

[hrm1220]

I appreciate the inputs, but it seems that vb script doesn't like "With objWorkbook.Connections("Connection1").ODBCConnection" or "objWorkbook.Connections("Connection1").ODBCConnection.Refresh"

So, I'm not sure what to do. All I would like to do is have it open a workbook in excel 2007 and refresh the data.

Is there a way to enable the refresh or go to trust center to enable data connection in vb script?