Refresh embedded queries automatically

[tonycuban]

I have a file with embedded queries which I want to save to a different location, refresh all and save the file. The refresh All does not work because the file is locked by myself.
The refresh on open, doesn't work because i must prompt the user for input so the file doesn't get refresh when is open after been saved.

Any ideas would be appreciated. it would be perfect if there is a way to refresh embedded queries in a closed file.

the code is:

Function Create_Rec_XLfile()
Dim objXL As Excel.Application, objBook As Excel.Workbook
Dim strRDATE As String, strSource As String, strDestination As String

strRDATE = Format(DLookup("[rptDate]", "SD_DATA"), "yyyymmdd")
strSource = "\\main\Excel_Templates\RecToEntries.xls"
strDestination = "\\main\2011\" & strRDATE & "_RecToEntries.xls"

If Dir(strDestination) <> "" Then Kill strDestination

FileCopy strSource, strDestination

Set objXL = CreateObject("Excel.Application")
Set objBook = objXL.Workbooks.Open(strDestination)

objBook.RefreshAll

objBook.Save
objBook.Close
objXL.Quit
Set objBook = Nothing
Set objXL = Nothing
End Function