プログラミングのメモ

プログラミングの学び直し備忘録

01:入門/基礎 項目

準備

インストール

コーディング/コンパイル/実行

C#

統合環境/エディタ

統合環境でコーディング/コンパイル/実行

C#

Java

Hello World

入門/基礎

コメント

変数/データ型/配列(リスト)

フロー制御

基礎

関数

配列/リスト

辞書

文字列

VBA:Excel :App

画面更新

    Application.ScreenUpdating = True
    Application.ScreenUpdating = False

ファイルを開く
[ファイル]-[開く]-[参照]

  • 開く
  • 読み取り専用
  • コピー
  • 保護ビュー
  • 開いて修復...
'**
'* ファイル選択
'*
'* @param       {}
'* @param_ref   {}
'* @return      {}
'*
'* @note
'*
Public Function mthSelectFileDialog( _
                                    Optional sPath As String _
                                    ) As String

    Dim sRes As String

    '================================
    '// 初期フォルダ
    '================================
    If sPath = "" Then
        sPath = ThisWorkbook.Path
    End If
    
    If Not mFSO.FileExists(sPath) Then sPath = ThisWorkbook.Path
    
    '===========================================================
    '// ファイル選択(ダイアログ)
    '// https://excel-ubara.com/excelvba1/EXCELVBA376.html
    '===========================================================
    Dim fd As FileDialog
    
    '//
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = sPath
        .ButtonName = "開く"
        .Filters.Clear
        .Filters.Add "Excelファイル", "*.xls"
    End With
    
    Dim lRet As Long
    lRet = fd.Show
    If lRet = 0 Then
        Set fd = Nothing
        Exit Function
    End If
    
    mthSelectFileDialog = fd.SelectedItems.Item(1)

End Function

R1C1参照形式
[ファイル]-[オプション]-[数式]-[数式の処理]
[R1C1参照形式]

    Application.ReferenceStyle = IIf(Application.ReferenceStyle = xlR1C1, xlA1, xlR1C1)

計算方法の設定
[ファイル]-[オプション]-[数式]-[計算方法の設定]

'**
'* 計算方法の設定
'*
'* Property Calculation As XlCalculation
'*    Excel.Application のメンバー
'*----------------------------------------------------------
'* Sub Calculate()
'*    Excel.Application のメンバー
'*
'* Sub Calculate()
'*    Excel.Worksheet のメンバー
'*
'* Function Calculate()
'*    Excel.Range のメンバー
'*
'*
'* parg     {}
'* @arg_ref {}
'* @arg_opt {}
'* @ret     {}
'*
'* @note
'*
Public Sub mthCalculation()
    
    '=============================
    '// Application
    '=============================
    Dim app As Application
    Set app = Application
    
    With app
        .Calculation = xlCalculationManual     '手動計算
        .Calculate                             '計算実行
        .Calculation = xlCalculationAutomatic  '自動計算
    End With

    '=============================
    '// WorkBook
    '=============================
    Dim wb As Workbook
    Set wb = app.Workbooks(ThisWorkbook.Name)
    With wb
    End With

    '=============================
    '// WorkSheet
    '=============================
    Dim ws As Worksheet
    Set ws = wb.Worksheets(wb.Sheets(1).Name)
    With ws
        .Calculate                             '計算実行
    End With
    
    '=============================
    '// Range
    '=============================
    Dim rng As Range
    Set rng = ws.Range(Cells(2, 1), Cells(10, 10))
    With rng
        .Calculate                             '計算実行
    End With
    
    
    Set ws = Nothing
    Set wb = Nothing
    Set app = Nothing
    
End Sub

VBA:Excel :FSO

ファイル

存在確認

'**
'* 【 File 】
'*  存在確認:FileExists
'*
'* Function FileExists(FileSpec As String) As Boolean
'*
'* @param       {}
'* @param_ref   {}
'* @return      {}
'*
'* @note
'*
Public Function FileExists(FileSpec As String) As Boolean

    Dim bRes As Boolean

    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    
    bRes = FSO.FileExists(FileSpec)
    
    Set FSO = Nothing
    
    FileExists = bRes
    
End Function

移動

削除

