VB6(Visual Basic 6.0)から Excel を使う

VB6 から単純に Cell に文字を入力したり、色を変えたり、Cell の値を読んだりします。めも書きです。
※動作に使用した環境は VB6.0 と Microsoft Office 2003 です。
サンプルのダウンロード

このカテゴリーの先頭へ このページの先頭へ

■サンプルの説明

起動したところ
Command1 を押下すると、TextBox に書いてある文字を A1 セルに書き出します。
Command2 を押下すると、ファイル(読み込むファイルはプログラム内に定数で書いてあります)を読み込み、A1 の値をメッセージ表示します。

このカテゴリーの先頭へ このページの先頭へ

■コード

Form1.frm
Option Explicit

Private Const EXCEL_OBJECT_NAME As String = "Excel.Application"
Private Const SAVE_FILE_PATH As String = "D:\test.xls"
Private Const READ_FILE_PATH As String = "D:\test.xls"

' 書き込み
Private Sub Command1_Click()
On Error GoTo ErrorHandler
    
    ''' Excel オブジェクトを作成する
    Dim excelApplication As Object
    Set excelApplication = CreateObject(EXCEL_OBJECT_NAME)
    ' メッセージを表示しないようにする
    Call CallByName(excelApplication, "DisplayAlerts", VbLet, False)
    ' 表示しない
    Call CallByName(excelApplication, "Visible", VbLet, False)
    
    ''' WorkBooksObject を取得
    Dim excelWorkbooks As Object
    Set excelWorkbooks = CallByName(excelApplication, "Workbooks", VbGet)
    
    ''' WorkBookObject を取得
    Dim excelWorkBook As Object
    Set excelWorkBook = CallByName(excelWorkbooks, "Add", VbMethod)
    
    ''' WorkSheets を取得
    Dim excelSheets As Object
    Set excelSheets = CallByName(excelWorkBook, "Sheets", VbGet)
    
    ''' WorkSheet を取得
    Dim excelSheet As Object
    Set excelSheet = CallByName(excelSheets, "Item", VbGet, 1)

    ''' Cells を取得
    Dim excelCells As Object
    Set excelCells = CallByName(excelSheet, "Cells", VbGet)

    ''' Range を取得
    Dim excelRange As Object
    Set excelRange = CallByName(excelCells, "Item", VbGet, 1, 1)

    ' ExcelRange の操作
    ' 文字を入力
    Call CallByName(excelRange, "Value", VbLet, Me.Text1.Text)
    
    ' 背景色を設定
    Dim excelCellInterior As Object
    Set excelCellInterior = CallByName(excelRange, "Interior", VbGet)
    Call CallByName(excelCellInterior, "ColorIndex", VbLet, 6)
    
    ' 文字の設定
    Dim excelCellFont As Object
    Set excelCellFont = CallByName(excelRange, "Font", VbGet)
    Call CallByName(excelCellFont, "ColorIndex", VbLet, 3)
    
    ' AutoFit する
    Dim excelEntireColumn As Object
    Set excelEntireColumn = CallByName(excelCells, "EntireColumn", VbGet)
    Call CallByName(excelEntireColumn, "AutoFit", VbMethod)
    
    ' 保存
    Call CallByName(excelWorkBook, "SaveAs", VbMethod, SAVE_FILE_PATH)
    
    Call MsgBox("おわりました", vbInformation + vbOKOnly)
    
ErrorHandler:
On Error GoTo Finally
    If Not excelWorkBook Is Nothing Then
        Call CallByName(excelWorkBook, "Close", VbMethod)
    End If
    If Not excelApplication Is Nothing Then
        Call CallByName(excelApplication, "DisplayAlerts", VbLet, True)
        Call CallByName(excelApplication, "Quit", VbMethod)
    End If
Finally:
    Set excelEntireColumn = Nothing
    Set excelCellFont = Nothing
    Set excelCellInterior = Nothing
    Set excelRange = Nothing
    Set excelCells = Nothing
    Set excelSheet = Nothing
    Set excelSheets = Nothing
    Set excelWorkBook = Nothing
    Set excelWorkbooks = Nothing
    Set excelApplication = Nothing
End Sub

' 読み込み
Private Sub Command2_Click()
On Error GoTo ErrorHandler
    
    ''' Excel オブジェクトを作成する
    Dim excelApplication As Object
    Set excelApplication = CreateObject(EXCEL_OBJECT_NAME)
    ' メッセージを表示しないようにする
    Call CallByName(excelApplication, "DisplayAlerts", VbLet, False)
    ' 表示しない
    Call CallByName(excelApplication, "Visible", VbLet, False)
    
    ''' WorkBooksObject を取得
    Dim excelWorkbooks As Object
    Set excelWorkbooks = CallByName(excelApplication, "Workbooks", VbGet)
    
    ''' WorkBookObject を取得(ファイルを開く)
    Dim excelWorkBook As Object
    Set excelWorkBook = CallByName(excelWorkbooks, "Open", VbMethod, READ_FILE_PATH)
    
    ''' WorkSheets を取得
    Dim excelSheets As Object
    Set excelSheets = CallByName(excelWorkBook, "Sheets", VbGet)
    
    ''' WorkSheet を取得
    Dim excelSheet As Object
    Set excelSheet = CallByName(excelSheets, "Item", VbGet, 1)

    ''' Cells を取得
    Dim excelCells As Object
    Set excelCells = CallByName(excelSheet, "Cells", VbGet)

    ''' Range を取得
    Dim excelRange As Object
    Set excelRange = CallByName(excelCells, "Item", VbGet, 1, 1)

    ' ExcelRange の操作
    Dim value As String
    value = CStr(CallByName(excelRange, "Value", VbGet))
    
    Call MsgBox("A1:" & value, vbInformation + vbOKOnly)
    
ErrorHandler:
On Error GoTo Finally
    If Not excelWorkBook Is Nothing Then
        Call CallByName(excelWorkBook, "Close", VbMethod)
    End If
    If Not excelApplication Is Nothing Then
        Call CallByName(excelApplication, "DisplayAlerts", VbLet, True)
        Call CallByName(excelApplication, "Quit", VbMethod)
    End If
Finally:
    Set excelRange = Nothing
    Set excelCells = Nothing
    Set excelSheet = Nothing
    Set excelSheets = Nothing
    Set excelWorkBook = Nothing
    Set excelWorkbooks = Nothing
    Set excelApplication = Nothing
End Sub
このカテゴリーの先頭へ このページの先頭へ

■参考資料

CallByName 関数のプロシージャを実行するための使用方法