EXCELとACCESSを使った運用 Part.1
EXCELとACCESSを使って、出来ること。
EXCELからACCESSにデータを追加してみようかな？
日々データー量が増加して、EXCELが重くなるなんてありませんか？
100万件以内ならそれでも…と思いますが、それを超えだすと厳しくなってきます。
Excelは1シートに100万件までです。※2003以前はもっと少ないです。
解決策はいろいろあると思うのですが、今回、Accessにデータを溜めておき、必要なときに抽出して使う方法を簡単に説明したいと思います。
細かいところは、ググれば先人たちの説明がネットにあふれていますのでそちらを…（＾＾）
イメージですが、
・ACCESSにデーターベースデザインを作成する。
・EXCELからデータを追加する。（VBAでACCESSに追加）
・EXCELから必要分のデータを抽出する。（EXCELから、ACCESSのデータを抽出する（日付等）
・細かい編集はEXCELのマクロ等で実行して編集。
※ACCESSにフォームやマクロを作成するという選択肢もありますが、ハードルが高いので、今回はパスします。
VBA等はACCESS側へは作らない。但し、簡単なクエリ（※Accessの機能）は作ってみても良いかもしれません。
最初に、取り込みたいデータをEXCELに作成します。（CSV等でしたら、外部データの取込みでEXCELのシートに取込む）
######
EXCELからACCESSにインポートする際、データ前半を見て自動的に各項目の形式を決定します。
本来と違う形式でテーブルが作成された場合は、後で修正が必要です。
※詳しい方は、手で作成すれば、この工程は必要ないです。
ACCESS側から。（今回Office2016で作成しています。）
空のACCESSを開きます。
外部データからEXCEL取込みを選択します。
以下手順で取込むと、取込んだEXCELのシート名でテーブルができます（ここではDATA）
ファイル名に作ったEXCELを指定します。
主キーは設定しない。（設定すればスピード上げたりできるのですが今回はしない方向で…）
こんなテーブルが作成されます。
デザインは、こんな感じですね。
ＡＣＣＥＳＳを適当な名前で保存します。
ここまでどうですか？
まずは入れる枠を作成しました。取り込んだデータは必要無ければ消してください。
インデックスは設定していません。設定すると早くなりますが、データー量に影響を及ぼすので今回はなしです。
EXCEL側へ行きます。
ＥＸＣＥＬのＶＢＡとＡＣＣＥＳＳが連携できるように設定をします。
参照設定のウィンドウの
Microsoft ActiveX Data Objects 2.X Libraryにチェックを入れるだけです。
一番大きい数字で大丈夫だと思いますが、だめだったら一個づつ下げてください。
※PC内で参照するならこれで問題ないですが、いろいろなOfficeバージョンが存在する場合は別のやり方で参照したほうが良いでしょう。
↓ 識者の説明
https://ateitexe.com/vba-ado-not-reference/
※簡単に言うと、まるっと参照します宣言か、VBAに毎回参照します宣言をするかの違いです。
変数宣言しときましょ。
変数宣言しておくと、存在しない変数を指定するとコンパイルでエラーになってくれます。
※https://www.bookservice.jp/2018/01/24/excel_macro001/
今回は、モジュールレベルの変数を書いています。プログラムが終るまで有効です。
以下内容（変数は未記載です。）
'Dim myCon As New ADODB.Connection
'Dim myRecordSet As New ADODB.Recordset
'Dim mySQL As String
'Dim dbFile As Variant
'Dim mySheetName As Variant
'Dim vFILENAME As Variant ' OPENするファイル名(フルパス) accdb
'### 上記モジュール変数にCOPY
Dim myCon As New ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String
Dim dbFile As Variant
Dim mySheetName As Variant
Dim vFILENAME As Variant ' OPENするファイル名(フルパス) accdb
MSG_FLG = MsgBox("accessDBへ追加実行OK？", vbYesNo)
If MSG_FLG = vbNo Then
Exit Sub
End If
'アクティブなシート名を取得
mySheetName = "DATA"
Const cnsTITLE = "○access抽出（*.accdb）接続処理"
Const cnsFILTER = "～から (*.accdb),*.accdb"
'### 基本フォルダ指定（シートKANRI）（ACCESSの入ってる場所）
With CreateObject("WScript.Shell")
.CurrentDirectory = Worksheets("KANRI").Range("B1")
End With
vFILENAME = Application.GetOpenFilename(FileFilter:=cnsFILTER, Title:=cnsTITLE)
If VarType(vFILENAME) = vbBoolean Then
MsgBox ("キャンセル")
Exit Sub
End If
'### AccessDB open 準備 ###
dbFile = vFILENAME
myCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
myCon.Open
' MsgBox ("【確認】" & vbCrLf & myCon)
'※※※※※※オートフィルター解除 ※※※※※※
If Sheets(mySheetName).AutoFilterMode Then
Sheets(mySheetName).Range("A1").AutoFilter
End If
'### open ###
myRecordSet.Open mySheetName, myCon, adOpenKeyset, adOpenDynamic
CNT01 = 0
Set set_data01 = Worksheets(mySheetName).Range("A2")
Do Until set_data01.Offset(0, 0).Value = ""
myRecordSet.AddNew
myRecordSet![顧客コード] = set_data01.Offset(0, 0).Value '### []
myRecordSet![顧客名] = set_data01.Offset(0, 1).Value
myRecordSet![出荷日] = set_data01.Offset(0, 2).Value
myRecordSet![商品コード] = set_data01.Offset(0, 3).Value
myRecordSet![商品名] = set_data01.Offset(0, 4).Value
myRecordSet![金額] = set_data01.Offset(0, 5).Value
myRecordSet![冊数] = set_data01.Offset(0, 6).Value
'### データーがスペースの可能性がある場合は以下のようにしてください
' If set_data01.Offset(0, 7).Value = "" Then
' myRecordSet![商品コード] = Null
' Else
' myRecordSet![商品コード] = set_data01.Offset(0, 3).Value
' End If
'
myRecordSet.Update '### 書き込み
Set set_data01 = set_data01.Offset(1, 0) '次行
CNT01 = CNT01 + 1
Loop
'### オブジェクトのクローズ処理
myRecordSet.Close
'トランザクション終了
Set myRecordSet = Nothing
MsgBox (CNT01 & "件取込。※EXCEL(DATA)→ACCESSDB END")
/
/
/
/
/
/
以下上記サンプルです。
※セキュリティの関係でACCESSのファイルはそのままUPできないので、EXCELとACCESS一緒に圧縮してUPしてあります。
D:SAMPLE に置いてください。（別の場所に置くことも可能です。※KANRIシートの基本フォルダ名を修正）
SAMPLE100（EXCEL&ACCESS）
次回、ACCESSから取り出すEXCEL
