Excel VBA 質問スレ Part65

2020/03/17(火) 16:27:20.88

ExcelのVBAに関する質問スレです
コード書き込みや作成依頼もOK

※前スレ
Excel VBA 質問スレ Part64
https://mevius.5ch.net/test/read.cgi/tech/1575297834/

VIPQ2_EXTDAT: checked:vvvvv:1000:512:: EXT was configured

2020/04/04(土) 10:15:36.71

自動更新の間隔を短くするのが無理なら手動でできないのかな？
強制的に切断と接続を繰り返してみたらどうだろう

2020/04/04(土) 13:14:06.10

>>173
やっぱり、逆だよね

2020/04/04(土) 18:10:09.51

>>172
ローカルのつもりだったんだけどそれなら共同編集になるのかな？

2020/04/05(日) 01:47:50.77

セルA1とB1の数値が一致しない場合はC1を塗りつぶす、というのはできるのでしょうか？

2020/04/05(日) 01:56:11.77

>>177
If A1 <> B1 Then [C1].Interior.Color = RGB(1, 234, 56)

2020/04/05(日) 07:39:22.81

>>178
ありがとうございます！

2020/04/05(日) 11:37:21.47

A行の値を見て、その値が1だったら、空白行を上に2つ入れたいのですが、できますか。

2020/04/05(日) 11:44:06.84

間違えた。

A列の値を見て、その値が1だったら、空白行を上に2つ入れたいのですが、できますか。

2020/04/05(日) 11:59:20.85

できます

2020/04/05(日) 12:00:45.64

>>180
Sub Macro1()
　　For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
　　　　If Cells(r, 1) = 1 Then Rows(r).Resize(2).Insert
　　Next
End Sub

2020/04/05(日) 12:32:03.32

>>183
ありがとうございます！できました。

2020/04/06(月) 00:22:26.25

ほんまこの程度自分で考えないのかなあ

2020/04/06(月) 01:48:40.71

子が吐き出したcsvデータ(子の状況により1ファイル数行～数百行程度のデータ00000000.csv～FFFFFFFF.csv)を親で吸い上げて管理用に整理するマクロを作成しているのですが子のデータ数が多くなったせいか親の処理を実行中にExcelが落ちるようになりました。
現状では
Workbook.Open 00000001.csv
でファイルを開いてから配列に叩き込んで処理しているのですが、
他に何か開かずに直接配列に収納するとか別の開き方など良い手段はありますでしょうか。

2020/04/06(月) 02:53:22.77

>>186
その説明では落ちる原因がわからんので対処のしようがない
とりあえずテキストファイルとして開いてSplitで配列に入れるとか

2020/04/06(月) 07:33:50.36

>>174
自動更新しても更新されない
強制的にはできないね

2020/04/06(月) 09:41:20.32

>>174
最初、自前タイマーでの自動保存を提案しようと思ったけど、全ユーザーの同期とか排他処理とか必要になるからやめた
同時使用数にもよるけど、現状の5分ぐらいが実用限度だと思う
それでも問題があるから、MSも共有ブック機能を非推奨にしたんだろうしね

2020/04/06(月) 15:09:00.57

>>186
まず落ちる原因の特定が必要
本当にデータ量が原因かどうか？

それでもし量の問題だとしたら
同時に必要なデータを見定める

たとえばA、B、Cのファイルを同時に開いてたが
じつはAとB、BとCで処理する工夫の余地があるとか

それでもアルゴリズムではどうにもならない量なら
SQLとかのデータベースに移行する

2020/04/06(月) 22:35:08.05

https://oshiete.goo.ne.jp/qa/1972570.html
ActiveWorkbook.SaveAsにするとファイル名変えずに保存できる理由を教えてください
Copyした時点で別ブックが作られるんですか？

2020/04/06(月) 23:45:47.95

マクロで作られた印刷ボタンを押すと両面印刷で出てくるからこれを片面印刷に統一したいんだけどどうしたらいいの？

