accessでexcelファイルを取り込むにはインポート(acImport)か、リンク(acLink)か、どっちがよろしいのか？比べて、自動化のコードをvbaで書いてみた。

　accessでexcelファイルを取り込むのにインポートが良いのか、或いはリンクが良いのか？

　元のファイルを明確にする（しておきたい）必要がある場合は、リンク(acLink)を使うのが良い。
　accessを開いた状態では、リンクしているexcelファイルがロックされるので、他のユーザがexcelファイルを更新できない（読み取り専用となる）。
　勿論、accessを閉じれば、いいだけだ（他のユーザはexcelファイルを更新できるようになる）。
　そんな使い勝手は、窮屈で嫌だというのであれば、元ファイルをその度にコピーし、複製をリンクしておくのがよろしいのではないか。
　リンクした場合、データの型変換エラーがあると、そのフィールドが＃num errorと表示されるが、実際、その値が使用されるまでわからない（と思う）。
　また、リンクしたテーブルが空（タイトル行のみで、データ行がない）の場合、そのテーブルを参照するクエリは、全て型エラーになる（タイトル行のみで、データがないため、データ型を決められない＝型が一致しないというチグハグなエラーで怒られる）ので、テーブルが空、つまり、タイトル行しかない場合は、そのクエリをスキップするとかの対応が必要となる。 vbaで書くと、If dcount("フィールド名","テーブル名") > 0 で判定し、成立した場合にクエリを出すというのがよろしい。
　excelファイルをリンクするvbaを書いてみた。これでリンク操作を自動化できる。

Sub ImportExcel()
Dim file1 as String
  file1 = "C:¥Users¥username¥Documents¥importedEXCEL.xlsx"
  if DCount("*", "MSysObjects", "[Name] = 'テーブル1'") > 0 Then DoCmd.DeleteObject acTable,"テーブル１"  ' テーブル１があれば削除する
  DoCmd.TransferSpreadsheet acLink,acSpreadsheetTypeExcel12Xml,"テーブル１",file1,True,シート名
  If Dcount("フィールド１","テーブル１") > 0 then 'テーブル１が空でなければ（フィールド１のデータが存在する）、クエリ１を実行する
    DoCmd.Query "クエリ１"
  End if
End Sub

　元のファイルを明確にする必要がなければ、インポート(acImport)を使うのが良い。
　インポートを使えば、元のexcelファイルがロックされることはない。 　インポートを使うと、型変換エラーがあったら、インポート直後にその旨のエラーメッセージが出るので、対応しやすい。ただし、エラーを見逃さない執念深さが必要である。
　excelファイルのインポート操作を自動化するvbaは、以下の通りだ。

Sub ImportExcel()
Dim file1 as String
  file1 = "C:¥Users¥username¥Documents¥importedEXCEL.xlsx"
  if DCount("*", "MSysObjects", "[Name] = 'テーブル1'") > 0 Then DoCmd.DeleteObject acTable,"テーブル１"  ' テーブル１があれば削除する
  DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12Xml,"テーブル１",file1,True,シート名
End Sub

access vba excel基本のコード

　access vba excel操作の基本のコードは、以下の通り。
　とりあえず、excel文書をオープンし、Sheet1を削除、上書き保存するコードを書いてみた。

Dim exApp As Object
Dim Wb As Object
Dim buf As string
    Set exApp = CreateObject("Excel.Application")
 　　exApp.Visible = False
 　　exApp.DisplayAlerts = False
   Set Wb = ExApp.Workbooks.Open("¥¥dir1¥dir2¥filename.xlsx")
   or
   buf = "¥¥dir1¥dir2"
   file1 = dir(buf & "filename.xlsx")
   exApp.workbooks(file1).open file1
　　With Wb
　　　　.Worksheets("Sheet1").Delete
       other codes
　　　　.Save
　　　　.Close
　　End With
　　exApp.DisplayAlerts = True
　　exApp.Quit
   Set exApp = Nothing

excelの複数シートコピーで、他シートへのリンクがあるシートは、参照元と参照先のシートを指定し、同時にコピーしなけばならない。

excelの複数のシートコピーで、他シートを参照するリンクがあるシートは、参照元と参照先のシートをarray指定し、同時にコピーしなけばならない。そうしないと、元々のファイルへの外部参照のリンクができてしまい、都合が悪具なる。

accessからexcelへDoCmd.TransferSpreadsheetでexportできるのは新規ファイルのみだ。

　accessからexcelへDoCmd.TransferSpreadsheetでexportできるのは新規ファイルのみだ。既存ファイルへexportするとtoo many filedsみたいな訳がわからないメッセージでDoCmd.TransferSpreadsheetがエラーになる。（と思う）

　どうしても既存のEXCELに落としたい場合、adoとかdaoとかで１レコードずつ、EXCELの既存シートに上書きするという方法（荒技）もあるようだ。

　でも、どうしても既存ファイルと同じ名前のファイルにエクスポートしたければ、それを削除し、エクスポートするという荒技もある。

buf = "¥¥dir1¥dir2"
file1 = buf & "filename.xlsx")
if dir(file1) <> "" then kill file1
DoCmd.TransferSpreadsheetでfile1へexport  

accessなどのデータベースを使い、ある条件のデータ抽出をするシステムを構築する場合、データのクレンジングがとても大切だ。

　データクレンジング(data cleansing)、或いはデータクリーニング(data cleaning)、またデータスクラビング(data scrubbing) は、データベースを構築する時に、まず、最初に考えておかねばならない。

　その中でもユニークデータの唯一性を担保するのは、とても大切だ。人から与えられたデータがマスタ系でユニークデータであるべきデータであるとするならば、使う前に必ず、唯一性をチェックしておこう。それを怠ると、後で、必ず、大きな「しっぺ返し」を被る。転ばぬ先の杖だ。ユニークなデータの唯一性は必ず、担保しておくこと。肝に銘じよう。

　ここで、一息のコーナー。最近、村上　春樹さんの小説１Ｑ８４を再読。ドフトエフスキーの「罪と罰」の登場人物であるラスコーリニコフとサーシャの関係性を使った表現が出てきた。 初めて１Ｑ８４を読んだ時には、罪と罰を読んでいなかったので、その文章は染みて来なかった。ところが、先日、たまたま、罪と罰を読むことができていたので、なるほどと思った。より、共感できるような気がした。

accessのクエリでこれを覚えていると善きことがある。

　クエリでIIfを使う場合、複数条件になる時には、IIf(exp1,IIf(exp2,trupart2,falsepart2),falsepart1)とIIfの入れ子にするのが楽だ。IIf(eval(exp1 AND exp2),trupart,falsepart)でもいいようだが・・・

　DateDiff関数とDateAdd関数

　開始日から終了日までの日数を求めるには、DateDiff("d",[開始日],[終了日])　

　DateDiffの"d"を”WW"にすれば、週数がわかる。

　現在から１ケ月後の日付を得るには、DateAdd("m",1,Dtae())

access vbaでexcelを扱う場合にはCreateObjectでExcel.applicationを立上げておくのがいいようだ。

そうしないと、作成したexcelブックが宙に浮いたままになるようだ。つまり、CreateObjectして、ファイルをオープンし、セーブ、クローズ、そして、EXCELアプリを.quitするといったケアをしないと、駄目なようだ。