2018-05-06

MS AccessのインストールされていないPCで、VBA（Excel）を使って accdbを最適化する

最近VBAネタが続く・・・

今使っているOffice 2013を買う時、Accessはあれば便利かもしれないけど、
「どうせ使う機会は無いだろう」という事で、Office Personalを買ったので
Accessは持っていない。

しかし、最近になって〇〇なデータ管理をしようかと思ったが、どうにもExcelだけでは厳しい。
ローカルで手軽に使えるデータベースが欲しいけれど、このためだけにAccessを買うというのももったいない。
（LibreOffice ~~Calc~~ Baseという選択肢はない。）
バックエンド用として使えれば、フロントエンドはC#とか最悪Excelでもなんとか出来る。

あれ、mdbってVBAで作れなかった？
やっぱり作れるじゃないか、accdbも！

で、このゴールデンウィークに、accdbファイル自体と、
それに、テーブルを作るためのツールを作った。
肝心のデータ管理用のフロントエンドは全く出来ていないし
データもインサートしてないのに、気が早くて「最適化ツールは絶対必要だ！！！」
となり、作り始めたはいいが、コンパイルエラー
"DBEngine"を、「変数が定義されていません」とおっしゃる。
f:id:Z1000S:20180506161438j:plain

DAOの参照設定してないじゃん。
Microsoft DAO 3.6 Object Library 参照設定して、コンパイルもOK。
いざ、実行！

実行時エラー 3343
データベースの形式 'C:\Datas\sample.accdb' を認識できません。

調べていくと

引数 options に次のいずれかの定数を使用すると、最適化されたデータベースのデータの形式のバージョンを指定できます。
・・・
dbVersion120
最適化に Microsoft Access データベースエンジンバージョン 12.0 のファイル形式を使用するデータベースを作成します。
DBEngine.CompactDatabase method (DAO) | Microsoft Docs

オプションに、dbVersion120を追記して、コンパイルしてみる。
"dbVersion120"に対して、"変数が定義されていません。"
f:id:Z1000S:20180506163124j:plain
まだ、だめですか・・・orz

さらに調べていくと、

DAO 12.0 (ACEDAO.DLL) の DBEngine.CompactDatabase メソッドを利用する。
VBレスキュー(花ちゃん) の Visual Basic 2010 用掲示板(VB.NET 掲示板)

という記載を発見。

でも、参照設定に、DAO 12.0なんて見当たらない。
そもそも、ACEDAO.DLLなんて入ってるの？
f:id:Z1000S:20180506163729j:plain
DLLはあった。
じゃあ、どれを参照すればいいの？？？

オブジェクトブラウザーで「DAO」を確認すると「Microsoft office 15.0 Access database engine Object」に含まれているんですね。
ふひろ通信: Microsoft Access 2013の参照設定で「Microsoft DAO 3.6 Object Library」を有効にしようとすると警告が出る

これ（Microsoft Office 15.0 Access database engine Object Library）か？
f:id:Z1000S:20180506165710j:plain
参照設定チェックしてみると
f:id:Z1000S:20180506170103j:plain
オブジェクトブラウザに、dbVersion120の文字が表示された！
コンパイルもOK。
いざ、実行。
エラーの発生もなく、無事終了。

ダミーのデータ10万件をinsert後、全てdeleteしてから
最適化処理でファイルサイズが小さくなっているのを確認。
（拡張子".tmp"となっているのが最適化実行直後（リネーム前））
f:id:Z1000S:20180508074139j:plain

最終的なコードは、これ

'参照設定
'Microsoft Office 15.0 Access database engine Object Library

Public Sub compactDB()

    Dim sSrcPath    As String
    Dim sDestPath   As String

    '最適化するファイルのパス
    sSrcPath = "C:\Datas\sample.accdb"

    '最適化後のファイル名
    sDestPath = sSrcPath & ".tmp"

    On Error GoTo ERR_COMPACT_DB

    '最適化の実行
    DBEngine.CompactDatabase sSrcPath, sDestPath, DAO.LanguageConstants.dbLangJapanese, DAO.DatabaseTypeEnum.dbVersion120
    '冗長に書いているが、下記でもOK
    'DBEngine.CompactDatabase sSrcPath, sDestPath, dbLangJapanese, dbVersion120

    '最適化前のファイルを削除
    Kill sSrcPath

    '最適化後のファイル名を最適化前のファイル名にリネーム
    Name sDestPath As sSrcPath