2020/04/07(火) 00:23:26.13

>>192
マクロを書き換える
印刷方法の変更はプリンターごとに違ってて、Excelのマクロの中でも一番面倒な部分だから、会社でわかってる人に聞くのが早い

2020/04/07(火) 00:29:05.68

プリンタ登録を2つに増やして使い分けるのが簡単。

でもWin95のころはコピーするだけでプリンタ増やせたけど年々難しくなるよね

2020/04/07(火) 00:35:40.64

正直印刷は、機器変更、NW、印刷位置、紙質とかでトラブルが非常に多くなるから、
金、人命とか絶対にVBAで自動化しなければ行けない理由がない限り手動にするようにしたわ
印刷部分だけPDFにして置いておくとか

2020/04/07(火) 01:53:49.62

マクロは印刷プロパティを出すところまで。
それ以上はトラブルの原因になるからやめとき

2020/04/07(火) 04:36:48.84

そもそも紙にこだわるのが非効率で
電子化するのが本来の理想ではある

2020/04/07(火) 07:13:41.88

プリンタのプロパティでプリンタの設定自体を片面印刷にしておくと片面印刷になりますか？

2020/04/07(火) 08:07:09.26

そのくらい自分でやって試せよ

2020/04/07(火) 08:07:32.68

>>186
CSVをWorkbook.Openで開くのが駄目。
OpenステートメントとかFileSystemObject使う方が良い。

2020/04/07(火) 09:12:43.26

>>197
そんなの職場や利用目的によりけりだから

2020/04/07(火) 12:34:06.90

コントロールパネルからプリンタのプロパティで片面印刷に設定したんだけど片面印刷オンリーになりません
どうしたらいいですか？

2020/04/07(火) 12:42:21.74

やったこと全部書き出してプリンターメーカーに聞け

2020/04/07(火) 15:41:03.55

特定の文字列のみ文字色を変えたいんだけど、
置換を使うと指定した文字列を含むセルに入力されている全ての文字の色が変わってしまう…

現状では数百数千セルある中から文字列を選択して文字色変更を繰り返す、という辛い作業を続けているのですが
この様に、指定した文字列だけの色を変更って出来ませんか？

2020/04/07(火) 17:49:52.17

>>204
Sub 特定文字列に色をぬる(検索範囲 As Range, 対象文字列 As String, 色 As Long)
Dim r As Range
For Each r In 検索範囲
Dim s As Long
s = InStr(r.Characters.Text, 対象文字列)
If s > 0 Then
r.Characters(s, Len(対象文字列)).Font.Color = 色
End If
Next
End Sub

2020/04/07(火) 19:26:33.02

>>205
何か少し違う気がする。
rの中で対象文字列が1回しか変わらないんじゃね?

s=instr(s+1,r.Value,対象文字列)でsが0になるまでループかな?

2020/04/07(火) 19:29:30.91

何から覚えようVBA

2020/04/07(火) 19:42:05.21

>>198
やってみてどうなった？

2020/04/07(火) 20:04:27.93

>>207
覚えるのが目的じゃなかろう

2020/04/07(火) 20:22:06.26