'**
'* 【File】
'* 削除:DeleteFile
'*
'* Sub DeleteFile(
'*            FileSpec As String,
'*            [Force As Boolean = False]
'*            )
'*
'* @arg     {str}   削除するファイル名
'*                   パスの最終にワイルドカード使用可
'* @arg_opy {bln}   True:読取専用も削除
'* @arg_ref {}
'* @ret     {}
'*
'* @note
'*  存在しないファイル名を指定するとエラー
'*
Public Sub DeleteFile( _
                    sFileSpec As String, _
                    Optional bForce As Boolean = False _
                    )

    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    
    Call FSO.DeleteFile(sFileSpec, bForce)

    Set FSO = Nothing
    
End Sub

ファイル名 取得

'**
'* 【 File 】
'*  ファイル名を返す:GetFileName
'*
'* Function GetFileName(Path As String) As String
'*
'* @param       {}
'* @param_ref   {}
'* @return      {}
'*
'* @note
'*
Public Function GetFileName(Path As String) As String

    Dim sRes As String

    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    
    sRes = FSO.GetFileName(Path)
    
    Set FSO = Nothing
    
    GetFileName = sRes
    
End Function

ファイル名(拡張子なし) 取得

拡張子 取得

コピー

'**
'* 【 File 】
'*  コピー:CopyFile
'*
'* Sub CopyFile(
'*              Source As String,
'*              Destination As String,
'*              [OverWriteFiles As Boolean = True]
'*                )
'*
'* @param       {}
'* @param_ref   {}
'* @return      {}
'*
'* @note
'*  ワイルドカード使用可
'*
Public Sub CopyFile( _
                    sSource As String, _
                    sDestination As String, _
                    Optional bOverWriteFiles As Boolean = True _
                    )

    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    
    Call FSO.CopyFile( _
                        sSource, _
                        sDestination, _
                        bOverWriteFiles _
                        )
                    
    Set FSO = Nothing
    
End Sub

フォルダ

【 参考 】

FileSystemObject

C#:Excel(COM)

ひな形サンプル

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// 参照設定
// Microsoft Excel 16.0 Object Library

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

/// <summary>
/// 
/// </summary>
namespace Excel_COM_2.ExcelCOM_01
{
    /// <summary>
    /// 
    /// </summary>
    class clsExcelCOM_01
    {
        /// <summary>
        /// 
        /// </summary>
        public static void mth_Main()
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = true; //表示・非表示

            try
            {
                Excel.Workbooks wbs = app.Workbooks;
                try
                {
                    string sFile_fp = @"D:\COM_TEST.xlsx";
                    Excel.Workbook wb = wbs.Open(
                                                    Filename:sFile_fp,
                                                    ReadOnly:true
                                                    );
                    try
                    {

                        Excel.Sheets wss = wb.Sheets;
                        try
                        {
                            //Worksheet ws = wss[1];
                            Excel.Worksheet ws = wss["シート(No2)"];
                            try
                            {
                                //================================
                                // 最終行/最終列
                                //================================
                                int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row;
                                int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row;

                                //===============================================
                                // 使用範囲を一括で二次元配列にコピー
                                //===============================================
                                Object[,] rangeArray;
                                Excel.Range rng = ws.UsedRange;
                                try
                                {
                                    rangeArray = rng.Value;
                                }
                                finally { Marshal.ReleaseComObject(rng); }

                                


                                // 二次元配列に対してループを回す
                                int lastRow = rangeArray.GetLength(0);
                                int lastCol = rangeArray.GetLength(1);
                                for (int r = 1; r <= lastRow; r++)
                                {
                                    //Console.WriteLine(r);
                                    for (int c = 1; c <= lastCol; c++)
                                    {
                                        Console.WriteLine(rangeArray[r, c]);
                                    }
                                }
                            }
                            finally { Marshal.ReleaseComObject(ws); }
                        }
                        finally { Marshal.ReleaseComObject(wss); }
                    }
                    finally
                    {
                        //if (wb != null)
                        //{
                        //    wb.Close(false);
                        //}
                        Marshal.ReleaseComObject(wb);
                    }
                }
                finally { Marshal.ReleaseComObject(wbs); }
            }
            finally
            {
                //if (app != null)
                //{
                //    app.Quit();
                //}
                Marshal.ReleaseComObject(app);
            }
        }


    }
}

