vb.net 操作 vba

請問前輩:

如何用vb.net啟動excel的vba呢???

謝謝

Update:

謝謝各位的意見~但是否可以有更清楚的範例呢???

小弟依李察兄的資訊嘗試的一下~但未成功~~>.<

郁平兄的建議也很棒~但~~沒寫過這樣的~~~(基礎不夠好)

是否能再詳細的說明呢?

2 Answers

Rating
  • 9 years ago
    Favorite Answer

    http://msdn.microsoft.com/en-us/library/aa159913(o...

    MSDN有答案

    2011-07-07 21:14:48 補充:

    我寫個範例吧

    先在EXCEL裡新增一個模組Module1

    寫兩個巨集

    Sub clear()

    Cells.Select

    Selection.ClearContents

    Range("A1").Select

    End Sub

    Sub Test()

    Dim ra As Range

    Set ra = Range("A2:j20")

    Dim r As Range

    For Each r In ra

    r.Value = Int(Rnd() * 80 + 20)

    Next

    Range("A21:J21").Select

    Selection.FormulaR1C1 = "=SUM(R[-19]C:R[-1]C)"

    Range("A1").Select

    End Sub

    然後存檔為Book1.xls

    放在專案目錄中的\bin\debug裡

    vb.net 程式如下

    Imports Excel = Microsoft.Office.Interop.Excel

    Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles Button1.Click

    Dim oExcel As Excel.ApplicationClass

    Dim oBook As Excel.WorkbookClass

    Dim oBooks As Excel.Workbooks

    'Start Excel and open the workbook.

    oExcel = CreateObject("Excel.Application")

    oExcel.Visible = True

    oBooks = oExcel.Workbooks

    oBook = oBooks.Open(Application.StartupPath & "\Book1.xls")

    'Run the subroutine.

    oExcel.Run("clear")

    MessageBox.Show("先呼叫巨集clear()來清除所有資料")

    oExcel.Run("Test")

    MessageBox.Show("再呼叫巨集Test()來填入資料")

    'Close the workbook and quit Excel.

    oBook.Save()

    oBook.Close()

    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)

    oBook = Nothing

    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)

    oBooks = Nothing

    oExcel.Quit()

    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

    oExcel = Nothing

    End Sub

    End Class

    2011-07-07 21:25:55 補充:

    重點是要引用

    Imports Excel = Microsoft.Office.Interop.Excel

    MSDN上的程式我幾乎沒有更動

    執行這個程式要記得關閉所有EXCEL視窗

    因為它沒有檢查目前是否有EXCEL被開啟

    (為了終於原味...MSDN上的程式碼)

  • 9 years ago

    滿有趣的;

    我不知道答案,但是,我會這樣做:

    1先在excel中設定檔案開啟時,即啟動vba的特定程式碼。

    2於vb.net中呼叫並啟動該excel檔案。

    如此,便可完成。

Still have questions? Get your answers by asking now.