ERR_COMPACT_DB:
    If Err.Number <> 0 Then
        Debug.Print "[" & CStr(Err.Number) & "]" & Err.Description
    Else
        Debug.Print "Done."
    End If

End Sub

自分一人で使うやつなので、エラー処理は最低限。
あと、参照設定の15.0の部分は、Officeのバージョンで変わるらしい。

mdbの場合は、dbVersion120の代わりに、dbVersion40を使えばいけるのかな？（未確認）

参考サイト
DBEngine.CompactDatabase method (DAO) | Microsoft Docs
DatabaseTypeEnum enumeration (DAO) | Microsoft Docs

2018-04-27

VBAのDictionaryのItemに動的配列を格納する

VBA Dictionary Redim ReDim Preserve 配列連想配列

C++のmultimapのように、特定のキーに対し、複数のデータをDictionaryに格納できないか？
キー毎にデータ数が異なる場合、最大データ数を求めてから固定長配列を格納するのは無駄だし、
実際にデータがいくつ入っているか先頭から当たっていかないとわからなそう。
それは、やりたくない。

で、動的配列をItemとして格納してみたら、案外あっさりと出来ました。

ワークシートから読み込んだ地名の先頭１文字をキーとして、その地名を配列にしてItemに格納する例です。

Public Sub dicTest()

    Dim dicAddress  As New Dictionary
    Dim sAddress()  As String
    Dim sKey        As String
    Dim lItems      As Long
    Dim sValue      As String
    Dim lRow        As Long
    Dim i           As Long

    lRow = 1

    lItems = 0
    ReDim sAddress(lItems)

    With ThisWorkbook.Worksheets("Sheet1")
        sValue = .Cells(lRow, 1).Value

        Do Until (Len(sValue) = 0)

            sKey = Left$(sValue, 1)

            If dicAddress.Exists(sKey) = True Then
                'キーありなら、格納されている配列を取得
                sAddress = dicAddress.Item(sKey)

                lItems = UBound(sAddress) + 1

                '配列の要素数をひとつ増やす
                ReDim Preserve sAddress(lItems)
            Else
                'キーなしなら、１個分のデータを格納できるよう、配列を初期化
                lItems = 0

                ReDim sAddress(lItems)
            End If

            sAddress(lItems) = sValue

            '配列を格納
            dicAddress(sKey) = sAddress

            lRow = lRow + 1

            sValue = .Cells(lRow, 1).Value

            'Dictionaryには、コピーされた配列が格納される（ようです）。
            '消さなくても試した限りでは問題はなさそうだけど、残して何度も使い回すのは気持ち悪いので消す。
            Erase sAddress
        Loop
    End With

    '格納したデータを書き出す
    Debug.Print "----- い -----"
    For i = 0 To UBound(dicAddress.Item("い"))
        Debug.Print dicAddress.Item("い")(i)
    Next i

    Debug.Print "----- お -----"
    For i = 0 To UBound(dicAddress.Item("お"))
        Debug.Print dicAddress.Item("お")(i)
    Next i

End Sub

テストデータは
---------------------------------
あきる野市
 あわら市
 あま市
 あさぎり町
おいらせ町
いわき市
つくばみらい市
 さいたま市
いすみ市
おおい町
いなべ市
いの町
いちき串木野市
---------------------------------

実行結果は、
----- い -----
いわき市
 いすみ市
 いなべ市
いの町
いちき串木野市
----- お -----
おいらせ町
おおい町

こんな感じ。

ポイントは、

１．キーがあった時の、データ（動的配列）の取り出し方。
２．データを追するのためのReDim PreserveとReDimの使い方。
３．データ読み出し時の指定方法dicAddress.Item("い")(i)
あたりでしょうか。

まあ、ちょっとしたネタなので、「もっといい方法がある」とか突っ込まないで下さい。

こんなのもあります。

z1000s.hatenablog.com

2018-04-15

ExcelのVBAで使えるDLLを、C++（Visual Studio 2017）で作る。・・・その１

Excel VBA C++ Visual Studio DLL

ExcelのVBAでもそこそこの事は出来るけれど、C++で処理したい事があって「DLLにしちゃえ」と思ったはいいが、いろいろと忘れて（いや、覚えてないから忘れられないだろうorz）作るのが大変だったので、まとめてみることにした。

