如何在Powershell脚本中使用VBA代码 [英] How to use VBA Code inside a Powershell Script

问题描述

我想使用PowerShell将一些原始数据放入新的Excel文件中.我知道如何使用PowerShell提供的comobject Excel.Application.

现在，我的问题是，必须在Excel内部(在脚本运行时)以不同的方式处理数据.我已经创建了一些VBA宏，但不久前我想重用它们.是否可以通过新创建并填写好的Excel中的脚本使用" VBA?还是我需要在Powershell中重新创建VBA?

解决方案

实质上，VBA是一种连接到Excel对象库的语言.PowerShell也是一种可以连接到Excel对象库的语言.其他的还包括 Java ，C# ， PHP ，R ，还有更多通过 '早期绑定设置xlObj = New Excel.Application设置accObj = New Access.Application设置wrdObj = New Word.Application...后期绑定设置xlObj = CreateObject("Excel.Application")设置accObj = CreateObject("Access.Application")设置wrdObj = CreateObject("Word.Application")...

PowerShell

 <代码> $ xlObj = new-object -comobject excel.application$ accObj =新对象-comobject access.application$ wrdObj =新对象-comobject word.application... 

打开Office对象，使用Excel的 Workbooks.Open ，访问权限文档.打开.注意:这些方法都不限于VBA.

• VBA

   <代码>设置xlWB = xlObj.Workbooks.Open("C:\ Path \ To \ Workbook.xlsx")设置accDB = accObj.OpenCurrentDatabase("C:\ Path \ To \ Access.accdb")设置wrdDOC = wrdObj.Documents.Open("C:\ Path \ To \ Document.docx")... 

• PowerShell

   <代码> $ xlWB = $ xlObj.Workbooks.Open("C:\ Path \ To \ Workbook.xlsx")$ accDB = $ accObj.OpenCurrentDatabase("C:\ Path \ To \ Access.accdb")$ wrdDOC = $ wrdObj.Documents.Open("C:\ Path \ To \ Document.docx")... 

句柄集合(例如Excel工作表，Access表，Word段落).

• VBA

   '选择单个对象设置xlSheet = xlWB.Worksheets("mySheet")设置accTable = accObj.Currentdb().TableDefs("myTable")设置wrdParag = wrdDOC.Paragraphs(1)... 

• PowerShell

   <代码>#EXCEL WORKSHEETS LOOP$ xlObj =新对象-comobject excel.application$ xlWB = $ xlObj.Workbooks.Open("C:\ Path \ To \ Workbook.xlsx")Foreach($ xlWB.Worksheets中的$ wsh){写主机$ wsh.name}$ xlWB.Close($ false)$ xlObj.quit()[System.Runtime.Interopservices.Marshal] :: ReleaseComObject($ xlObj)#访问数据库表循环$ accObj =新对象-comobject access.application$ accDB = $ accObj.OpenCurrentDatabase("C:\ Path \ To \ Access.accdb")Foreach($ accObj.CurrentDb().TableDefs()中的$ tbl){写主机$ tbl.name}$ accDB.DoCmd.CloseDatabase$ accObj.quit()[System.Runtime.Interopservices.Marshal] :: ReleaseComObject($ accObj) 

I want to put some Raw Data into a new Excel File with PowerShell. I'm aware how to use the comobject Excel.Application which PowerShell delivers.

Now my problem is the Data has to be processed in different ways inside the Excel (while the script is running). I already created some VBA Macros to do so a while back and now I'd like to reuse them. Is it possible to "use" the VBA through the Script inside the newly created and filled up Excel? Or do I need to recreate the VBA in Powershell?

解决方案

Essentially, VBA is a language that connects to the Excel object library. PowerShell is also a language that can connect to Excel object library. Still others including Java, C#, Python, PHP, R, and more have this facility via Component Object Model (COM) available in Windows environments.

Therefore, simply translate your VBA code to PowerShell as any Excel method should be available. VBA tends to be tied to MS Office products because it is the defacto connected language but is actually a separate component (see how it is first reference checked under Tools\References... in IDE). Below are examples of simpler VBA to PowerShell translations:

1. Initialize Office objects

   • VBA

     ' EARLY BINDING
     Set xlObj = New Excel.Application
     Set accObj = New Access.Application
     Set wrdObj = New Word.Application
     ...
     
     ' LATE BINDING
     Set xlObj = CreateObject("Excel.Application")
     Set accObj = CreateObject("Access.Application")
     Set wrdObj = CreateObject("Word.Application")
     ...
     

   • PowerShell

     $xlObj = new-object -comobject excel.application
     $accObj = new-object -comobject access.application
     $wrdObj = new-object -comobject word.application
     ...
     

2. Open Office objects with Excel's Workbooks.Open, Access' OpenCurrentDatabase, Word's Documents.Open. Note: None of these methods are restricted to VBA.

   • VBA

     Set xlWB = xlObj.Workbooks.Open("C:\Path\To\Workbook.xlsx")
     Set accDB = accObj.OpenCurrentDatabase("C:\Path\To\Access.accdb")
     Set wrdDOC = wrdObj.Documents.Open("C:\Path\To\Document.docx")
     ...
     

   • PowerShell

     $xlWB = $xlObj.Workbooks.Open("C:\Path\To\Workbook.xlsx")
     $accDB = $accObj.OpenCurrentDatabase("C:\Path\To\Access.accdb")
     $wrdDOC = $wrdObj.Documents.Open("C:\Path\To\Document.docx")
     ...
     

3. Handle collections (e.g., Excel sheets, Access tables, Word paragraphs).

   • VBA

     ' SELECT SINGLE OBJECTS
     Set xlSheet = xlWB.Worksheets("mySheet")
     Set accTable = accObj.Currentdb().TableDefs("myTable")
     Set wrdParag = wrdDOC.Paragraphs(1)
     ...
     

   • PowerShell

     # EXCEL WORKSHEETS LOOP
     $xlObj = new-object -comobject excel.application
     
     $xlWB = $xlObj.Workbooks.Open("C:\Path\To\Workbook.xlsx")
     
     Foreach($wsh in $xlWB.Worksheets)
     {
        Write-host $wsh.name
     }            
     
     $xlWB.Close($false)
     $xlObj.quit()
     
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlObj )
     
     
     # ACCESS DB TABLES LOOP
     $accObj = new-object -comobject access.application    
     $accDB = $accObj.OpenCurrentDatabase("C:\Path\To\Access.accdb")
     
     Foreach($tbl in $accObj.CurrentDb().TableDefs())
     {
        Write-host $tbl.name
     }
     
     $accDB.DoCmd.CloseDatabase
     $accObj.quit()
     
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($accObj )
     

这篇关于如何在Powershell脚本中使用VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！