[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別ブックへの転記』（KAORI）
Excel2003使用
[[20121128164620]] で同じ質問をしてしまったのですが・・・
転記元ブックC5 → 転記先ブックA列の3行目～ 転記元ブックC7 → 転記先ブックB列の3行目～ 転記元ブックC3 → 転記先ブックC列の3行目～ ・ ・ ・ と、いくつか続くのですが転記元にマクロを組んで転記先ブックは表示させずに転記後に上書き保存できればと考えています。
転記先は台帳管理用なので3行目・4行目・5行目と転記元のマクロを実行するたびに行が下にずれていく感じです。
ご教授お願いできますでしょうか？
過去ログなどをいろいろ見たのですが同じ内容でもコードの書き方が色々あってよく分からず同様内容でも質問スレを立ててしまいました。
使用イメージ等をもう少し具体的に書いておかれると良いと思います。 ・転記元ブックのすべてのセルに すべてデータが入力された状態で実行しようと思っているのか？ ・一回の転記で、転記先ブックの1行分なのか など。。。
また、そちらのデータイメージがつくようなサンプルデータを作ってもらえると良いと思います。
転記元ブック
[A] [B] [C]
[1]
[2]
[3] 入力日 2012/11/30
[4]
[5] 顧客CD EX036
[6]
[7] 顧客名 エクセル商事
[8]
[9] 備考
転記先ブック
[A] [B] [C] [D]
[1]
[2] 顧客CD 顧客名 入力日 備考
[3] EX036 エクセル商事 2012/11/30
[4]
こんな感じでサンプルが書いてあれば 推測できることも多くなります。
たとえば、 備考があるので「すべて埋まらなくても転記するな」とか どうも 一回に一つのデータだな とか 入力日は必ず入力されるセルだろうからこの列で最終行を特定できるな とか。。。
(HANA)
HANAさま
ご指摘ありがとうございます。
確かに文章では伝わりにくい部分も出てきますよね．．．
しかもHANAさまに書いていただいたサンプルがまさに私のイメージ通りでした。
お手数おかけして申し訳ありませんでした。
転記元ブック
[A] [B] [C]
[1]
[2]
[3] 入力日 2012/11/30
[4]
[5] 顧客CD EX036
[6]
[7] 顧客名 エクセル商事
[8]
[9] 備考
[10] 機械名 123-33 AAA
転記先ブック
[A] [B] [C] [D] [E] [F]
[1]
[2] 顧客CD 顧客名 入力日 備考 機械名 機械種類
[3] EX036 エクセル商事 2012/11/30 123-33 AAA
[4]
HANAさまのサンプルをお借りして、追加します。
※ 必ず全ての項目を入力するわけではないので転記元が空欄ならば転記先も空白 ※ 転記元C10では『123-33 AAA』なのですが転記先ではE列とF列に分けて転記したい。 (必ず文字数が同じ、数字とアルファベットではないので機械名と機械種類の間に半角のスペースが入っているのでスペースで判断したい) ※ 転記先は一年分の台帳用なので年度が変われば自動で新しいブックになり2行目までの項目や書式はそのままで3行目から新たに保存したい。保存名は台帳2013とか
最後のは出来るのかすら私には分からないのですが・・
さらに説明が足りないようであれば、ご指摘お願いします。
(KAORI)
＞※ 転記先は一年分の台帳用なので年度が変われば自動で新しいブックになり2行目までの項目や書式はそのままで3行目から新たに保存したい。 「年度」ってのが曲者ですが・・・4月始まりですか？
「自動で新しいブック」と言う事ですが、2行目のタイトル等をどうしてよいかわからないので テンプレートブックを一つ作っておいて、マクロでそれを複製することにするのはどうでしょう？
大きな流れとしては i ．今年度の台帳ブックがあるか確認、無い場合は作成。 ii ．今年度の台帳ブックを開く。 iii．転記先の最終行を取得する。 iV ．データを転記する。 V ．上書き保存する。 Vi ．台帳ブックを閉じる。 vii．入力ブックの転記済データを削除する。 と言う感じで良いのではないかと思います。
エクセルには「マクロの記録」と言うツールが用意されていますので いくつかの部分は、記録で出来たコードを参考にできると思います。
まずは、ご説明して下さったのと同じレイアウトのブックを作って下さい。 転記元ブック名は「入力」シート名は・・・入力 転記先ブック名は「台帳2012」シート名は・・・データ それから「台帳テンプレート」ですね。
「台帳テンプレート」と「台帳2012」は、2行目に項目名だけ入れておいてください。
次にマクロの記録に関してです。
入力ブックを開いて、マクロの記録を開始して下さい。 マクロの保存先は「作業中のブック」です。
記録する手順は １．メニューから「台帳2012」ブックを開く ２．データシートのA5セルを選択 ３．Ctrl + ↑ ・・・・A2セルがアクティブになるハズです。 ４．上書き保存 ５．「台帳2012」ブックを閉じる 以上の作業です。
出来たらコードをこちらに乗せて下さい。
マクロの記録は、こちらもご参考に。。。 http://www.excel.studio-kazu.jp/lib/e4b/e4b.html ライブラリ「マクロの自動記録」
最初の画面の絵は、2007での手順になっています。
2003の場合はその下に文字で書いてある
Excel2003：ワークシート側から [ツール(T)]メニュー→[マクロ(M)]→[新しいマクロの記録(R)]
を選択します
をして下さい。
この作業は ii,iii,v,vi 部分のコードになります。
(HANA)
HANAさま
>「年度」ってのが曲者ですが・・・4月始まりですか？ スミマセン。訂正させてください。 受注日が2012年だと2013年でも台帳2012に書き込みたいので入力日の項目を受注番号としていて 『GG12-1111』と入力していくのですがGG12←この12が2012年を意味します。GG13なら2013年 なので受注番号で判断できるようになりますか?
>テンプレートブックを一つ作っておいて、マクロでそれを複製することにするのはどうでしょう？ 私には思いつかなかったのでテンプレートブック作ります。
>出来たらコードをこちらに乗せて下さい。
Sub Macro1() ' '
ChDir "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）"
Workbooks.Open Filename:= _
"\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls"
Windows("入力 .xls").Activate
Range("A5").Select
Selection.End(xlUp).Select
ActiveWorkbook.Save
Windows("台帳2012.xls").Activate
ActiveWindow.Close
End Sub
私の使用しているPCは2003なのですが他に数台2000.2002を使用しているPCがあります。 全てに対応できるでしょうか?
(KAORI)
他のPCでも使える様に ってのは大丈夫ですが 「同時に複数のPCから一つの台帳に変更をかける」 となると、 同じタイミングで転記しようとした時にどうするか とか 誰かがすでに台帳ブックを開いていた時にどうするか とか 複雑になってしまいますが。。。そういった点ではどうですか？
＞ 受注番号で判断できるようになりますか? そうですね。 サンプルでは「顧客CD」に受注番号が入っている事にしますね。 話が面倒になるので、以降はC5セルとかA列を「受注番号」っていう事にします。
載せてもらったコードですが。。。
台帳2012を開いた後
入力ブックをアクティブにして Windows("入力 .xls").Activate
入力シートのA5セルを選択して Range("A5").Select
Ctrl + ↑ Selection.End(xlUp).Select
をしましたね？
でもって、そのまま上書き保存してるので 保存されたのは 入力ブックです。。。。
台帳2012ブックのデータシートで A5セルを選択し Ctrl + ↑ をやった時 アクティブセルがA2セルに移動する事を確認して下さい。
その時のコードが
Range("A5").Select
Selection.End(xlUp).Select
ですが、2行目の Selectionと言うのはその上の A5セルの事なので
Range("A5").End(xlUp).Select
の様に一行で書けます。
これで、上方向に入力がある最後のセルに移動できます。 運用を続けていくと A列のデータは確実に5行目より増えますので このまま使うのでは都合が悪いです。
そこで、「シートの行数」を取得できる Rows.Count と組み合わせてみます。
Range("A" & Rows.Count).End(xlUp).Select
この様にしておけば シートの一番下まで入力が無い限り データの最後のセルに移動できます。
この記述では 記入がある最後のセルが【選択】されますが ここの目的は ＞iii．転記先の最終行を取得する。 です。
セルの行番号は Range(セル).Row で取得できますので
Range("A" & Rows.Count).End(xlUp).Row
これが最終行。
転記するのは その一つ下の行なので
転記先行 = Range("A" & Rows.Count).End(xlUp).Row + 1
ですね。
こんな感じ(？)で ii～iv までの部分を作ってみます。 まずは 台帳2012.xls を固定にしたコードです。
'------
Sub TEST1()
Dim 転記先行 As Long
Dim 転記元 As Worksheet
Set 転記元 = ThisWorkbook.Sheets("入力")
'ブックを開く
With Workbooks.Open("\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls")
With .Sheets("データ")
'転記先行取得
転記先行 = .Range("A" & Rows.Count).End(xlUp).Row + 1
'データ転記
.Range("A" & 転記先行).Value = 転記元.Range("C5").Value '受注番号
.Range("B" & 転記先行).Value = 転記元.Range("C7").Value '顧客名
.Range("C" & 転記先行).Value = 転記元.Range("C3").Value '入力日
.Range("D" & 転記先行).Value = 転記元.Range("C9").Value '備考
.Range("E" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(0) '機械名
.Range("F" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(1) '機械種類
End With
'上書き保存
.Save
'閉じる
.Close
End With
Set 転記元 = Nothing
End Sub
'------
「.」の前には With ～ の部分が省略されていると思って下さい。
たとえば 転記先行を取得するところの .Range("A" & Rows.Count).End(xlUp).Row は
Workbooks("～～台帳2012.xls").Sheets("データ").Range("A" & Rows.Count).End(xlUp).Row
最後の .Close の所は
Workbooks("～～台帳2012.xls").Close
です。
入力ブックにコードを貼りつけたら 入力ブックだけが開いている状態にして 入力シートをアクティブにして（いくつかデータも入れておいてください。） コードを貼りつけたウィンドウに戻り、[F8]を押して一行ずつ実行してみて下さい。
目的のブックが開いて 一つずつ転記され 上書きされて終了したら
まずは意図通りの動きが出来ています。 途中でエラーが出て止まったら、どこで止まったのか＆エラーメッセージは何か。 あわせて教えて下さい。
(HANA)
HANAさま
説明も含め、コードの提示ありがとうございます。
今までコードの理解をせずにネットから拾ってつなぎ合わせてやってきたので勉強になります。
結果、エラーも出ずに転記できました。
>「同時に複数のPCから一つの台帳に変更をかける」 となると、 同じタイミングで転記しようとした時にどうするか とか 誰かがすでに台帳ブックを開いていた時にどうするか とか 複雑になってしまいますが。。。そういった点ではどうですか？ この点に関しては入力者は1人なので大丈夫です。 ただ、場所を変えて入力する場合もあったので・・・
(KAORRI)
HANAさま
抜けがありました・・・
>台帳2012ブックのデータシートで A5セルを選択し Ctrl + ↑ をやった時 アクティブセルがA2セルに移動する事を確認して下さい。 A2になること確認しました。
(KAORI)
TEST1はうまく動きましたか。 内容も、大きくは大丈夫ですか。。。？
色々な所で作業をする ってだけなら、問題なさそうです。
でしたら i ．今年度の台帳ブックがあるか確認、無い場合は作成。 をやらないといけないですね。
まずは 受注番号から年を取得してみますが。。。。
＞『GG12-1111』
20○○限定（・・・で良いとは思いますが）にすると
Sub sample1()
MsgBox "20" & Mid("GG12-1111", 3, 2)
End Sub
こんな感じで、Mid関数がつかえそうです。
もしも「必ず受注日の年とそろえるから！！」と言うのなら
そこから年を取得しても良いと思います。
Sub sample2()
MsgBox Year("2012/11/30")
End Sub
これなら 注文番号を付けるルール 最初にアルファベットが何個付くか 下2桁で表すか、4桁すべて記述するか 等が変わっても、コードを変える必要は無くなります。
イレギュラーがあって これは前年受注だけど、今年にしたい とか、 やっぱり4月始まりで分かれてる方が便利 なんてのがあると 問題が出ます。
どちらが良いかは KAORIさんにお任せします。
まずは、ブックの有無を確認するコードから。。。
'------
Sub TEST2()
Dim MyPath As String
MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls"
If Dir(MyPath) = "" Then
MsgBox "ブックがありません"
End If
End Sub
'------
ブック名を変更して確認してみて下さい。
(HANA)
HANAさま
>i ．今年度の台帳ブックがあるか確認、無い場合は作成。 をやらないといけないですね。 サンプル1のMid関数でやりたいと思います。 必ずアルファベット2文字の後に12.13とつくので。
>ブックの有無を確認するコード ブック名を変更してメッセージボックス『ブックがありません』とでました。
追加
>内容も、大きくは大丈夫ですか。。。？ 私の思い描いたとおりになりました。 当初、セルC10は関数をセルに入れるしかないのかな。と考えていたので。
(KAORI)
TEST2では、ブックが無かった時にメッセージを出しましたが 本当はそんな事せずに・・・入力ミスも疑った方が良い？・・・ 複製を。
今回はそっくりそのまま複製して、名前だけ変われば良いので FileCopyステートメント を使うと便利だと思います。
構文 FileCopy source, destination source 必ず指定します。 コピーするファイル名を示す文字列式を指定します。 フォルダ名およびドライブ名を含めて指定できます。 destination 必ず指定します。 コピー後のファイル名を示す文字列式を指定します。 フォルダ名およびドライブ名を含めて指定できます。
実際の例は、Web検索してみて下さい。たくさん見つかると思います。
今回は source に元のブックを指定するので "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" destinationは、コピー後を指定するので MyPath ですね。
で、MyPath の中の年部分は Mid関数を使ったものと組み合わせですね。
MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳20" & & Mid("GG12-1111", 3, 2) & ".xls"
こんな感じで 組み合わせられそうですか？
(HANA)
HANAさま
上記コードは‘データ転記後のEnd Withの後に組み込めばいいのでしょうか？
(KAORI)
いや、まずは TEST2 を改造してもらって TEST3 を作る。
上手くいったら TEST1とTEST3を組み合わせて
今は直接「GG12-1111」と書いてある所をセル参照・・・転記元.Range("C5").Value・・・にしたり
TEST1の方も、もともとはパスが直接書いてあるので それを MyPath にしたり です。
単純に張り付けるだけじゃなく、少しは構文を読んで 組み替えてもらわないといけないです。
ちなみに、TEST3（改造してもらって出来るコード）は ＞＞i ．今年度の台帳ブックがあるか確認、無い場合は作成。 の部分です。
で、前後してますが TEST1は ＞＞ ii～iv までの部分を作ってみます。 なので 少なくとも ＞データ転記後のEnd Withの後に って事にはならないと思いますが。。。
なんか思い違いがありますか。。。？
(HANA)
HANAさま
私の早とちりでした。
>こんな感じで 組み合わせられそうですか？ 全文を作成と思い込んでしまいました・・・
(KAORI)
＞今まで～～ネットから拾ってつなぎ合わせてやってきた～～ って書いてあったので 「組み替えたりは出来るだろうから、やってもらえば良いや。」 と思ったのですが。。。
たぶん、できると思いますので やってみて下さい。
＞vii．入力ブックの転記済データを削除する。 これも、まずはマクロの記録からですね。 Ctrlキーを押して 複数セルを選択してから Deleteキーを押す を 記録にとってみて貰うと良いと思います。
途中で悩んでしまったら、そこまででも載せてもらえると アドバイス出来る事もあるのじゃないかと思います。
(HANA)
HANAさま
Sub TEST2()
Dim source, destination As Variant
Dim MyPath As String
MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls"
source = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls"
destination = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls"
FileCopy "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳20" & & Mid("GG12-1111", 3, 2) & ".xls"
End Sub
こんな感じでしょうか？
最後のFileCopyで赤く文字が表示されてしまいます。
(KAORI)
あら～、FileCopyステートメント の実際の例は見てみましたか？ たとえばこんなページとか。 http://officetanaka.net/excel/vba/tips/tips91.htm#copy
そしたら、 次のサンプルは、"C:\Work\Sample.txt"を"D:\Tmp"フォルダに"Test.txt"という名前でコピーします。 Sub Sample() FileCopy "C:\Work\Sample.txt", "D:\Tmp\Test.txt" End Sub って書いてあります。
今回は、 "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" を、 "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls" にコピーしたいので、単純に置き換えると FileCopy "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" , "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls" ですが、後ろ側の文字（\\Documents～2012.xls）は、変数：MyPathの中に作ってあるので FileCopy "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" , MyPath
TEST2は、変数の宣言の下に MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls" と書いて、MyPathの中に "\\Documents～2012.xls" を入れてますからね。
'------
Sub TEST3()
Dim MyPath As String
MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls" '変数に入れて
FileCopy "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" , MyPath '実際に複製する命令
End Sub
'------
ただ、こうやっちゃうと、このコードを実行すると いつでも複製しようとしてしまうので TEST2で メッセージボックスを出している所（行）で メッセージボックスを出す代わりに FileCopy～ を実行してください。
って所まではどうでしょう？
(HANA)
FileCopyステートメントに関して追記ですが
FileCopy source, destination
~~1~~~ ~~2~~~~~~~~
1の位置に コピー元のブックのパス
2の位置に コピー後のブックのパス を指定しないといけないです。
いまは、MyPath と一つだけしか変数を使ってなかったですが こんな変数を使った例にすると 分かりやすかったでしょうか？
Dim コピー元 As String Dim コピー後 As String コピー元 = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" コピー後 = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls" FileCopy コピー元, コピー後
作成された「TEST2」も 各ブックのパスを一旦変数に入れておられましたよね。 あの時に FileCopy source, destination ってやっておくと、ブックがコピーされたと思います。
＞最後のFileCopyで赤く文字が表示されてしまいます。 これは、 FileCopy の後ろには「,」で区切って二つの項目を設定しないといけないのに 一つ（コピー後のパス）しか設定されていなかったからです。
なんですが。。。違いがわかりそうですか？
(HANA)
(HANAさま)
FileCopyステートメントの理解が出来ずにいましたが上記の例で色々調べてできました。
(KAORI)
それは良かったです。 基本の説明を読むことも大切ですが 簡単な使用例を参考にして 実際の文字に置き換えてチャレンジしてみるのも良いと思います。 何にしても 説明を読んだだけで、実際の状況に置き換えるながら 作成していくのは、難しいと思いますので。
さて、コードの方はどこまで出来ましたか？
実際は、
MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls"
ここの所－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－~~が
固定ではなく、「Mid("GG12-1111", 3, 2)」なんですよね。
と考えていってもらえれば良いと思うのですが。
ちなみに、前回「&&」を二つ書いちゃいましたが 実際は一つで良いので 一つにしておいてください。
(HANA)
HANAさま
Sub TEST2()
Dim BookName As String
Dim MyPath As String
MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\"
BookName = "台帳20" & Mid("GG12-1111", 3, 2) & ".xls" 'ファイル名
If Dir(MyPath & BookName) = "" Then
MsgBox "ファイルがありません"
FileCopy MyPath & "\台帳テンプレート.xls", MyPath & fyear & BookName
Exit Sub
Else
MsgBox "ファイルがあります"
End If
End Sub
あ、そうですね。 一旦フォルダ迄を変数に入れておいた方が賢い感じですね。
コードの方はそれで大丈夫でしょうか？きちんと動いてますか？
＞MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\" ＞MyPath & "\台帳テンプレート.xls" ってなっているので、台帳テンプレートのパスが "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\\台帳テンプレート.xls" と「\」が重複してしまいませんか？
また ＞MyPath & fyear & BookName 変数:fyear ~~~~~ は、何が入っているでしょう？？
コードを書いている所の一番上の行に 「Option Explicit」 を書いておくと良いかもしれません。 宣言していない変数（Dim ○○ と書いていない変数）が使えなくなります。
それから、今回はブックを複製してからも 続きの処理に進みますので ＞Eixt Sub しない方が良いと思います。
今度は、作成された TEST2と、最初に作成した TEST1 を組み合わせますが
BookName = "台帳20" & Mid("GG12-1111", 3, 2) & ".xls" 'ファイル名
ここが、実際は固定ではなく ~~~~~~~~~ 受注番号のセルの値を使いたいですよね。
TEST1では↓の記述で、受注番号のセルの値を転記してますが
.Range("A" & 転記先行).Value = 転記元.Range("C5").Value '受注番号
なので
BookName = "台帳20" & Mid(転記元.Range("C5").Value, 3, 2) & ".xls" 'ファイル名
こんな感じにしたい。
これをするためには 変数：転記元 に 転記元のシートをセットしておかないといけないです。
TEST1の↓の記述ですね。
Set 転記元 = ThisWorkbook.Sheets("入力")
なので、この二つを組み合わせて
TEST1で使う変数の名前なんかも、上の方で宣言して・・・・・・・TEST1 参考 すぐさま 転記元のシートをセット ・・・・・・・・・・・・・・TEST1 参考 MyPath に パスを入力・・・・・・・・・・・・・・・・・・・・TEST2 参考 BookName にブック名を作成・・・・・・・・・・・・・・・・・・TEST1,TEST2 参考 If Dir・・・・ End If で、ブックが無い場合ブックを作る・・・TEST2 参考 以下、TEST1のブックを開いて転記して終了～～
って感じですね。
ちなみに、マクロ名を重複しない様につけて投稿してもらえると良いと思います。 「TEST2のコードで」って言った時に、どのコードかわかりにくくなってしまいますので。
(HANA)
HANAさま
>MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\" MyPath = "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\" でした・・
> 基本の説明を読むことも大切ですが 簡単な使用例を参考にして 実際の文字に置き換えてチャレンジしてみるのも良いと思います。 まずはデスクトップ上でテストフォルダを作ってA.xls、B.xlsなどの名前で動かしてみて、実際のブック名を入れてみました。
>変数:fyear ~~~~~ は、何が入っているでしょう？？ 何も入れていません・・・ 削除わすれました・・・
一つ教えてください！ TEST1でのHANAさんに書いていただいたコードの変数 Dim 転記先行 As Long ありますが、Integerではなく、なにか意味があるのでしょうか？ マクロ初心者で変数の理解がいまいちできなくて・・・
(KAORI)
あ、すみません。Integerって書いたつもりでした。(○´ω｀○)ゞ
今回 Long だと -32,768 ～ 32,767 の値しか入らないので、問題ありますよね。 2003のワークシートの行数は65,536行ありますので。。。
指摘してもらえてよかったです。 Integer って書いて下さい。ｍ(＿ ＿；)ｍ
(HANA)
えっ、 Long と Integer 逆ですよ。 Integer の方が >-32,768 ～ 32,767 の値しか入らないので ですから、 Long のままでいいんじゃないでしょうか(^^) (Mook)
あ・・・あれ？ どこ見たのかな。。。((; =゜з゜=)) ～♪
Integer 整数型 -32,768 ～ 32,767 Long 長整数型 -2,147,483,648 ～ 2,147,483,647
Longで良いですね。
Mookさん、毎度お世話になっております。
KAORIさん って違いです。（;￣ー￣A
(HANA)
HANAさま Mookさま
>ワークシートの行数は65,536行ありますので 値の入る制限なのですね。
コード作りました。
Option Explicit Sub TEST4()
Dim 転記先行 As Long
Dim 転記元 As Worksheet
Dim BookName As String
Dim MyPath As String
Set 転記元 = ThisWorkbook.Sheets("入力")
MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\"
BookName = "台帳20" & Mid(転記元.Range("C5").Value, 3, 2) & ".xls" 'ファイル名
If Dir(MyPath & BookName) = "" Then
MsgBox "ファイルがありません"
FileCopy MyPath & "\台帳テンプレート.xls", MyPath & BookName
Else
MsgBox "ファイルがあります"
End If
'ブックを開く
With Workbooks.Open("\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls")
With .Sheets("データ")
'転記先行取得
転記先行 = .Range("A" & Rows.Count).End(xlUp).Row + 1
'データ転記
.Range("A" & 転記先行).Value = 転記元.Range("C5").Value '受注番号
.Range("B" & 転記先行).Value = 転記元.Range("C7").Value '顧客名
.Range("C" & 転記先行).Value = 転記元.Range("C3").Value '入力日
.Range("D" & 転記先行).Value = 転記元.Range("C9").Value '備考
.Range("E" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(0) '機械名
.Range("F" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(1) '機械種類
End With
'上書き保存
.Save
'閉じる
.Close
End With
Set 転記元 = Nothing
End Sub
動かしてみたところ、台帳2012.xlsに保存は出来るのですが、"GG13-1111"のときに台帳2013.xls と新しくブックはできても台帳2012.xlsのほうに保存されてしまいました。
(KAORI)
おしいっ。
＞"GG13-1111"のときに台帳2013.xls と新しくブックはできても台帳2012.xlsのほうに保存されてしまいました。
だって、開くブックが
＞With Workbooks.Open("\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls")
ですからねぇ。。。 ~~
MyPath & BookName のブックを開いて、そこに転記する事になると思いますよ？
(HANA)
HANAさま
・・・スミマセン
もう少し詳しく教えてください。
>MyPath & BookName のブックを開いて、そこに転記する事になると思いますよ？ \台帳2012.xls\はいらないという意味でしょうか？
(KAORI)
いやいやいやいや・・・・
テンプレートブックを複製する時に 変数を使わない場合
FileCopy "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳テンプレート.xls" , "\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls" って書く所を ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 変数使って FileCopy MyPath & "\台帳テンプレート.xls", MyPath & BookName って書いたじゃないですか。 ~~~~~~~~~~~~~~~~~
この後ろ側の BookName の所が、C5セルの値によって
台帳2012.xls だったり 台帳2013.xls だったりと、変わる様にしたのですよね？
BookName = "台帳20" & Mid(転記元.Range("C5").Value, 3, 2) & ".xls" 'ファイル名
で、MyPath には "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\" と入れたので
MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\"
MyPath & BookName の記述で
"Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳20" & Mid(転記元.Range("C5").Value, 3, 2) & ".xls"
って書いた事になってる。
これを
With Workbooks.Open("\\Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\台帳2012.xls")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ここでもつかって欲しい☆
(HANA)
HANAさま
なるほどです。
With Workbooks.Open(MyPath & BookName)
でいいのですね！
(KAORI)
ですです！！
で、これで転記はできたと思うので 転記済みの証＆次の入力の為に 入力シートの入力データを削除して終わるのが良いと思います。
まずはマクロの記録を試してみて 記録されたコードを参考に、TEST4につけ加えてみて下さい。
(HANA)
HANAさま
Option Explicit Sub TEST5()
Dim 転記先行 As Long
Dim 転記元 As Worksheet
Dim BookName As String
Dim MyPath As String
Set 転記元 = ThisWorkbook.Sheets("入力")
MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\"
BookName = "台帳20" & Mid(転記元.Range("C5").Value, 3, 2) & ".xls" 'ファイル名
If Dir(MyPath & BookName) = "" Then
MsgBox "ファイルがありません"
FileCopy MyPath & "\台帳テンプレート.xls", MyPath & BookName
Else
MsgBox "ファイルがあります"
End If
'ブックを開く
With Workbooks.Open(MyPath & BookName)
With .Sheets("データ")
'転記先行取得
転記先行 = .Range("A" & Rows.Count).End(xlUp).Row + 1
'データ転記
.Range("A" & 転記先行).Value = 転記元.Range("C5").Value '受注番号
.Range("B" & 転記先行).Value = 転記元.Range("C7").Value '顧客名
.Range("C" & 転記先行).Value = 転記元.Range("C3").Value '入力日
.Range("D" & 転記先行).Value = 転記元.Range("C9").Value '備考
.Range("E" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(0) '機械名
.Range("F" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(1) '機械種類
End With
'上書き保存
.Save
'閉じる
.Close
End With
Set 転記元 = Nothing
Range("C3").Select
Selection.ClearContents
Range("C5").Select
Selection.ClearContents
Range("C7").Select
Selection.ClearContents
Range("C9").Select
Selection.ClearContents
Range("C9").Select
Selection.ClearContents
Application.Quit
Windows("入力.xls").Close True
End Sub
動かして見ました。
ウィンドウは閉じるのですがエクセルファイル(?)が閉じません・・・
(KAORI)
HANAさま
Application.Quit
Windows("入力.xls").Close True
~~~~~~~
↑このせいかなと思って
Application.Quit ThisWorkbook.Close False
にしてみたらできました。
大丈夫でしょうか？
(KAORI)
あれ？全部終わってしまって良いんですか？ 続けて入力したいときなんか面倒だと思うんですが。。。
入力したら、転記して すぐ閉じる なら セルの値のクリアは別にしなくても 保存せずに閉じるだけで良いと思います。
ちなみに、記録してもらいたかった手順は ＞Ctrlキーを押して 複数セルを選択してから Deleteキーを押す を ＞記録にとってみて貰うと良いと思います。 です。
それと、最初にやった
＞ですが、2行目の Selectionと言うのはその上の A5セルの事なので
＞ Range("A5").End(xlUp).Select
＞の様に一行で書けます。
も反映させてみてもらえればと思っていました。
エクセルを終わるのはこんなページが参考になると思います。 http://www.nurs.or.jp/~ppoy/access/excel/xlA022.html
(HANA)
HANAさま
確かに・・・保存せずに閉じればいいですよね。。。
Range("C3,C5,C7,C9,C10").Select
Range("C11").Activate
Selection.ClearContents
となりました。
>Range("A5").End(xlUp).Selectの様に一行で書けます。
も反映させてみてもらえればと思っていました。
Range("C3,C5,C7,C9,C10").ClearContents
でいいのでしょうか？
(KAORI)
＞保存せずに閉じればいいですよね。。。 ただ、入力はしているので 閉じようとしたら 「変更を保存するか？」とか聞いて来るので 保存したつもり～ の ThisWorkbook.Saved = True を入れておくのが良いのではないかと思います。 もちろんこれは、閉じずに続けて入力する場合でも。 最後にはどうしても閉じますからね。
＞Range("C3,C5,C7,C9,C10").ClearContents
＞でいいのでしょうか？
そうですね。
あと、このままだと どのシートの事かわからないので
転記元.Range("C3,C5,C7,C9,C10").ClearContents
と、ThisWorkbook.Sheets("入力")だと明記しておくと良いかもしれません。
今回、すぐにエクセルを閉じてしまうなら不要ですが。
マクロの記録で出来たコードは ～Select→Selection～ となっている事が多いです。 ですが、数が多いと「ただ選択する」と言うだけの動きが、処理速度に影響してくる事もあります。 画面もちらちらしますしね。
基本的な記述はマクロの記録を参考に。 余力があれば、それをすっきりさせて。 コードを作っていく事にすると、少しは簡単に作れるのではないかと思います。
(HANA)
HANAさま
Option Explicit Sub TEST6()
Dim 転記先行 As Long
Dim 転記元 As Worksheet
Dim BookName As String
Dim MyPath As String
Set 転記元 = ThisWorkbook.Sheets("入力")
MyPath = "Documents and Settings\機械\加工\表作成\報告書台帳原紙、ほか(予備など）\"
BookName = "台帳20" & Mid(転記元.Range("C5").Value, 3, 2) & ".xls" 'ファイル名
If Dir(MyPath & BookName) = "" Then
MsgBox "ファイルがありません"
FileCopy MyPath & "\台帳テンプレート.xls", MyPath & BookName
Else
MsgBox "ファイルがあります"
End If
'ブックを開く
With Workbooks.Open(MyPath & BookName)
With .Sheets("データ")
'転記先行取得
転記先行 = .Range("A" & Rows.Count).End(xlUp).Row + 1
'データ転記
.Range("A" & 転記先行).Value = 転記元.Range("C5").Value '受注番号
.Range("B" & 転記先行).Value = 転記元.Range("C7").Value '顧客名
.Range("C" & 転記先行).Value = 転記元.Range("C3").Value '入力日
.Range("D" & 転記先行).Value = 転記元.Range("C9").Value '備考
.Range("E" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(0) '機械名
.Range("F" & 転記先行).Value = Split(転記元.Range("C10").Value, " ")(1) '機械種類
End With
'上書き保存
.Save
'閉じる
.Close
End With
Set 転記元 = Nothing
Set 転記元 = ThisWorkbook.Sheets("入力")
転記元.Range("C3,C5,C7,C9,C10").ClearContents
ThisWorkbook.Saved = True
End Sub
>基本的な記述はマクロの記録を参考に。 確かに今までネットで調べてのコピペでなんとなく動いたからいいかな。 くらいでやってきたので、まずはマクロの記録からどんなコードになるかを確認しながら覚えていきたいと思います。 まだ、自分でコードの書き換えはできませんが・・・
(KAORI)
＞ネットで調べてのコピペでなんとなく動いたからいいかな。 これも良いと思います。 コピペで動くって事は、構文に従ってエクセルに指示を出せてる って事だと思いますので。 。。。私は基本的に、つぎはぎしながらコード作ってます。。。
ネットで調べると、思いがけない記事に出会えたりします。 また、やりたい事がすべて記録でとれるわけではないので。
さて、TEST6の最後の方ですが
End With のすぐ後に
Set 転記元 = Nothing
をしているので、次に使う前に
Set 転記元 = ThisWorkbook.Sheets("入力")
が必要になっています。
End Sub の前に「Set 転記元 = Nothing」をすれば良いと思いますよ。
(HANA)
HANAさま
上記のコードに変更して理想の形になりました。
解説つきで最後までお付き合いいただきありがとうございました！
また何かありましたら宜しくお願いします。 (すぐに聞いちゃいそうですが・・・)
(KAORI)
上手くできましたか。良かったです。 こちらこそ、無事に完成させて頂き、ありがとうございました。
使用しながら、さらなる改良を目指してください。(`･ω･´)
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.