Find

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// 参照設定
// Microsoft Excel 16.0 Object Library

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_COM_2.ExcelCOM_01
{
    class clsFind
    {
        public static void mth_Main()
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = true; //表示・非表示
            try
            {
                Excel.Workbooks wbs = app.Workbooks;
                try
                {
                    string sFile_fp = @"D:\COM_TEST.xlsx";
                    Excel.Workbook wb = wbs.Open(
                                                    Filename: sFile_fp,
                                                    ReadOnly: true
                                                    );
                    try
                    {
                        Excel.Sheets wss = wb.Sheets;
                        try
                        {
                            //Worksheet ws = wss[1];
                            Excel.Worksheet ws = wss["シート(No2)"];
                            try
                            {
                                //================================
                                // 最終行/最終列
                                //================================
                                int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row;
                                int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row;

                                //===============================================
                                // 使用範囲を一括で二次元配列にコピー
                                //===============================================
                                Excel.Range targetRange = null;
                                Excel.Range firstFind = null;
                                Excel.Range currentFind = null;

                                try
                                {
                                    //targetRange = ws.Columns[colName]; //シート内での検索範囲
                                    //targetRange = ws.Columns[1]; //シート内での検索範囲
                                    targetRange = ws.Cells; //シート内での検索範囲

                                    currentFind = targetRange.Find(
                                                                    "s", //検索キー 
                                                                    Type.Missing,
                                                                    Excel.XlFindLookIn.xlValues,
                                                                    Excel.XlLookAt.xlPart,
                                                                    Excel.XlSearchOrder.xlByRows,
                                                                    Excel.XlSearchDirection.xlNext,
                                                                    false,
                                                                    Type.Missing,
                                                                    Type.Missing
                                                                    );

                                    while (currentFind != null)
                                    {
                                        if (firstFind == null)
                                        {
                                            firstFind = currentFind;
                                        }
                                        else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
                                        {
                                            break;
                                        }
                                        currentFind = targetRange.FindNext(currentFind);
                                        Console.WriteLine(currentFind.Address);
                                        currentFind.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
                                    }
                                }
                                finally
                                {
                                    if (currentFind != null) Marshal.ReleaseComObject(currentFind);
                                    if (firstFind != null) Marshal.ReleaseComObject(firstFind);
                                    Marshal.ReleaseComObject(targetRange);
                                }

                            }
                            finally { Marshal.ReleaseComObject(ws); }
                        }
                        finally { Marshal.ReleaseComObject(wss); }

                        wb.SaveAs(@"D:\COM_TEST_2.xlsx");
                    }
                    finally
                    {
                        if (wb != null)
                        {
                            wb.Close(false);
                        }
                        Marshal.ReleaseComObject(wb);
                    }
                }
                finally { Marshal.ReleaseComObject(wbs); }
            }
            finally
            {
                if (app != null)
                {
                    app.Quit();
                }
                Marshal.ReleaseComObject(app);
            }
        }

        public static void mth_Mai_BK()
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Excel.Workbooks wbs = app.Workbooks;
                try
                {
                    string sFile_fp = @"D:\COM_TEST.xlsx";
                    Excel.Workbook wb = wbs.Open(sFile_fp);
                    try
                    {
                        Excel.Sheets wss = wb.Sheets;
                        try
                        {
                            //Worksheet ws = wss[1];
                            Excel.Worksheet ws = wss["シート(No2)"];
                            try
                            {
                                //================================
                                // 最終行/最終列
                                //================================
                                int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row;
                                int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row;

                                //===============================================
                                // 使用範囲を一括で二次元配列にコピー
                                //===============================================
                                Excel.Range targetRange = null;
                                Excel.Range firstFind = null;
                                Excel.Range currentFind = null;

                                try
                                {
                                    //targetRange = ws.Columns[colName]; //シート内での検索範囲
                                    targetRange = ws.Columns[1]; //シート内での検索範囲

                                    currentFind = targetRange.Find(
                                                                    14, //検索キー 
                                                                    Type.Missing, 
                                                                    Excel.XlFindLookIn.xlValues, 
                                                                    Excel.XlLookAt.xlPart,
                                                                    Excel.XlSearchOrder.xlByRows, 
                                                                    Excel.XlSearchDirection.xlNext, 
                                                                    false,
                                                                    Type.Missing, 
                                                                    Type.Missing
                                                                    );

                                    while (currentFind != null)
                                    {
                                        if (firstFind == null)
                                        {
                                            firstFind = currentFind;
                                        }
                                        else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
                                        {
                                            break;
                                        }
                                        currentFind = targetRange.FindNext(currentFind);
                                        Console.WriteLine(currentFind.Address);
                                        currentFind.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
                                    }
                                }
                                finally
                                {
                                    Marshal.ReleaseComObject(currentFind);
                                    Marshal.ReleaseComObject(firstFind);
                                    Marshal.ReleaseComObject(targetRange);
                                }

                            }
                            finally { Marshal.ReleaseComObject(ws); }
                        }
                        finally { Marshal.ReleaseComObject(wss); }

                        wb.SaveAs(@"D:\COM_TEST_2.xlsx");
                    }
                    finally
                    {
                        if (wb != null)
                        {
                            wb.Close(false);
                        }
                        Marshal.ReleaseComObject(wb);
                    }
                }
                finally { Marshal.ReleaseComObject(wbs); }
            }
            finally
            {
                if (app != null)
                {
                    app.Quit();
                }
                Marshal.ReleaseComObject(app);
            }
        }

    }
}