とりあえず、今回は、何もしない、何も返さない関数を含むDLLをビルドして、VBAから呼び出して、エラーが出ない事を確認するまで。

何らかの値を渡して、何らかの処理をした結果を返してもらうのは、次回以降に・・・

開発環境
DLLの作り方（基礎編）
使い方（Excelからの呼び出し方）

開発環境

OS：Windows 10 Professional
DLL：Microsoft Visual Studio Community 2017（Microsoft Visual C++ 2017）
VBA：Microsoft Office Personal 2013（Microsoft Excel 2013）

DLLの作り方（基礎編）

プロジェクトの作成

１．Visual Studioを起動
２．「ファイル」-「新規作成」-「プロジェクト」をクリック

f:id:Z1000S:20180415145858j:plain

３．「Visual C++」-「Windows デスクトップ」-「ダイナミックリンクライブラリ (DLL)」をクリック

f:id:Z1000S:20180415145927j:plain

４．「名前」「場所」「ソリューション名」を指定し、「OK」をクリック
「ソリューションのディレクトリを作成」にチェックが入っていないと、ソリューション名は指定できないので、作成する場合はチェックする。
ソリューションのディレクトリを作成するかどうかは任意。今回は作成した状態で説明しています。作成しない場合は、適宜読み替えて下さい。

サイトに寄っては、Visual Studioを「管理者として実行しないとDLLが作れない」と書かれている所もあるようですが、私の場合は通常の起動でも作成出来ました。

f:id:Z1000S:20180415150056j:plain

モジュール定義ファイル（defファイル）

VBAからアクセスしやすいように、モジュール定義ファイルを追加します。
１．「プロジェクト」-「新しい項目の追加」をクリック。

f:id:Z1000S:20180415150120j:plain

２．「インストール済み」-「Visual C++」-「コード」-「モジュール定義ファイル (.def)」をクリック

f:id:Z1000S:20180415150143j:plain

３．名前をプロジェクト名.def（AccessibleFromVBA.def）として、「追加」ボタンをクリック

f:id:Z1000S:20180415150201j:plain

f:id:Z1000S:20180415150232j:plain

プロジェクトの設定

プロジェクトのプロパティを変更します。

１．「プロジェクト」-「プロパティ」をクリック

f:id:Z1000S:20180415150254j:plain

２．構成を"アクティブ(Debug)"から"すべての構成"に変更します

３．「構成プロパティ」-「全般」-「プロジェクトの既定値」-「文字セット」を"マルチバイト文字セット" "Unicode 文字セットを使用する"に変更します。（2019/9/25 訂正）

f:id:Z1000S:20190925144310j:plain

４．「構成プロパティ」-「全般」-「全般」-「出力ディレクトリ」を"$(SolutionDir)$(Configuration)\"から"$(ProjectDir)$(Configuration)\"に変更します。
（この操作は必須ではありません。）

f:id:Z1000S:20180415164739j:plain

５．「構成プロパティ」-「リンカー」-「モジュール定義ファイル」の値が、先程作成したモジュール定義ファイル名（AccessibleFromVBA.def）になっているか確認します。

f:id:Z1000S:20180415164817j:plain

６．「適用」ボタンをクリック

プリコンパイル済みヘッダーは、使わなくてもいいと思ったが、とりあえずそのまま使用することに。

関数を追加してみる

まずは、何もしない関数を追加してみる。

ヘッダーファイル

デフォルトでヘッダーファイルが無いので、追加する。
１．「プロジェクト」-「新しい項目の追加」をクリック。

f:id:Z1000S:20180415165036j:plain

２．「インストール済み」-「Visual C++」-「コード」-「ヘッダーファイル (.h)」をクリック

３．名前をプロジェクト名.h（AccessibleFromVBA.h）として、「追加」ボタンをクリック

f:id:Z1000S:20180415165051j:plain

４．まずは、おまじない。

#pragma once

extern "C" {
#define ACCESSIBLEFROMVBA_API __declspec(dllexport)
}

#pragma onceについてはこちらを参照。

extern "C"については、ロベールのＣ＋＋教室 - 第４７章Ｃ±± -あたりを見るか、自分でググって下さい。

