初めて投稿します。
一つのフォルダに複数のエクセルファイルが格納されていて、そのファイルを一括でインポートを行い、インポートできたファイルのみ別フォルダへ移動するシステムを作成したいと思っています。
とりあえず一括インポートするコードは作成できましたが、フォルダを移動させるコードが、全てのエクセルファイを移動させることしかできず、インポートできたかどうかの確認ができません。インポートしたテーブルにはファイル名の情報を取り込んでいますので、そのファイルだけを移動できれば良いと思われます。本当はアクセス側でファイル名の取得を行いたいのですが、うまくいかず、エクセル側でファイル名の情報を関数で出力し、それを取り込むようにしています。
インポートのコードは
Dim dname As String
Dim fname As String
Dim tblname As String
Dim sname As String
dname = "C:\アクセス\エクセル\"
tblname = "テーブル名"
fname = Dir(dname & "*.xlsx")
sname = "sheet3!"
Do While fname <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblname, dname & fname, True, sname
fname = Dir()
Loop
としまして、ファイル移動のコードは別のボタンで
Dim fso As New Scripting.FileSystemObject
Dim sourceFile As String
Dim destinationFolder As String
' sourceFile = Application.CurrentProject.FullName
sourceFile = "C:\アクセス\エクセル\*.xlsx"
destinationFolder = "C:\アクセス\エクセル\処理済\"
fso.MoveFile sourceFile, destinationFolder
Set fso = Nothing
としました。インポートとフォルダの移動を一つのコードで行うと、最初のファイルしかインポートできずにすべてのファイルが移動してしまうので、分けてあります。
なにかヒントだけでも得られればと思っています。
どうぞよろしくお願いします。
「インポートできたファイルのみ別フォルダへ移動」とのことですが、インポートに失敗するファイルがあるということですか。そのとき、エラーはでますか。それともエラーも出ずにインポートできないファイルが存在するということですか。
まずは、インポートできない原因を探るのが先決だと思いますが。
あと、インポートはDir関数、ファイル移動は FileSystemObject を使ってますが、これはどちらかに統一した方がいいでしょう。
Dir関数にはいろいろ制限があるので、FileSystemObject の方がお勧めです。
早速教えていただきありがとうございます。
基本的にはインポートできないことは無いと考えていますが、実はまだ稼働していないシステムなので、できない場合に、どのファイルが失敗したのかを見つけ出せると便利だと思い作成を考えています。
いろいろなサイトで調べてよくわからず作ってみたので、FileSystemObjectでインポートのコードを作ってみます。
DoCmd.TransferSpreadsheet でインポートできなかったら通常はエラーがでるはずので、それで判断できると思います。
エラーが出たときのコードによる対処法は、下記が参考になるでしょう。
VBA エラー処理をする (On Errer GoTo)
FileSystemObjectでファイル名を取得する方法は下記が参考になるでしょう。
VBA ファイルの一覧を取得
かなり難易度は高いと思いますので、とりあえず、上記やいろいろなサイトを参考にできるところまでコードを書いてみてください。
そのうえで躓いたら、その時点でのコードを提示してここに追記質問してください。
いろいろ教えていただきありがとうございます。
難しそうですが、試してみます。
時間がかかりそうですが、確認後、わからないところがあったらまたよろしくお願いします。
エラー処理の方が敷居が高いので、まずは、FileSystemObjectでフォルダー内のファイル名を取得して、それをインポート、直後にそのファイルを別フォルダーに移動する、というループ処理を作成してみてください。
それで、インポートに失敗することがあるようなら、それからエラー処理を追加するといいでしょう。
とりあえず、FileSystemObjectでファイル名取得、拡張子が xlsx のファイルをインポート後、別フォルダーへ移動するサンプルコードを書いたので置いておきます。
インポートがファイル個別にできているのでそこに処理を追加すればいいですね
これも同じタイミング（ファイルごとにインポートできたタイミング）でやればいいと思います
ステートメントによるファイル処理には「ファイルの移動」がないので「コピー＆削除」で対応します
ファイル名を書き込む処理は主題からずれているので、とりあえず、「ここでやればいいよ」とだけにしておきます。具体的にはインポートファイル名が未記入のレコードに対して更新クエリを発行すればいいでしょう（別な方法でうまくいっているのであればそれでもいいと思います）
同様に、FileSystemObjectを使うかどうかもひとまず置いておきます
エラー処理について
「エラー処理をする」のと「エラーを無視して処理をする」は似ているようで異なります（テクニックのテンプレートが）
インポートできたファイルはそのフォルダからなくなっているので、「インポートできなかったファイルがある＝フォルダにファイルが残っている」ことで判断できます
ファイル毎に処理をしているのでそこで記録を取ってもいいですね
私が悩んでいる間にいろいろと考えていただきありがとうございます。
なんとなく同じようになりましたが、拡張子を限定するやり方は勉強になりました。ありがとうございます。
今日作ったものに追加してみます。
ここで取得したファイル名を保存してみましたが、うまく保存できませんでした。SQLで追加してみましたが、インポートしたレコードではなく、別のレコードとして保存されてしまいました。
hirotonさんエラー処理のことを考えていただいてありがとうございます。
難しいので、教えていただいたことをよく読んで試してみます。
よくわかっていないのですが、教えていただいたコードの中で
というのは正常に処理ができた（0はエラーが発生していない）ということなのでしょうか？
初歩的な質問ですいません。
よろしくお願いします。
こんなにいろいろ教えていただけるとは思っていなかったので、本当にうれしいです。ありがとうございます。
Err オブジェクト
Err.Numberは「リセット時に0」になります。その後、「エラーの発生がなければ0のまま」です。ただし、エラーが複数回発生した場合は「最後のエラー」情報になっていることに注意が必要ですOn Error Resume Nextを使ったエラー対策は、これらをしっかりと把握している必要があるということです。提示したコードはサンプルの為、できるかぎりエラートラップに焦点を絞って記述しています
エラーの結果を変数（
インポート成功）に取っているのもエラートラップに焦点を絞るためで、適切に処理できているならばのように、エラートラップ内で処理すればいいでしょう
初心者さんのコードについてのアドバイス
コードには適切なインデント(字下げ)を付けましょう。それがいい加減だとコードが読みづらいし、バグが入り込む原因になります。
dname, tblname, sname, destinationFolder に代入するコードが
For Each ... Eachループ内にあるのは無駄ですね。固定値なので繰り返し代入する必要はないので、
For Each ... Eachの前に移動した方がいいでしょう。For Each f In fl.Filesはフォルダー内のすべてのファイルを取得しますので、拡張子がxlsxかどうか確認するコードが必要になります。私の回答のコードでは、GetExtensionNameで拡張子を取り出して確認してますが、他にLike演算子で下記のようなコードでも可能です。If f.Name Like "*.xlsx" Thenまずはエラー処理なしのコードで実行してみて、インポートに失敗するのかどうか、エラーが出るのかどうか、確認しましょう。そしてその原因が何か特定しましょう。
その原因によって、コードにエラー処理(エラー無視も含む)をすべきなのか、それとも別の対処法がいいかのか、変わってくるでしょう。(エクセルファイルが壊れている、フィールド名が異なっている、他のユーザーがエクセルをファイルを編集中など....)
追加では新規レコードとして保存されますね。やりたいことは、下記のようなことでしょうか。
取り込み先テーブル(
tblname = "テーブル名")に「フィールド名」というフィールドがあり、そのテーブルにインポートしたときに、インポートした各レコードにファイル名を書き込みたい。だとしたら、更新クエリになりますね。インポート直後に下記のようなSQLを実行すればいいでしょう。
前回の私のコードに追加するなら、下記のようになります。
DoCmd.RunSQLでSQLを実行してもいいのですが、いちいち確認メッセージがでるので、それをオフにしておいた方がいいでしょう。CurrentDb.Executeなら確認なしに実行します。エラー処理は、まずは、これが正常に動作することを確認して、各コードの意味を理解してからでいいと思います。
一気に理解しようとしても混乱するように思いますがどうでしょうか。
とりあえずのエラー処理を追加したサンプルコード
エラー処理のやり方がこんなにあるとは知りませんでした。もっと勉強してみます。
今回教えていただいたコードでやりたかったことがほとんどできている感じがします。
ありがとうございます。
CurrentDB.Executeでもsqlが実行できるんですね。しかも確認無しで。勉強になりました。
教えていただいたコードの中にエラー内容を別テーブル（T3）に保存するコードを追加してみたのですが、err.descriptionだけが保存できませんでした。ファイル名とエラー番号がわかるだけでもすごく助かりますが、エラー内容も一緒に保存出来たらもっとありがたいのですが、可能でしょうか？
データ型を長いテキストにしてみたのですが、やはり保存されませんでした。（イミディエイトウィンドウには出てきてるのですが）エラー番号は3011でした。
コードを見る限りは問題なさそうなんですけどね。
Debug.Print の部分を下記のようにSQLを出力するようにして、そのSQLが正しいものになっているか確認して、さらに、そのSQLをクエリのSQLビューに貼り付けて実行して正しく実行されるか確認してみてください。
そんなにはないです。自分がやっている処理を正確に把握して、適切にチェックしないと不慮の事態が発生します。やり方はたいてい決まった形になるでしょう。エラー処理は難しいとされるゆえんですね
書き方ならいくつかあります
かなりくどいですが、エラーが発生しうる処理それぞれに絞ってエラー処理を組み込んでいます。コードの記述ミスに対して強い書き方でしょう
くどいですが、よくある
On Error Resume Nextの乱用問題を起こすよりはよっぽどマシですちなみに上記処理では、
CurrentDb.Executeの処理にエラートラップが掛かっていません。当然、何か処理を行えばエラーが発生する可能性があるので、ここも十分に検討が必要なポイントになります>> 14のコードを見直してみましょう
インポート処理では「テーブル名」テーブルを指定しているのにファイル名の書き込み処理では「T2」テーブルを指定しています
まぁ、ただの記述ミスなんでしょうが、実際に記述ミスがされていたとして、どうプログラムが動くのか考えてみてください。原因不明の問題に悩まされることでしょう
これについてはhatenaさん指摘の通り、エラートラップ処理を入れる前に正常な動作をするプログラムかどうかを確認すべきということです
やり方ということであれば、エラーがなかった時だけ続きの処理をやる＝エラーが発生したら中断する方向で処理を組むというのは考えられます
中断がさせやすい（分かりやすい）ように処理を専用の関数に独立させてエラートラップをしています
（関数にしなくても、
Resume line（Resume ステートメント）で同じことはできますが、ジャンプ処理はオススメしません）入れ子構造が深くなったり、
Err.Numberのチェックが繰り返されるよりはだいぶわかりやすい処理になっていると思います。ここまでするのは関数を作るコストもあるので一概に良いとは言えませんがエラー処理が複雑化する（させる）ならOn Error GoTo Labelを使った方法を考えたほうがいいと思いますhatenaさんに教えていただいた方法で確認してみました。
クエリを実行するとエラーが発生しました。
Err.Descriptionの中にシングルクォーテーションで括った文字が入っているのが問題のようでした。
なので、Err.Descriptionのシングルクォーテーションをダブルクォーテーションで括ったら保存できました。
確認の仕方を教えていただいてありがとうございました。とても勉強になりました。
hirotonさんエラーについて教えていただいてありがとうございます。
仕事に使うシステムになるので、不慮の事故が起きるとまずいですよね。
もっとエラーについて勉強してみます。
hirotonの回答はあくまでもエラートラップの方法についてです。具体的な案件に対するエラー対策面についてはほとんど触れていません。一概にエラートラップを入れれば解決するというものでもありません。十分に検討してください
今出ているコードの例では、「インポートに成功したがファイル移動に失敗した」という事態を想定しています（「そのような状況を許すプログラムを組んでいる」と読み取れます）
この状態で再度インポート処理が実行されたらどうなるでしょうか？
Accessはたいていの不都合ならエラーを出して止まってくれるソフトウェアです。大規模なシステムを組むとか、販売用のシステムを組むとかであればいろいろ神経質にならないといけないですが、上記のようなよほどの特殊な事態は標準のエラーに任せてしまって十分だと思います。（当然エラー発生時の対応体制は必要になります）
システムを使うユーザーレベルにそういうものだと理解してもらうのがうまく運用するコツだと思いますよ
hirotonさんありがとうございます。
今回はエラーで止めるより、止めずにインポートできるものだけインポートしてインポートできなかったものは元のフォルダに戻り、インポートできなかったファイル名とエラーも出力されるので、後からわかるので、いいような気がします。
ただ、エラー処理についてほとんど考えたことがなかったので、もう一度コードを見直してみます。
もう一つ教えていただきたいことがあるのですが、質問の内容と外れているので、別の質問であげたほうがいいのかもしれませんが。
今回想定しているエクセルは、１つのファイルにレコードは一つ、フィールドは複数という想定です。
できれば列（横）方向にフィールド名、行（縦）方向にレコードになるようにしようと思っていますが、元々申請書類をそのままエクセルで印刷できるようになっているため、テーブルに保存したいデータがいろんなセルにある場合に、複数のセルを指定してインポートすることは可能でしょうか？
保存テーブルのフィールド名をf1,f2,f3,f4,f5というように作成しておいて、読み込むエクセルのセルの場所をを順番にA2,E2,A5,E5（f5にはファイル名）というように指定していこうと思っています。
一つのセルなら指定できましたが、複数となると、うまくいかないのですが、可能でしょうか？
決まった様式なので、セルの座標は固定、シートも同じなのですが、場所が点在している状態です。元のエクセルで数式を使って格子状に配列すれば良いのですが、後からの修正が困難なので、今後追加で保存したい項目が出てきたときになかなかできなくなってしまうので、アクセス側で変更できれば便利だと思い質問させていただいています。どうぞよろしくお願いします。
この中で
をいろいろ書き方を変えてみたのですが、ダメでした。
どうぞよろしくお願いします。
エクセルのデータをテーブルにインポートする場合は、１行１レコードとなりますので、A2,E2,A5,E5 というように行が異なるものをインポートすることはできません。
そのような要件なら、インポート操作以外の方法になります。
具体的には、エクセルブックを開いて(非表示で開くことも可)、シートの各セルを読み込んで、テーブルに書き込んでいく処理を書くことになります。
一から作り直しになりますので、新規に質問しなおした方がいいでしょう。
ありがとうございます。
そんな気がしてました。
教えていただいた処理の仕方を調べてみます。
わからないことがあれば新規に質問させていただきますので、どうぞよろしくお願いします。