開いているBOOK

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// 参照設定
// Microsoft Excel 16.0 Object Library

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

using Microsoft.VisualBasic;
using System.Security;


namespace Excel_COM_2.ExcelCOM_01
{
    class clS開いているExcel処理
    {
        public static object CreateObject(string progId, string serverName)
        {
            Type t;
            if (serverName == null || serverName.Length == 0)
                t = Type.GetTypeFromProgID(progId);
            else
                t = Type.GetTypeFromProgID(progId, serverName, true);
            return Activator.CreateInstance(t);
        }

        public static void mth_Main()
        {
            //var app = new SecurityCritical.GetObject(, "Excel.Application");
            //var app = new Microsoft.Office.Interop.Excel.Application();
            var app = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
            //var app = new CreateObject(, "Excel.Application");

            try
            {
                Excel.Workbooks wbs = app.Workbooks;
                try
                {
                    string sBkName = "";
                    foreach ( Excel.Workbook item_wb in wbs)
                    {
                        Console.WriteLine(item_wb.Name);
                        sBkName =item_wb.Name;
                    }
                    Excel.Workbook wb = wbs[sBkName];


                    //string sFile_fp = @"D:\COM_TEST.xlsx";
                    //Excel.Workbook wb = wbs.Open(sFile_fp);
                    try
                    {
                        Excel.Sheets wss = wb.Sheets;
                        try
                        {
                            //Worksheet ws = wss[1];
                            Excel.Worksheet ws = wss["シート(No2)"];
                            //Excel.Worksheet ws = wss[1];
                            try
                            {
                                //Console.WriteLine(ws.Cells[1, 1].value.tostring());
                                
                                //================================
                                // 最終行/最終列
                                //================================
                                int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row;
                                int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row;
                                ws.Cells[1, 1].value = 5555;

                                //===============================================
                                // 使用範囲を一括で二次元配列にコピー
                                //===============================================
                                Excel.Range targetRange = null;
                                Excel.Range firstFind = null;
                                Excel.Range currentFind = null;

                                try
                                {
                                    //targetRange = ws.Columns[colName]; //シート内での検索範囲
                                    //targetRange = ws.Columns[1]; //シート内での検索範囲
                                    targetRange = ws.Cells; //シート内での検索範囲

                                    currentFind = targetRange.Find(
                                                                    "s", //検索キー 
                                                                    Type.Missing,
                                                                    Excel.XlFindLookIn.xlValues,
                                                                    Excel.XlLookAt.xlPart,
                                                                    Excel.XlSearchOrder.xlByRows,
                                                                    Excel.XlSearchDirection.xlNext,
                                                                    false,
                                                                    Type.Missing,
                                                                    Type.Missing
                                                                    );

                                    while (currentFind != null)
                                    {
                                        if (firstFind == null)
                                        {
                                            firstFind = currentFind;
                                        }
                                        else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
                                        {
                                            break;
                                        }
                                        currentFind = targetRange.FindNext(currentFind);
                                        Console.WriteLine(currentFind.Address);
                                        currentFind.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
                                    }
                                }
                                finally
                                {
                                    if (currentFind != null) Marshal.ReleaseComObject(currentFind);
                                    if (firstFind != null) Marshal.ReleaseComObject(firstFind);
                                    Marshal.ReleaseComObject(targetRange);
                                }

                            }
                            finally { Marshal.ReleaseComObject(ws); }
                        }
                        finally { Marshal.ReleaseComObject(wss); }

                        //wb.SaveAs(@"D:\COM_TEST_2.xlsx");
                    }
                    finally
                    {
                        //if (wb != null)
                        //{
                        //    wb.Close(false);
                        //}
                        Marshal.ReleaseComObject(wb);
                    }
                }
                finally { Marshal.ReleaseComObject(wbs); }
            }
            finally
            {
                //if (app != null)
                //{
                //    app.Quit();
                //}
                Marshal.ReleaseComObject(app);
            }
        }

    }
}