"#define ACCESSIBLEFROMVBA_API __declspec(dllexport)"については、以下を参照。

DLL のビルド時には通常、エクスポートする関数のプロトタイプやクラスを含むヘッダーファイルを作成し、そのヘッダーファイル内の宣言に __declspec(dllexport) を追加します。コードを読みやすくするために、次のように __declspec(dllexport) 用のマクロを定義して、そのマクロをエクスポートする各シンボルに使います。
__declspec(dllexport) を使った DLL からのエクスポート

#define・・・は、externの{}の外の方がいいかもしれないけど、今回は、C++ & VBA Onlyなのでこれでよしとする。

５．extern "C"の後の{}内に関数を宣言する。
今回は、引数を取らず、何も返さない"DoNothing"という関数を追加してみる。

-----　重　要　-----
戻り値の型と関数名の間にWINAPIを忘れないように。

#pragma once

extern "C" {
#define ACCESSIBLEFROMVBA_API __declspec(dllexport)
    ACCESSIBLEFROMVBA_API void WINAPI DoNothing();
}

f:id:Z1000S:20180415165729j:plain

赤い波線が出て来るが気にしない。

ソースファイル

関数の宣言が出来たら、次は定義の作成。
１．ソリューションエクスプローラーで、AccessibleFromVBA.cppをクリック（or ダブルクリック）
２．前項で作ったヘッダーファイルのインクルードを確認。無ければ追記。

f:id:Z1000S:20180415170741j:plain

３．宣言に合わせて関数を作成。今回は何もしないので、関数の中身は"return;"のみ

#include "stdafx.h"
#include "AccessibleFromVBA.h"

ACCESSIBLEFROMVBA_API void WINAPI DoNothing()
{
    return;
}

f:id:Z1000S:20180415170804j:plain

モジュール定義ファイル

１．ソリューションエクスプローラーで、AccessibleFromVBA.defをクリック（or ダブルクリック）

f:id:Z1000S:20180415170825j:plain

２．"LIBRARY"の後ろに、DLL名を追記。
３．"EXPORTS"と追記。
４．エクスポートする関数名を追記。

f:id:Z1000S:20180415170901j:plain

以前は、関数名の後ろに"@"と数字を付けていたけど、特定の場合を除き付けることはお勧めではないようです。

@ordinal を使用して、関数名ではなく番号が DLL のエクスポートテーブルに格納されるように指定できます。多くの Windows DLL で、レガシコードをサポートするために序数がエクスポートされます。 DLL のサイズを最小限に抑えるのに役立つため、16 ビットの Windows コードでは序数を使用することが一般的でした。レガシサポートのために DLL のクライアントで必要な場合を除き、関数を序数でエクスポートすることはお勧めしません。 .LIB ファイルには序数と関数のマッピングが含まれているため、DLL を使用するプロジェクトでは通常と同様に関数名を使用できます。
EXPORTS

ビルド

１．「ビルド」-「AccessibleFromVba のビルド」をクリック

f:id:Z1000S:20180415171016j:plain

２．出力ウィンドウに、"ビルド: 1 正常終了、0 失敗、0 更新不要、0 スキップ"と表示されればOK

f:id:Z1000S:20180415171039j:plain

出力先のフォルダに、"AccessibleFromVba.dll"が出来ているはず。

使い方

Excel

Declare ステートメント

１．構文
[ Public|Private ] Declare Sub name Lib "libname" [ Alias "aliasname" ] [ ( [ arglist ] ) ]
[ Public|Private ] Declare Function name Lib "libname" [ Alias "aliasname" ] [ ( [ arglist ] ) ] [ As type ]

指定項目	説明	備考
name	関数名	前述の例では、DoNothing
libname	DLL名	必要であればパスも含めて指定
aliasname	DLL またはコードリソース内のプロシージャの名前	多分気にしなくてもOK
arglist	プロシージャが呼び出されるときにプロシージャに渡される引数を表す変数のリスト	オプション
type	Function プロシージャによって返される値のデータ型	オプション。VBAの型を指定

詳細はこちらとかこちら。

DoNothingの例

標準モジュールに、以下の１行を追記。（パスは自分の環境に合わせて下さい。）

Private Declare Sub DoNothing Lib "C:\Datas\MyDatas\Developer\VisualStudioComunity2017\DllForVBA\AccessibleFromVBA\Debug\AccessibleFromVBA.dll" ()