>>187,190,200
186です。ご回答有難う御座います。
ご指摘を参考に以下の様な形に変更したところ、強制終了する事なく全ファイルの吸い上げが出来ました。お手数をお掛け致しました。
'// ファイル数カウント //
Dim CSVPth As String, CSVNam As String
CSVPth = Dir(CSVDir & "*.csv")
CSVNam = CSVPth
Do While CSVPth <> ""
CSVPth = Dir()
CSVNam = CSVNam & CSVPth
Loop
Dim CSVAry As Variant, CSVCnt As Long
CSVAry = Split(CSVNam, ".csv")
Dim OutAry() As Variant
ReDim OutAry(1 To (UBound(CSVAry) + 1) * 6, 1 To 1)
Dim LinAry As Variant, LinStg As String, LinCnt As Long
'// CSVファイル読込処理 //
For CSVCnt = LBound(CSVAry) To UBound(CSVAry)
If CSVAry(CSVCnt) <> "" And Dir(CSVDir & CSVAry(CSVCnt) & ".csv") <> "" Then
Open CSVDir & CSVAry(CSVCnt) & ".csv" For Input As #CSVCnt + 1
LinCnt = 0
Do While Not EOF(CSVCnt + 1)
Line Input #CSVCnt + 1, LinStg
LinCnt = LinCnt + 1
LinStg = "" & Replace(LinStg, """", "") & ""
LinAry = Split(LinStg, ",")
<-- 処理 -->
ReDim Preserve OutAry(1 To (UBound(CSVAry) + 1) * 6, 1 To WorksheetFunction.Max(UBound(OutAry, 2), LinCnt)
Loop
Close #CSVCnt + 1
End If
Next Range(Sheet2.Range("A1"), Sheet2.Cells(UBound(OutAry, 2), UBound(OutAry, 1))).Value = WorksheetFunction.Transpose(OutAry)

2020/04/07(火) 21:04:34.54

Windows10になってから、アプリごとに印刷設定を覚えるようになってややこしくなった
以前はプリンタのプロパティからデフォルト状態を決め打ちできたのに

2020/04/07(火) 22:46:04.39

>>206
その辺は仕様がはっきりしないから何とも言えんし
そもそも丸投げを受けたつもりはない

s+1だとaaaaaにたいしてaa指定したら5文字全部色変わるけどそれが良いのかどうかわからんだろ

2020/04/08(水) 03:24:16.94

4文字目から3文字赤に変えたマクロの記録
ActiveCell.Characters(Start:=4, Length:=3).Font.ColorIndex = 3

2020/04/08(水) 19:41:01.55

まさかシート毎に印刷の画面から片面印刷を選ばなきゃ両面印刷をされるなんて知らなかったわ

2020/04/08(水) 19:55:07.25

プリンター関係の情報はシートごとになってるから。
手動ですべてのシートを両面印刷にする方法はあるけど。

2020/04/08(水) 20:05:36.79

シート全選択してプリンタ設定変更してからやればいい

2020/04/08(水) 20:16:40.48

シート全選択してプリンター設定してもダメなんですよね。

2020/04/08(水) 20:32:27.07

あきらめろん

2020/04/08(水) 20:37:06.75

>>217
上書き保存してファイルを開き直せ

2020/04/08(水) 22:17:54.74

dwgファイルをどうにかしてEXCELに図形で取り込みたいんだがなんか方法ありません？
家の見取り図みたいなファイルなんだけど。

2020/04/08(水) 23:34:26.41

>>220
DXFに変換

2020/04/09(木) 03:10:49.06

>>220
後輩にやらせる

2020/04/09(木) 04:58:57.51

>>220
JacConvert

2020/04/09(木) 07:53:04.17

セル右クリックしたら出てくるメニューを禁止に出来ない？
書式設定とかコピーとか貼り付けとかメニュー自体出ないように

2020/04/09(木) 07:57:33.92

>>224
サブクラスで出来るかどうか。
ここには答えられる程の上級者が居ないと思う。

2020/04/09(木) 08:14:30.62

保護かければ？

2020/04/09(木) 08:18:29.24

>>226
保護かけると使えなくなる機能があるから無理なんだ

2020/04/09(木) 08:39:31.78

これは？普通にググったら出てきたぞ
https://www.moug.net/tech/exvba/0150035.html

2020/04/09(木) 09:20:01.56

普通にやり方あるぞ

2020/04/09(木) 12:24:02.59

エクセル2007についてです

セルをクリックすると、カレンダーが表示されて、そのカレンダーの日付をクリックすると、セルに日付が挿入される

という形にしたいのですが、ここで問題があります

一つはAccessがないため、カレンダーコントロールがないのと、もう一つはインターネット接続不可端末のため、どこからかDLするというのができないです

この場合のカレンダーから日付取得をするというのはできないのでしょうか？

2020/04/09(木) 12:30:31.66

>>230
ボタンを横7列、縦6つ並べたカレンダーコントロール的なフォームを自作すればいい
https://ateitexe.com/excel-vba-calendar-control/

2020/04/09(木) 12:31:40.44

カレンダーのシートを作って、そこから選択させるとか

2020/04/09(木) 13:01:14.59

>>231
早速参考にさせていただきました
ありがとうございます

ただ、これ起動すると、日付クリックした後、テキストボックスに日付挿入になるので
どこかのコードにテキストボックスではなくてセル挿入になるようにしたらいいと思うんですけれど、どこに記述するのがいいですかね？

まだvbaいじり始めた身としてはここまで複雑だといじりづらいというか

2020/04/09(木) 20:27:14.43

>>224
BeforeRightClickイベント

2020/04/09(木) 21:58:32.74

>>221
調べるとDXFはTextファイル形式で出来ているみたいでネットで調べながらやってみたけど、重い。まぁ俺の書き方が駄目な気もするけど。線の数が4万とか超えてる。

2020/04/09(木) 23:05:01.47

作者不明のファイルを修正しているのですが、そのファイル自身を指定するのにファイル名で
記述してありました。これだと「●●Ver1.0.xlsm」→「●●Ver1.1.xlsm」のようにファイル名を
変える度にコードを修正しなくてはならないためThisWorkBookに変えようかと思っているのですが
その場合なにか弊害とかあるんでしょうか？

2020/04/10(金) 07:51:15.22

シートを全選択したら一括で変更できるのか

2020/04/10(金) 08:26:28.75

>>236
弊害は無いよ

2020/04/10(金) 09:37:44.73

そのファイル自身だと完全に明確で揺らがないなら、thisworkbookでいんじゃね

2020/04/10(金) 19:23:44.82

どこから参照されてるのか全部調べないとわからないんじゃないかそれ。
まあThisworkbookを知らないだけの可能性は高いが。

2020/04/10(金) 19:27:26.52

>>238,239
ありがとうございます。

今日内容を確認してきましたが、マクロが記述されたブック（A.xlsm）上でシートを作成し、それをコピーして
新規ブック(B.xlsx)を作成してからもう一度A.xlsmの特定のシートをアクティブにする、という処理の中で
A.xlsmのブック名を明示してありました。

今日は時間がなくて試せなかったのですが、この場合でもマクロが記述されたブックはA.xlsmなので
ThisWorkBookで問題ないかなと思ったのですが、それであっていますでしょうか？

2020/04/10(金) 19:38:53.80

>>240
ThisWorkBookを知らないというかちゃんと理解していないのはその通りだと思います。
ThisWorkBookの主体がが変わる場合があるということでしょうか？
マクロ実行中にほかのブックに記述されたマクロを利用したりすると変わる、とかですかね？

2020/04/10(金) 20:30:11.90

ThisWorkBookはプログラムが記述されているワークブックを参照せよっていう意味だからブックAを実行中にアクティブがブックBに切り替わったりしてもブックAに記述されたThisWorkBookはブックAを参照する

2020/04/11(土) 03:52:15.33

エクセルテンプレートを開いた状態
↓
フォルダにある1つ目のtxtデータをエクセルテンプレにドラッグしてエクセル化
↓
エクセル化したC1:C8の範囲をコピー
↓
元々開いてあるエクセルのA1:A8に「数値」のみ貼り付け
↓
エクセル化されたデータは閉じて2つ目のtxtをドラッグしてエクセル化
↓
C1:C8の範囲をコピーしてテンプレのB1:B8に「数値」のみコピー

って感じでテンプレに測定機から出したデータをひたすら貼ってるんですが
これを簡単にするVBAとか可能ですか？

人によってはテキストをエクセル化はまとめて5つドラッグしたりして順番に貼ってく人もいます

エクセル化したデータの数値のみコピーしたいセルはC1:C8は固定

テンプレにはABCDと順番に貼って増やしていく

これをなんとかマクロかする案があればご教授お願いします。

2020/04/11(土) 07:25:04.87

可能です。

2020/04/11(土) 08:08:50.84

テンプレート(.xlt .xltx)は何に使うの？

2020/04/11(土) 10:50:20.33

>>245
その一言でも希望があってありがたい
どういう流れで作ったらいいか何か構想のヒントなどあればいただきたいです

・テンプレのエクセルにボタン設置
・テンプレ開くと同時にユーザーフォーム開くようにしてテキスト選択できるようにするのか
・そもそもVBAがまだ人間になれてない猿並のスキル

>>246
テンプレは.xlsxです、普通のエクセルの

2020/04/11(土) 10:56:04.79

>>244
txtデータは、例えばカンマで区切られてるとか、空白で区切られてるとか、決まってるの？
内容的にはVBAがある程度使える人なら難しくないと思うけど、ちょっとネットで聞いて作ろうとかいうのは難しいんじゃないかな。

**248** (ｱｳｱｳｴｰ Sa3a-d4/r) · 2020/04/11(土) 11:19:08.65

txtファイルがどのくらいの数あるのかで、どういう手順が良いか違ってくると思います。

そんなにたくさんないなら、テンプレファイル（◯◯.xlsm ）に、ボタンを用意。ボタンを押したらファイルを選択するダイアログが出て、該当のtxtファイルを選択してデータを読み込む。

txtファイルがたくさんあるとこの手順は面倒なので、同一フォルダに入れてなんらかの順番で読み込むようにするとか。

2020/04/11(土) 11:27:54.59

>>248
そこら辺の知識がなくてとにかくテキストを、開いてるエクセルにドラッグすると決まったセル範囲に例えばC1:C5に欲しいデータが出るのでそれをコピーしてるのが現状です

イメージ的にいつも

https://i.imgur.com/50VhJqS.jpg
テキスト入ったフォルダとエクセルを並べる
↓
https://i.imgur.com/8TiTDap.jpg　
ドラッグしてエクセル化
↓
https://i.imgur.com/OrMUP1G.jpg
数値のみコピーしてテンプレに貼り付け
↓
https://i.imgur.com/htHvRLv.jpg
閉じた後、次のテキストをドラッグしてエクセル化
↓
https://i.imgur.com/1F89knY.jpg
テンプレの次の列に貼り付け

これを日によっては70くらいやったりしてます、、

2020/04/11(土) 11:31:26.34

>>249
レスありがとです！！！そこです！
日によってデータ数が違って
5つの日もあれば30の日もあれば75の日もあります
ボタン作って該当するデータの数を任意で選べたりできないのかなぁと妄想してます

2020/04/11(土) 11:41:57.94

この貼り付けるエクセルはそもそもこんなのでテンプレなの？っていうは一応エクセルの関数で
他の何種類もあるシートに複数の関数で出せる紐付けしてあるので
テンプレというか自分なりのベースのようなものです

本当はその関数すら慣れてる人ならVBAで余裕で組むんだろうなと思うのですがそんな欲ばる資格がまだないのでまずはこの作業を…

2020/04/11(土) 11:45:20.84

GetOpenFilenameメソッドでダイアログで複数のtxtファイルを選択するか、「転記前フォルダ」とかに対象のtxtファイル入れておいて、フォルダ内をfor eachですべて読み込むか。読み込み済みのtxtは自動で「転記済フォルダ」に入れるか削除。
どちらにしてもtxtファイルの名前順に読み込むようになるだろうから、そこを一工夫する必要があるかも。

2020/04/11(土) 12:37:41.59

>>252
とりあえず、フォルダ内の全テキストを一気に読み込むやつを作った
専用のフォルダに必要なデータだけ入れて、フォルダ名を2行目に書いて

Sub Macro1()
　　ChDir "c:\tmp"
　　f = Dir("*.txt")
　　c = 1
　　Do While f <> ""
　　　　Open f For Input As #1
　　　　For r = 1 To 6
　　　　　　Line Input #1, s
　　　　　　Cells(r, c) = Split(s, vbTab)(2)
　　　　Next
　　　　Close #1
　　　　f = Dir
　　　　c = c + 1
　　Loop
End Sub

2020/04/11(土) 12:48:46.57

>>247
マクロ記録してそれを加工すればいい

2020/04/11(土) 12:55:48.43

>>254
うおおおお
マジでありがとうございます！
いま外出中なので帰ったら速攻でVBE開いて取りかかります！！
他のこんな猿にアドバイスくれる人達に感謝のみ
批判されて当然のスキルしかないのでそういう覚悟もして相談させてもらってます

6月くらいまでには形にして自分もある程度
コードのルールや流れを猛勉強します

>>255
それも頑張ります、まず加工する知能を身につけます

2020/04/11(土) 18:21:57.90

個人的にはCHDIRと#1ってのが
気になった

2020/04/11(土) 20:11:07.53

恋だな

2020/04/11(土) 20:18:04.56

6月て
こんなんIT系の仕事じゃなくても普通に１日で理解するやろ。。

多分セルごとに書き込むからファイル数とデータによっては遅くなるな

2020/04/11(土) 21:02:43.27

クリップボードに整形したデータ入れてペーストでもいいのよ
タブで右のセル
改行で次の行

2020/04/12(日) 05:51:52.52

VBAについて詳しい方に質問です。
現在VBAでオートフィルタの自動プログラムを組んでいます。
そこで質問があります。以下の様なデータがあったとします。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
私の名前は、太郎です。
私の名前は、岩崎です。
あなたの名前は、太郎です。
あなたの名前は、岩崎です。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
検索ワードとしてスペース区切りで「あなた太郎」と入力された場合
「あなた」と「太郎」を同時に含む行のみをフィルタリングするには
どういう記述を Criteriaにすれば良いのでしょうか？

Criteria1:="*" & [検索ワード] & "*"

現在こんな感じで記述してますがうまく動きません。
AND条件、そしてOR条件で指定する方法を教えてくださいｍ（＿）ｍ

2020/04/12(日) 08:05:06.29

>>261
それやるならオートフィルタじゃない。
アドバンスフィルタ。

2020/04/12(日) 11:41:12.47

>>261
シート上でオートフィルタかける時にできること以外のことはできない

2020/04/12(日) 11:45:08.44

どうしてもその記述でやりたいならワードをスペースで分割してから条件2つ指定する

2020/04/12(日) 13:12:18.32

ありがとうございます。
アドバンスフィルタとやらを調べてみます。

2020/04/12(日) 13:17:20.10

>>261
And条件の場合はこう書く
Criteria1:="=*あなた*", Operator:=xlAnd, Criteria2:="=*太郎*"

2020/04/12(日) 13:39:43.30

>>266
ありがとうございます！！

2020/04/12(日) 17:59:57.23

いえいえ

2020/04/13(月) 01:44:02.35

>>254
これを、やってみたけどcellsのところでエラーがでます、うーん
頑張ります

2020/04/13(月) 07:29:10.31

>>269
ヒント　変数 r

2020/04/13(月) 08:13:39.61

>ヒント
ウザっ

2020/04/13(月) 11:51:14.39

>>269
それはたぶん、txtファイルの中身が想定外
例えば5行以下しかないか、C列にデータが入ってないか

**270** (ﾜﾝﾄﾝｷﾝ MM92-791I) · 2020/04/13(月) 12:54:16.85

>>269
すまん。変数 r はforで回ってた。