Excelのバックグラウンドプロセスを直接殺す

【Interop.Excel】Excelプロセス絶対殺すコード

foreach (var p in Process.GetProcessesByName("EXCEL"))
{
  if (p.MainWindowTitle == "")
  {
   p.Kill();
  }
}

C#:Excel

確認中

確認中

C#:Excel(COM)

参考

参考

Excelファイルを C# と VB.NET で読み込む "正しい" 方法

【Interop.Excel】Excelプロセス絶対殺すコード

【開いているBOOK確認】

VB.NET 開いているExcelBookとSheetをチェック - Take it easy!

Imports Excel = Microsoft.Office.Interop.Excel

''' <summary>
''' 
''' </summary>
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Debug.Print(fExcelOpen("BOOK1"))

    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="ExFileName"></param>
    ''' <returns></returns>
    Private Function fExcelOpen(ByVal ExFileName As String) As Boolean
        Dim oExcel As Excel.Application
        Dim oBooks As Excel.Workbooks
        Dim oBook As Excel.Workbook
        Dim fsts As Boolean =FalseDim oSheets As Excel.Sheets
        Dim oSheet As Excel.Worksheet
        Try
            '別プロセスのExcelを取得する
            'GetObjext第1引数のファイルパスは省略する
            oExcel = GetObject(, “Excel.Application”)
            '開いているブックを全て取得する
            oBooks = oExcel.Workbooks

            'ブック毎に確認
            For Each oBook In oBooks
                Debug.Print(oBook.Name)
                If oBook.Name = ExFileName Then
                    fsts = True
                End If
            Next
            'For Each oBook In oBooks
            '    Debug.Print(oBook.Name)
            'Next

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            'COMコンポーネントの解放
            If Not oSheet Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
                oSheet = Nothing
            End If
            If Not oSheets Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheets)
                oSheets = Nothing
            End If
            'COMコンポーネントの解放
            If Not oBook Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
                oBook = Nothing
            End If
            If Not oBooks Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
                oBooks = Nothing
            End If
            If Not oExcel Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
                oExcel = Nothing
            End If
        End Try

        Return fsts

    End Function

End Class

【C#】サンプル

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// 参照設定
// Microsoft Excel 16.0 Object Library

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
/// <summary>
/// 
/// </summary>
namespace Excel_COM_2.ExcelCOM_01
{
    /// <summary>
    /// 
    /// </summary>
    class clsExcelCOM_01
    {
        /// <summary>
        /// 
        /// </summary>
        public static void mth_Main()
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Excel.Workbooks wbs = app.Workbooks;
                try
                {
                    string sFile_fp = @"D:\COM_TEST.xlsx";
                    Excel.Workbook wb = wbs.Open(sFile_fp);
                    try
                    {
                        Excel.Sheets wss = wb.Sheets;
                        try
                        {
                            //Worksheet ws = wss[1];
                            Excel.Worksheet ws = wss["シート(No2)"];
                            try
                            {
                                //================================
                                // 最終行/最終列
                                //================================
                                int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row;
                                int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row;

                                //===============================================
                                // 使用範囲を一括で二次元配列にコピー
                                //===============================================
                                Object[,] rangeArray;
                                Excel.Range rng = ws.UsedRange;
                                try
                                {
                                    rangeArray = rng.Value;
                                }
                                finally { Marshal.ReleaseComObject(rng); }

                                


                                // 二次元配列に対してループを回す
                                int lastRow = rangeArray.GetLength(0);
                                int lastCol = rangeArray.GetLength(1);
                                for (int r = 1; r <= lastRow; r++)
                                {
                                    //Console.WriteLine(r);
                                    for (int c = 1; c <= lastCol; c++)
                                    {
                                        Console.WriteLine(rangeArray[r, c]);
                                    }
                                }
                            }
                            finally { Marshal.ReleaseComObject(ws); }
                        }
                        finally { Marshal.ReleaseComObject(wss); }
                    }
                    finally
                    {
                        if (wb != null)
                        {
                            wb.Close(false);
                        }
                        Marshal.ReleaseComObject(wb);
                    }
                }
                finally { Marshal.ReleaseComObject(wbs); }
            }
            finally
            {
                if (app != null)
                {
                    app.Quit();
                }
                Marshal.ReleaseComObject(app);
            }
        }


    }
}