以下のように呼び出す。

Public Sub DllCallTest()

    'DLLの関数呼び出し
    Call DoNothing

    Debug.Print "Done."

End Sub

f:id:Z1000S:20180415201403j:plain

エラーの発生もなく、無事終了。

次回予告

今回の内容では、投げっぱなしの処理しか出来ないので、次回はデータの受け渡しに関連する事をまとめる予定。

受け渡しするデータの型について
値渡しと参照渡しについて
処理する値を渡せるようにすること
処理した結果や値を返してもらえるようにすること
データ型による渡し方の注意点

それ以降は、

DLLからエクスポートされる関数の確認方法
DLLのデバッグ方法

などをまとめる予定。

いつ頃までに、まとめられるかは、不明・・・
気分次第？

2018/6/27

やっと、その２を追加しました。
予告までしたのに、遅いし、予告した内容も一部次に先送りしてるし・・・orz

z1000s.hatenablog.com

2019/10/22

その３文字列の受け渡しを追加しました。

z1000s.hatenablog.com

2018-04-11

Excelの列のアルファベット-数値変換

Excel VBA

ThisWorkbook.Worksheets(1).Columns("AA").Column
===>27

ThisWorkbook.Worksheets(1).Columns(28).Address(ColumnAbsolute:=False)
===>AB:AB
ThisWorkbook.Worksheets(1).Cells(1, 28).Address(RowAbsolute:=False, ColumnAbsolute:=False)
===>AB1

数値→アルファベットへの変換の場合、余計な部分を削除する必要がある。

2018-02-17

ManjaroLinux 筑波ミラーが復活したようです

Manjaro Linux pacman コマンド

2/11のブログにpacmanのミラーのうち、筑波がエラーが発生すると書きましたが、今日pacman-mirrorsが更新された後、レポジトリとの同期をすると筑波のミラーが復活していました。

f:id:Z1000S:20180217202750p:plain

筑波のミラーは応答は早いのですが、時々不安定な感じがするのは私だけでしょうか？
安定性では理研の方が上のような気がします。
でも面倒なので、そのまま筑波を使ってますが・・・

2018-02-11

Manjaro Linux で pacmanが接続拒否エラーを返す

Linux Manjaro Linux pacman

２、３日前位からでしょうか、pacman -Syuを実行すると、エラーが発生します。筑波に繋がらないようです。

エラー: ファイル 'core.db' を ftp.tsukuba.wide.ad.jp から取得するのに失敗しました : Failed to connect to ftp.tsukuba.wide.ad.jp port 80: 接続を拒否されました

とりあえずミラーを更新してみると、やはり筑波にエラーが発生しましたが、理研に切り替わって、結果的には大丈夫そうです。

f:id:Z1000S:20180211202734p:plain

2018-02-07

scpコマンドで秘密鍵を使ってファイルをコピーする

Linux コマンド

普段使用しているPCから、他のPCにファイルをコピーしようとする場合、scpコマンドを使用しますが、コピー先のPCと使用中のPCのユーザー名が異なる場合、明示的に接続先のユーザー名の指定が必要となる場合の指定方法です。

scp -i 秘密鍵ファイルコピーするファイル名コピー先のユーザー名@コピー先のアドレス:コピー先のファイルを保存するディレクトリ

空腹おやじのログと備忘録

VBA（主にExcel）でいろいろな実験的な事とか、Linuxのコマンドとか設定とかについて忘れないように、あれこれと・・・

MS AccessのインストールされていないPCで、VBA（Excel）を使って accdbを最適化する

VBAのDictionaryのItemに動的配列を格納する

ExcelのVBAで使えるDLLを、C++（Visual Studio 2017）で作る。・・・その１

開発環境

DLLの作り方（基礎編）

プロジェクトの作成

モジュール定義ファイル（defファイル）

プロジェクトの設定

関数を追加してみる

ヘッダーファイル

ソースファイル

モジュール定義ファイル

ビルド

使い方

Excel

Declare ステートメント

DoNothingの例

次回予告

Excelの列のアルファベット-数値変換

ManjaroLinux 筑波ミラーが復活したようです

Manjaro Linux で pacmanが接続拒否エラーを返す

scpコマンドで秘密鍵を使ってファイルをコピーする