Vbscript automation of Excel for Bloomberg's BDP function results

[marconi8232]

Hi,

I want to use a vbscript code in Windows Task Scheduler in order to automate the task of retrieving real-time market price data in Excel with the use of the Bloomberg add-in's BDP function and then copy and paste these real-time data by value to an Excel file and then saving the Excel file with a timestamp in its name.
My problem is that the BDP function does not stop recalculating (it shows Retrieving data...) and I cannot save real-time market price data because of this.
I use Windows 7 and Excel 2007.
What may be the problem with my code below?

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->[FONT=&quot]Here is my vbscript code:
'It starts here
Dim strWorkerWB
[/FONT][FONT=&quot]Dim strWorkerWB2[/FONT]
[FONT=&quot] strWorkerWB = "C:\swaps\swapok2.xls"
strWorkerWB2="c:\blp\API\Office Tools\BloombergUI.xla"

Dim strMacroName
strMacroName = "swapmakro"

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

'I want to see what happens
myExcelWorker.Visible=True

'Loading in the Bloomberg add-in
myExcelWorker.Workbooks.Open (strWorkerWB2)

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Open the Workbook specified on the command-line
Dim oWorkBook

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB )

'on error resume next
' Run the calculation macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0

[/FONT][FONT=&quot]Dim StrPathNameNew[/FONT]
[FONT=&quot] StrPathNameNew = replace(ucase(strWorkerWB),".XLS","") & "_" & year(Date()) & right("0" & month(date()),2) & right("0" & day(date()),2)& cstr(right(now,5)) & ".XLS"
oWorkBook.SaveAs StrPathNameNew
oWorkBook.Close

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will shut those down also
if myExcelWorker.Workbooks.Count = 0 Then
myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
'It ends here



Here is my swapmakro macro in the swapok2.xls Modules:
'It start here
Sub swapmakro()
Application.Run "RefreshAllStaticData"
Application.Wait DateAdd("s", 20, Now)
Range("A2:M5").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
'It ends here[/FONT]

 

[anishdacool]

Hello marconi8232,

Were you able to resolve the issue. I want to perform a similar task but I am beginner in VBA coding. Please let me know.TIA.

Regards,
anishdacool