エクセルVBAのテキスト保存の仕方

質問

次のようなマクロがあり、最後の行でできあがった表をクリップしています。
現在そのクリップボードの表をテキストに手動で貼り付けてその都度保存しているのですが
これを自動的にテキスト保存できればと思っています。
更にファイル名は左上１セルめ（この場合多分A1000のセル)にある文字列の最初から10文字をファイル名にしたいです。

マクロはよく知らないので記述の仕方を教えてください。
※このマクロはマクロの記録により自動作成したものです。
よろしくお願いします。
(office2013/Windows7)

Sub rep1000行削除集約()

Dim i As Long
Range("AC1.AI650").Copy
Range("A1000").PasteSpecial Paste:=xlPasteValues
Range("A1000:A2000").Value = Range("A1000:A2000").Value
Range("A1000:A2000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.Goto Reference:="R1000C1"
Range("A1000:G1000").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

End Sub

回答No.6

keithin · ベストアンサー

＞というのは前述のようにAC-AI列は元データA1～A170に対し「A1="","",」のようにしているため

そうですか？

回答は寄せられた補足の
＞ACでは=IF(ISERR(FIND("■",A2)),I2,"■")

を元にしています。
ここには=IF(～="","",)としてなかったので、ゼロ非表示を解除して１７０行までゼロになってますか確認してくださいとお返事してるんですが、更にＩ列にも＝””が入ってたんでしょうか。それとも補足の情報提供の間違い？

まぁどちらにしても
再掲：
＞要するに★のところで、「あなたのエクセルの事実に基づいて」各行のどこが一体何だったらスキップするのかしないのか

という事ですから、

　if h <> 0 and h <> "" then　’★

とでもしてみてください。

＃補足
まぁご相談の範疇では無かったので特に言ってませんでしたが、こんな具合に「ゼロを非表示にして空白に見える」のと「実際に何かが計算されている」のでは全く別物なので、特にマクロを使う場合には「実際にセルには何が入ってるのか」「目に見えるエクセルの事実」が重要になってくるってことです。「目に見える」ってのでひっかかったのでしたら、「数式バーに見えている」と言った方が良かったかもですね。

もうちょっと具体的に補足すると、ＡＣ列（１０００行以下に転記した時のＡ列）では
１．Ａ列に■があって■が計算されている
２．Ｉ列に何かが入っていてそれが再掲されている
３．Ｉ列が空白で、ＡＣ列には数字のゼロが計算されている
の３通りの計算結果が現れます。
更に言えば今回推測した（事実かどうかは知りません）ように
４．Ｉ列には””が計算されているので、ＡＣ列にも何かとして””が計算されている
という状況まで踏まえて、３と４のケースで行を削除したかったんだというところまで整理が付いてはじめて、そのようにマクロに書かなきゃならなかったってことです。

お礼

I列は=IF(C2="","",～
C列は=IF(A2="","",～
となっており、そのため
AC列もACでは=IF(ISERR(FIND("■",A2)),I2,"■")
によりA2が空白（算式なし)の場合、AC2が空白(算式あり)になります。
目に見える」ってのでひっかかったというより意味がわからなかったのですが「算式有りセル」と解釈すればいいでしょうか。

補足

なお、今回のご指示のように修正した結果行詰めがうまくゆきました。
どうもありがとうございました。

回答No.5

keithin

元データにゴミが混じっているのが原因と推測できます。

＞データ最終行のあと170行まで１行７つのタブと改行のマークが残ってしまいました。

まず、ゼロ非表示の設定を解除して、たとえばＡＣ１１セルがゼロ表示になる事をキチンと確認してください。
そのうえで、問題のＡＣ列のデータの最終行から１７０行までの間のセルが、ＡＣ１１と同じようには「ゼロ表示にならない」事を確認してください

ＡＣ列の元データになっているＩ列の該当セルを選んでＤｅｌｅｔｅキーを押し、Ｉ列のゴミデータを消去してＡＣ列にキチンとゼロが表示される（ゼロ非表示にすれば空白表示に戻る）ように整備してください。

マクロは回答したまま変更ありませんが、１０００行以下にそれを再掲したいという新しい内容が追加なら。

sub macro5()
　dim myPath as string
　dim flg as boolean
　dim h as range

　mypath = thisworkbook.path & "\"
　range("A999") = "temp"

　with application
　for each h in range("AC2:AC650")
　　if h <> 0 then　’★

　　if not flg then
　　open mypath & left(h.offset(0, 6).value, 10) & ".txt" for output as #1
　　flg = true
　　end if
　　print #1, join(.transpose(.transpose(h.resize(1, 7))), vbtab)

　　range("A65536").end(xlup).offset(1).resize(1, 7).value = h.resize(1, 7).value

　　end if
　next
　end with
　close #1
　range("A999").clearcontents
end sub

＃Ｉ列に「実際には何が入っていて」ＡＣ１７０までがゼロにならなかったのかご自分で解明して、それを★のとこに組み込んでも構いません。

お礼

お手数をおかけします。
タブで表示される空白行に該当する、AC-AI列のデータ末行以下170行までの欄はここはゼロ表示にはなりません。
（ゼロ表示はほかの部分になります）
というのは前述のようにAC-AI列は元データA1～A170に対し「A1="","",」のようにしているため、A列にもともとデータがないセルを反映しているからです。このため関数式の入っている空白セルということになります。
ちなみに、AC-AI列のデータのない部分の関数式を削除したら
テキストでもすべてカットされていました。

また、「再掲したいという新しい内容が追加」といのは追加ではなく、質問欄にあるVBAではそのようになっていると思いましたので、それをそのままお願いしたまですのでご理解ください。

回答No.4

keithin

sub macro4()
　dim myPath as string
　dim flg as boolean

　mypath = thisworkbook.path & "\"

　with application
　for each h in range("AC2:AC650")
　　if h <> 0 then　’★

　　if not flg then
　　open mypath & left(h.offset(0, 6).value, 10) & ".txt" for output as #1
　　flg = true
　　end if

　　print #1, join(.transpose(.transpose(h.resize(1, 7))), vbtab)
　　end if
　next
　end with
　close #1
end sub

お礼

度々お手数をかけます。
やはりデータ最終行のあと170行まで１行７つのタブと改行のマークが残ってしまいました。
それにA1000～G2000までのところにもデータは残したいので、質問欄にあるVBAに加えてファイル作成のコマンドの２行
mypath = thisworkbook.path & "\"
　open mypath & left(range("G1000").value, 10) & ".txt" for output as #1

を勝手に加えてみたのですが、ファイルは作成されたものの中身のないファイルになってしまいました。
（ファイルにクリップデータの貼り付けをするコマンドがないので当然ですが）
私の方ももう少しベンキョウしてみたいと思います。
ありがとうございました。

回答No.3

keithin

sub macro3()
　dim h as range
　dim myPath as string

　mypath = thisworkbook.path & "\"
　open mypath & left(range("G1000").value, 10) & ".txt" for output as #1

　with application
　for each h in range("AC2:AC650")
　　if h <> 0 then　’★
　　print #1, join(.transpose(.transpose(h.resize(1, 7))), vbtab)
　　end if
　next
　end with
　close #1
end sub

要するに★のところで、「あなたのエクセルの事実に基づいて」各行のどこが一体何だったらスキップするのかしないのか、ってだけの事です。

お礼

わかりにくくてすみません。
前回書いた後にすぐ気がついたのですが、修正不可だったので、再度修正になってしまいますが、
AC1～AI650のデータを、行削除作業のためにA1000に数値Copyしたあと、A列(A1000～A2000)のブランクセル、ゼロ非表示空白セルを検索し、その行を削除します。
前回の行削除のうち1行目もブランクなので削除対象になります。
その行削除（要するに行つめ）をした矩形（タブ）データをテキストに保存します。その際のファイル名は行つめしたあとの1行目右はじセル(G1000)の一部を使用します。

回答No.2

keithin

あぁ、ごめんなさい、行内に１つでも空セルがあったらその行丸ごと削りなんですね。
いや違うか。空セルなんじゃなくて？数式でゼロが計算されていて、それを表示してないと言いたかったのですか。

sub macro2()
　dim h as range
　dim myPath as string

　mypath = thisworkbook.path & "\"
　open mypath & left(range("G1000").value, 10) & ".txt" for output as #1

　with application
　for each h in range("AC1:AC650")
　　if .countif(h.resize(1, 7), ">0") = 7 then
　　print #1, join(.transpose(.transpose(h.resize(1, 7))), vbtab)
　　end if
　next
　end with
　close #1
end sub

「ブランク」ってのは、「セルにホントに何も記入されてない」という意味です。＝””が計算されていたり、数式の結果のゼロを表示してないとかは、ブランクではありません。自分言葉でこういう祖語になります。

まだ上手く出来ない時は、ＡＣからＡＩ列にはホントのところ
○実際にセルには何を入れてて、
○「消したいセル」「消したくないセル」はそれぞれ具体的にどういう結果になってるのか
自分言葉じゃなく「目に見えるあなたのエクセルの事実」をご相談で情報提供するようにしてください。

お礼

素人考えでゼロになるセルもブランクって考えていました。
ご指摘のように
=""で空白（ゼロ非表示)にしています。
ACでは=IF(ISERR(FIND("■",A2)),I2,"■")
AGでは=IF(G2="","",G2)
AIでは=IF(J2="","",SUBSTITUTE($A$1,LEFT($A$1,1),"",1)&SUBSTITUTE($A$2,LEFT($A$2,1),"",1))
そのほかのセルはACと類似です。
ちなみにAC1～AI1はデータのない完全ブランクセルです。
結果的にAC～AIは
1行目ブランク
2～10(例)行目数値or文字
11行目=""
12行目■
13～20行目数値or文字
21行目=""
22行目■
23～30行目数値or文字
31行目以下170まで=""
170以降はブランク（関数式なし)
※2行目以下の行は例示

