VBA调用javascript

2014-07-28 19:50:02 

函数接口

1. Function execJSFunc(filePath, funcName)
2. Dim code
3. Open filePath For Input As #1
4. Do While Not EOF(1)
5. Line Input #1, tmpCode
6. code = code & tmpCode & Chr(13)
7. Loop
8. Close #1
9. Set JS = CreateObject('ScriptControl')
10. JS.Language = 'JScript'
11. JS.AddCode code
12. Dim result
13. result = JS.run(funcName, ThisWorkbook)
14. execJSFunc = result
15. End Function

调用封装

Sub run(funcName)Dim path, fileName, pos, resultpath = ThisWorkbook.pathpos = InStr(4, ThisWorkbook.Name, '.', 1)pos = Len(ThisWorkbook.Name) - 4fileName = Mid(ThisWorkbook.Name, 1, pos - 1)path = path + '\' + fileName + '.js'result = execJSFunc(path, funcName)Debug.Print resultEnd Sub

调用示例

1. Sub 按钮1_Click()
2. run('hello')
3. End Sub

test.js源码

function hello(workbook) {var sheets = workbook.sheets;sheets('Sheet1').range('a3').value = 55555;return workbook.sheets.count;}