Excel :関数:日付/時刻関数

日付/時刻関数

年月日の取得

時分秒の取得

曜日の取得

日付から曜日の数値を取り出す:WEEKDAY

    '今日の曜日
    Debug.Print Application.WorksheetFunction.Text(Now, "aaaa")
土曜日
    
    '戻り値は1~7(日~土)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 1)
 7 
    '戻り値は1~7(月~日)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 2)
 6 
    '戻り値は0~6(月~日)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 3)
 5 
    '戻り値は1~7(月~日)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 11)
 6 
    '戻り値は1~7(火~月)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 12)
 5 
    '戻り値は1~7(水~火)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 13)
 4 
    '戻り値は1~7(木~水)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 14)
 3 
    '戻り値は1~7(金~木)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 15)
 2 
    '戻り値は1~7(土~金)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 16)
 1 
    '戻り値は1~7(日~土)
    Debug.Print Application.WorksheetFunction.WEEKDAY(Now, 17)
 7 

日付から曜日を表示:TEXT関数

  • 日付から曜日を表示(日本語_通常):TEXT(日付, "aaaa")
  • 日付から曜日を表示(日本語_短縮):TEXT(日付, "aaa")
  • 日付から曜日を表示(英語_通常):TEXT(日付, "dddd")
  • 日付から曜日を表示(英語_短縮):TEXT(日付, "ddd")
    '曜日
    Debug.Print Application.WorksheetFunction.Text(Now, "aaaa")
土曜日

    '曜日_短縮
    Debug.Print Application.WorksheetFunction.Text(Now, "aaa") '曜日_英語
    Debug.Print Application.WorksheetFunction.Text(Now, "dddd")
Saturday

    '曜日_英語_短縮
    Debug.Print Application.WorksheetFunction.Text(Now, "ddd")
Sat


週番号の取得

日付の変換

日付のシリアル値

時刻のシリアル値

期日

期間

VBA:Excel :ACCESS DB

データベースを作成する(ADOX)

データベースを作成する(ADOX)

テーブルの作成・削除/フィールドの作成・削除(ADOX)

テーブルの作成・削除/フィールドの作成・削除(ADOX)

Sub Sample_ADOX_CreateTable1()
 
    '参照設定:Microsoft ADO Ext.6.0 for DDL and Security
    
    '-------------------------------------------------------------------------
    Dim prm_DataSrc As String: prm_DataSrc = "D:" & "\TestDB.accdb"
    Dim prm_TbName  As String: prm_TbName = "TB_Test"
    
    
    Dim dic_Fld As Dictionary
    Set dic_Fld = New Dictionary
        
    'フィールド(列)の設定
    dic_Fld.Add "登録ID", adInteger
    dic_Fld.Add "氏名", adVarWChar
    dic_Fld.Add "生年月日", adDate
    dic_Fld.Add "備考", adLongVarWChar
    
    '-------------------------------------------------------------------------
    
    
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim ConStr As String
    Dim DBFile As String
    
    On Error GoTo ErrHandler
    
    'データベースのパスと名前
    
    'Access 2007以降(accdb ファイル)
    DBFile = prm_DataSrc
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    'Access 2003以前(mdb ファイル)
    'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile
    
    'データベース接続
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = ConStr
    
    'テーブルの作成
    Set tbl = New ADOX.Table
    tbl.Name = prm_TbName
    Set tbl.ParentCatalog = cat
    
    'フィールド(列)の設定
    Dim v
    For Each v In dic_Fld
        tbl.Columns.Append v, dic_Fld(v)
    Next v
'    tbl.Columns.Append "登録ID", adInteger
'    tbl.Columns.Append "氏名", adVarWChar
'    tbl.Columns.Append "生年月日", adDate
'    tbl.Columns.Append "備考", adLongVarWChar
    
    'テータベースへ登録
    cat.Tables.Append tbl
    
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    
    Set cat = Nothing
    Set tbl = Nothing
 
End Sub