Hello,
first of all I'm a rookie in programming with Visual Basic. My purpose is to programme the following tool:
It should open a particular Excel-workbook (WB1) at 12 o'clock, copy values of a few cells and write them into a further workbook (WB2) with current time and date. Then it should save the "further workbook" and both workbooks.
So far, so god.
I write a Sub, which does all these things, when it's activated. But here is my problem:
The values of WB1 are numbers from the Bloomberg-Add-In, which are updated every second. But if I start the Excel-Workbook with a VBScript, I get an "#NAME?" error in all the cells and I can't refresh the Bloomberg values, neither by the Bloomberg Ribbon, nor by a VBA Code like "Application.Run "RefreshEntireWorkbook". Bloomberg also tells me that the "Bloomberg Excel Tools is either corrupt or missing." So I add "Application.VBE.ActiveVBProject.References.AddFromFile "C:\....\Office Tools\BloombergUI.xla"" into my macro, but it also doesn't help.
Maybe anyone had a similar problem and can help me
PS: The Problem was discussed a few years ago, but the answers weren't satisfactorily.
http://www.mrexcel.com/forum/excel-questions/572195-name-errors-excel-addin-loaded-through-vbscript.html
VBScript:
first of all I'm a rookie in programming with Visual Basic. My purpose is to programme the following tool:
It should open a particular Excel-workbook (WB1) at 12 o'clock, copy values of a few cells and write them into a further workbook (WB2) with current time and date. Then it should save the "further workbook" and both workbooks.
So far, so god.
I write a Sub, which does all these things, when it's activated. But here is my problem:
The values of WB1 are numbers from the Bloomberg-Add-In, which are updated every second. But if I start the Excel-Workbook with a VBScript, I get an "#NAME?" error in all the cells and I can't refresh the Bloomberg values, neither by the Bloomberg Ribbon, nor by a VBA Code like "Application.Run "RefreshEntireWorkbook". Bloomberg also tells me that the "Bloomberg Excel Tools is either corrupt or missing." So I add "Application.VBE.ActiveVBProject.References.AddFromFile "C:\....\Office Tools\BloombergUI.xla"" into my macro, but it also doesn't help.
Maybe anyone had a similar problem and can help me
PS: The Problem was discussed a few years ago, but the answers weren't satisfactorily.
http://www.mrexcel.com/forum/excel-questions/572195-name-errors-excel-addin-loaded-through-vbscript.html
VBScript:
Code:
Option Explicit
Const sPath="\\User\....\Desktop\exceltest\"
Const sXL1="test2.xlsm"
Dim xlApp
Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.Visible = true
xlApp.Workbooks.Open sPath & sXL1, true