多分有効数字の入っている最終行をAI列で判断して、いわゆる=""の行は削除していると思いました。

補足

下記書き忘れ
削除したい行は11/21/31行以下です。

回答No.1

keithin

表範囲をメモ帳に貼り付けてそのまま保存してるってのは、タブ切りテキストで保存するのでいいって事ですね。

sub macro1()
　dim h as range
　dim myPath as string

　mypath = thisworkbook.path & "\"
　for each h in range("AC1:AI650")
　　if h <> "" then
　　　open mypath & left(h, 10) & ".txt" for output as #1
　　　exit for
　　end if
　next

　with application
　for each h in range("AC1:AC650")
　　if .counta(h.resize(1, 7)) > 0 then
　　print #1, join(.transpose(.transpose(h.resize(1, 7))), vbtab)
　　end if
　next
　end with
　close #1
end sub

ブックをしっかり保存してからマクロを実行します
テキストファイルをブックの横に書き出します。

お礼

さっそくありがとうございました。
テストをしましたが、こちらの質問で勘違いしていたのに気がつきました。
ファイル名はA1000ではなくG1000(固定)でした。(A1000はブランクになってました）
A1000の記述がどこにあるのか見つけられなくて、こちらでなおせず再度ご質問になってしまいすみません。

それと、copy対象の("AC1.AI650")はすべての行が埋まるのではなく行の途中及び後半はブランクが発生(関数でゼロ表示)するため、a1000にCopyしたあと空白削除をしています。
ご指示のマクロですと、この空白行が改行によるたぶんタブブランクが表示されてしまいました。
前にこのゼロ表示による空白行がやはり削除されなくて、その対応方法のマクロをとりこんだような記憶があります。

補足

下記追伸で、空白削除はAC1.AI650のところで直接やってしまうと元データ(A1～G650)をこわしてしまうため、A1000に移してから行っています。