Excel VBA 質問スレ Part50 [無断転載禁止]©2ch.net

2017/08/27(日) 12:40:17.57

!extend:checked:vvvvv:1000:512

スレ立ての際は一行目に
!extend:checked:vvvvv:1000:512
と入れてスレ立てして下さい

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

※関連スレ
VBAなんでも質問スレ Part2
http://mevius.2ch.net/test/read.cgi/tech/1432173164/
Access VBA 質問スレ Part1
http://mevius.2ch.net/test/read.cgi/tech/1328536426/
Excel総合相談所 126
https://mevius.2ch.net/test/read.cgi/bsoft/1496487719/

※前スレ
Excel VBA 質問スレ Part49©2ch.net
http://mevius.2ch.net/test/read.cgi/tech/1498410914/

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

2017/10/24(火) 12:26:01.19

>>754
データを格納するファイルと
そのデータを管理するファイルに分ける

データを管理するファイルでVBAを動かし
データを更新するときは更新中か否か判別する値を持つ

DBとDBMSのような関係

2017/10/24(火) 12:29:03.80

>>756
マクロの記録でオートフィルタの記録をとって見比べる

2017/10/24(火) 12:44:07.31

>>752
>>718
VBA でやるにはハードル高いかも

2017/10/24(火) 13:12:35.37

>>760
C♯とかだとイベント検知できたような気がするけど

2017/10/24(火) 14:18:38.15

c#とか使わなくてもvbsでフォルダ監視するファイル作って実行させとけばいけるんじゃない？
c#かvb.net使っていいならFileSystemWatcher使えば希望通りのことが出来ると思います。

2017/10/24(火) 15:13:31.99

>>762
できるかできないか、じゃなくて、
やり方を具体的に頼む

2017/10/24(火) 15:15:31.49

>>741
いや、だから既にVBAでも出来てるし。
ハードル高いかどうかは組む人のスキル次第でしょ。

2017/10/24(火) 15:50:11.86

>>763
今までのやり取りである程度推測できないならスキルが足りてないだけでしょ

2017/10/24(火) 16:08:30.01

フォルダをウォッチする機能を
Excelに持たせようとすること自体がセンス悪い

2017/10/24(火) 16:13:02.09

Book1　Sheetあ Sheetい Sheetう Sheetえ
Book1 の Sheetうの中にボタンを作って、そのボタンを押すとエクセルの任意のブックを参照して
参照したブックのSheet1の内容をすべてコピーして、Book1の SheetあのA1を起点に張り付けたいです。
ご教授お願いします

2017/10/24(火) 16:15:19.30

永久ループに適当にスリープ咬ませて、シートに列挙したファイル名と付き合わせるくらいかな？
高速かどうかはわからないけど

2017/10/24(火) 16:22:17.63

>>767
任意のブックとは常に決まったブックなのか、
それともその都度変わるのか？
変わる場合は、どうやって指定するのか。

2017/10/24(火) 16:28:26.33

>>769
その都度変わります。
ですのでファイルを参照するウィンドウを開いて指定する方法ができると嬉しいです

2017/10/24(火) 17:46:03.13

それ自体は難しくないと思います。
あとは、コピー元のブックがあるフォルダが決まってるかとかで多少変わってくるかと。
例えば常にカレントフォルダにあるとか、コピー先のブックと同じとか。

2017/10/24(火) 20:30:24.34

>>761
そりゃC#使えるならSystem.IO.FileSystemWatcherクラスを使えばいい
でもここはVBAスレだし
https://msdn.microsoft.com/ja-jp/library/system.io.filesystemwatcher(v=vs.110).aspx

2017/10/24(火) 21:16:13.10

>>763
vbs

myFLD1 = "C:\監視フォルダ"
myFLD2 = "C:\コピー先"
myLimit = 3 '3分間監視する

Msgbox "監視スタート"
myTime = DateAdd("n", myLimit, Now)
myMEM = ""
With CreateObject("Scripting.FileSystemObject")
For Each myF In .GetFolder(myFLD1).Files
If InStr(1, myMEM, myF.Name & vbTab) = 0 Then
myMEM = myMEM & myF.Name & vbTab
End If
Next
Do
For Each myF In .GetFolder(myFLD1).Files
If InStr(1, myMEM, myF.Name & vbTab) = 0 Then
myMEM = myMEM & myF.Name & vbTab
.CopyFile myF.Path, myFLD2 & "\"
End If
Next
WScript.Sleep 500
If myTime < Now Then Exit Do
Loop
End With
Msgbox "END"

2017/10/24(火) 21:20:32.78

>>763
vbsのサンプルなんでコピー処理を自分のやりたい処理に変えてみて下さい。
c#かvbなら上の人がリンク貼ってある先みるといいですよ

2017/10/24(火) 23:00:43.39

タスクランナーのGulp, Grunt, Jenkins とかを使えば？

毎秒フォルダ・ファイルを、watch して、タスクを実行する。
タスクでエラーが起きると、watchを解除する

タスクランナー → アプリ

自分でプログラミングすると、逆になる。
アプリ → タスク

エラー処理も、難しい

2017/10/24(火) 23:04:46.31

タスクスケジューラでいいように思うけど

2017/10/25(水) 02:23:47.05

さっさと諦めればいいのに

2017/10/25(水) 02:31:56.07

盛り上がってるんだから、話題に入れないお前が諦めりゃいいんだよ

2017/10/25(水) 08:15:57.43

>>772
VBAじゃ実用的なものは無理でしょって言う遠回しなお断りなんだが
まだvbsの方が現実的

2017/10/25(水) 16:02:07.00

VBAのステータスバーの表示方法について
課題でステータスバーを表示しなさいとなっているのですが割り算の問題？
なのかうまくひょうじできません。
基本は
Application.StatusBar = String(i, "■") & String(10- i, "□")
上記のコードでいいと思うのですが、
ステータスバーに進捗を表す"■"を表示する。表示する"■"の数は変数iの値を100で割った値とする
この、"■"の数は変数iの値を100で割った値とするの部分がうまくいきません。
やってみたのは下記のコードです
Application.StatusBar = String(Int(i / 100), "■") & String(Int(i / 100), "□")
ちなみに、Forの繰り返しは１から５００です。

2017/10/25(水) 16:21:09.87

ちょっとお知恵を拝借したいと

Excel 2010で作業をしているのですが、
大量にあるシートにある非表示セルをとある事情で全て削除したいのです。

ただし、
・どの行・列が非表示になっているかはシート毎に異なる（むしろ、消したい行・列を非表示にして、マクロで消すことが考えられる）
・場合によってはSpecialcells(xlCellTypeLastCell)で拾えるセルより下／右の行／列が非表示になっている可能性がある
　（使用されているセルはAD250までだが、非表示セルがAF列や300行に設定されていると上手くいかない。
　　または、AA:ADが非表示だとZ列が拾われてしまう。）
・どれくらい下／右まで使用されているかは必ずしも分からない（さすがに、1000行は使わないと思うが、断言できない）
という問題があります。

数十から百程度のシートがあるので、10000行500列のような数を処理するのは時間がかかるため、
できる限りチェックは最小圏で済ませたいのですが、いい方法はありませんでしょうか。

2017/10/25(水) 16:54:42.17

>>781
ブックをzipとして解凍して出てきたxml
(\Book\xl\worksheets\sheet1.xml とか)
の中身みると
<col min="16374" max="16374" width="0" hidden="1" customWidth="1"/>
みたいな感じでどの行、列が非表示なのか書いてあるからそれを拾うとかどうよ

2017/10/25(水) 16:59:56.89

>>780
>Int(i / 100)

をDebug.Printしてみる

2017/10/25(水) 17:08:25.65

>>781
ドキュメント検査の中に非表示の行・列を削除する機能がある

VBAなら
If Columns(ColumnNumber).Hidden = True Then Columns(ColumnNumber).EntireColumn.Delete
If Rows(RowNumber).Hidden = True Then Rows(RowNumber).EntireRow.Delete
をそれぞれ列数・行数分

2017/10/25(水) 17:24:35.66

>>782
xmlにそんな記載があるんですか。
確かにそれなら出来そうですが、それを解釈させるマクロを書くのは面倒くさそうな感じですね
それに、拡張子をzipに変えるとxmlが取り出せるというのを知らない人は多そう
（自分が使うために作るんですが、欲しいと言ってる人にあげる予定なので、難しいことは抜きにしたいかな…）

>>784
実務的にはそれを For … To … Step -1 で回すか>>782で調べたものを使うんでしょうが、
そこが問題なんですよね…

ところで、Columns(ColumnNumber)ってRangeオブジェクトを返すと思うのですが、
EntireColumnが必要なんですか？
いえ、Rows、Columnsあたりはよく分かってない部分があるので、自分の認識不足かも知れませんが。

2017/10/25(水) 17:27:57.53

>>784
（VBAに目が囚われて、非表示検査のことをすっかり忘れてた）
こちらは、あとでいじってみます。
そんな便利機能があったのか…

2017/10/25(水) 17:37:26.49

>>780
良く分からんが■と□の数が同じで良いのか?
どっちもInt(i/100)個表示するようになってるが。
最初の例では□は10から■の個数を引いてたよね。

2017/10/25(水) 17:44:03.77

ブックが大量にあるならドキュメント検査の機能をVBAから起動することもできるみたいよ

Set di = book.DocumentInspectors(3)
di.Fix Status, Result

DocumentInspectorsコレクションの3がHidden Rows and Columnsだってさ

2017/10/25(水) 17:57:35.27

xlCellTypeVisibleで表示されてる領域が取れるね。
で、それ以外が非表示ということで、指定領域以外の領域を返す関数作ればいけると思うのだけど。

2017/10/25(水) 19:49:21.08

>>779
>>773程度ならVBAでも同じようなもんだし、今時vbsでやるくらいならPowerShellなりC#の方がマシ

2017/10/25(水) 19:52:49.91

そりゃライブラリ揃ってるC#でやりゃ簡単なのはわかってる。
VBAでなんとか出来ないか？ってのが醍醐味でしょ

2017/10/25(水) 20:04:16.54

このキモさ只者じゃない

2017/10/25(水) 20:28:12.47

>>790
PowerShellには違和感ありまくり。なんか「覚えよう」と言う気になれない。どうせならC#を統合して欲しかった。

2017/10/25(水) 21:18:11.90

>>791
VBAで苦労したいなら>>718のリンク先をVBAで実装するだけでしょ
いまさら醍醐味とか言われてもね

2017/10/25(水) 21:57:34.79

Excel起動中だけフォルダ監視したい状況ってどういうケース？
それなりの信頼性が必要なら稼働監視も必要になってくる

VBAでやるならわざわざwin32使うより
新旧状態比較のほうがまだいい気がするけどね

2017/10/25(水) 21:59:06.19

>>789

>>713のRangeからRangeを引くメソッドが役立つね

2017/10/25(水) 23:07:42.90

>>790
いちいちエクセル立ち上げなきゃできないvbaなら見た目には見えないvbsの方がマシじゃね
クソかミソかみたいな話だが
まあそもそも俺はC♯等々でやるべきと言う派閥

2017/10/26(木) 02:53:53.87

Excelしかできない人ってかわいそう

2017/10/26(木) 07:01:20.13

バッチ処理ならvbsがいいけど、エラーログやらなんやらって思うと結局エクセルvbaの方が便利すぎて

2017/10/26(木) 07:06:29.21

>>795
> Excel起動中だけフォルダ監視したい状況ってどういうケース？
> それなりの信頼性が必要なら稼働監視も必要になってくる
醍醐味とか言ってる奴に実用性求めてどうするよ w

> VBAでやるならわざわざwin32使うより
> 新旧状態比較のほうがまだいい気がするけどね
理由は？
自分がそれしか理解できないからってこと？

2017/10/26(木) 07:48:52.20

>>788
そんなこと出来るんだ…
検査中はマクロは止まるんですかね…？

>>789
その考え方はなかったですね
これはこれで、やり方を考えてみます

2017/10/26(木) 11:25:46.03

ようはExcelの処理の中で高度なことが必要になったわけで、単独なら他の言語でも良いわけだ。
じゃあ高度だからといって他言語と連携させるのが良いと考えるのか、それともそれはカッコ悪いと考えるのかという話だと思う。
それと、その人にとってどれくらい高度なのかによって変わる。

俺はカッコ悪いと考える派なんでWin32APIを使うね。
別に苦でもない。

ただ、そもそもフォルダ監視が本当に必要なのかは疑問。
初心者の質問は結構、無理を通そうというものが多く、仕様を見直すとそんな機能は必要無かったとなることも多い。

2017/10/26(木) 11:49:24.76

適切な要求仕様を考えるのも、また一つの技術だからね。

2017/10/26(木) 13:02:27.20

初歩的な質問ですが、コードを記入して間違ったときに記入するときに削除をすると
通常の削除ではなく、そのスペースがなくなり上書きのようなものになり
直そうとするとそこから全ての文を打ち込みし直さないといけない状態ですが
通常の文のように削除、追加するようにはどうしたらいいでしょうか？

**デフォルトの名無しさん** (ｽﾌｯ Sd33-PLhk) · 2017/10/26(木) 13:06:20.32

>>804
プログラミングというのはコンピューターに対する処理手順の説明
お前みたいな説明の下手な奴はセンスないからプログラミングやめろ

2017/10/26(木) 13:13:32.91

>>805
雑談スレでもなく、質問スレでそんなにキャンキャン切れるのはプログラミング
向いてないからやめたら？

2017/10/26(木) 13:17:43.11

>>806
どちらにしてもなにいってるかわからないからやり直し

2017/10/26(木) 13:25:12.24

どっかでInsキー押し間違えて入力モードが切り替わってるんじゃないの。

2017/10/26(木) 14:17:19.57

>>804
>>805の言ってることは厳しいし、説明の上手い下手がセンスに直結してるとは思わないけど、初心者の人は説明が下手で回答を遠ざける人が非常に多い。
少しは文章を推敲すべきじゃないかな。
相手のエスパーを期待するってのは結局人にやらせたいってだけと受け取る人もいるだろう。

テキト－に考えてるのか分からないけど、自分の問題となってるコードをそのまま質問者に丸投げしたり、やる気が感じられないんだよね。

2017/10/26(木) 14:17:36.25

質問する側がこんなキチガイみたいな煽りする時点で答える必要はない

2017/10/26(木) 14:36:54.90

こういうおかしな日本語を見ると漠然とした不安を感じる
現象としては>>808だと思うけど

2017/10/26(木) 14:49:54.73

なんつーか改行もおかしい
糖質っぽい

2017/10/26(木) 16:04:18.61

お前らスルー耐性無さすぎw

2017/10/26(木) 16:54:30.02

このスレは自分が答えられない質問をする奴は基本貶す方針で運営されてるからな

2017/10/26(木) 17:12:20.63

>>814
その方針とやらに従うかどうか
決めているのは自分だろ

勝手に答えてやればいい

2017/10/26(木) 18:40:53.23

powershellはおっそいのがね...

2017/10/26(木) 19:44:24.92

>自分の問題となってるコードをそのまま質問者に丸投げしたり、やる気が感じられないんだよね。

まだ良いほうだよ、それ。w
とにかくコードも示さずエラーがエラーがってどんなエラーが出てるのかも示さずな奴も居たからな

2017/10/26(木) 20:47:10.77

>>814
そら答えられんわ
日本語としておかしいんだから

2017/10/26(木) 21:47:23.55

初心者です。
同じフォルダ内の複数のExcelファイルをひとつにまとめたいのですが、方法を教えていただければうれしいです。

2017/10/26(木) 21:53:25.01

>>819
シートに全部まとめて保存します
その後余分なファイルを削除します

2017/10/26(木) 22:11:22.87

>>816
なにやってるのか知らんけどスクリプト言語としては十分な性能だと思うが

2017/10/26(木) 22:14:00.17

>>819
zipフォルダにぶちこめばいいんじゃね？

って言われたくなきゃもっとやりたいことをきちんと書かなきゃダメだわな

2017/10/26(木) 22:23:16.30

>>822
いい返しだな

2017/10/26(木) 23:05:18.78

>>819
エクセルブックをまとめると一口で言っても、シートを一つのブックにまとめたり、データを一つのテーブルにまとめたり、いろんな解釈が成り立ちうる
というか、エクセルブックをまとめるというのは、何らかの作業目的を達成するための手段だろう？
初心者は自分でイメージしている手段の実装方法を質問するんじゃなくて、目的から説明して最適な方法を質問した方が良いぞ

2017/10/26(木) 23:08:11.48

>>821
CUIで動くコマンドとして捉えるとやっぱり遅いし、ワンライナー書くには便利だけどそれ以外のメリットがあまり実感できないんだよな

2017/10/26(木) 23:23:07.52

PSはともかくとして.NETにメリットを感じないなら
そりゃ使う理由ないよね

2017/10/27(金) 00:53:12.33

あのクソめんどくせえバワーシェルでワンライナーとか正気の沙汰とは思えんな

2017/10/27(金) 05:05:02.11

PowerShellの話題はPowerShellスレでやるんだ

2017/10/27(金) 08:53:24.35

ワークシート関数とプログラムの組み合わせで複雑な計算も簡単に書けるというexcelvbaの長所を活用しようよ

2017/10/27(金) 16:38:23.21

( ・ω・)∩ｼﾂﾓｰﾝです

最近ティンコが痒いんですが
どうすれば良いでしょうか
教えてくさい

2017/10/27(金) 16:54:33.12

>>830
風通しをよくするのが重要です
陰毛を剃ってノーパンで過ごして下さい

2017/10/27(金) 18:35:12.89

>>830
無水エタノールでもかけときゃ治るやろ
どうなっても知らんがな

2017/10/27(金) 19:48:16.15

>>830

Do Until ティンコ = 痒くない
Call ティンコ掻く
Loop

2017/10/27(金) 19:50:55.26

男のくせにティンコとかぼかして言うやつw

2017/10/27(金) 20:19:11.74

>>833
途中でオーバーフローして出血しそうなコード

2017/10/27(金) 21:33:06.91

まずは医師の診断を受けろ
最近東南アジア行ったんだろ？

2017/10/27(金) 21:52:19.37

VBAでサジェスト機能？をやりたいのですが、フォームにコンボボックス1を配置してシート1のA列にあるリストを一文字入力したらコンボボックスに候補が出るようにするコードが知りたいです。

欲を言えば、『田』と入力したら『田中』、『田口』とか『田』の候補が出てきて田中を選択して、スペースを入力してまた『鈴』と入力したら『鈴木』や、『鈴鹿』とまた候補出てきて入力出来るように出来ますか？

2017/10/27(金) 22:27:17.28

>>834 黒如意とでも書けばいいのかな

2017/10/27(金) 22:30:52.04

>>837
若干挙動は違うけど
自分がよくやる手法が>>284
Splitあたりで工夫すればスペースで区切ってできるかもね

2017/10/28(土) 00:40:19.22

>>838
大久保さんかよｗお前BBAだろｗ

**デフォルトの名無しさん** (JP 0Hc5-xXVv) · 2017/10/28(土) 09:13:49.16

肉男爵

2017/10/28(土) 09:33:46.66

リモートデスクトップを使用してADサーバへアクセスし
変更するマクロを作成しています。

shellを使用して目的のサーバを起動させることは出来たのですが
そこから開いたサーバを操作するにはどうしたらよいのでしょうか？
何かやろうとしてもローカルPCの方を操作しようとしてしまいサーバの方を操作できません。

曖昧な質問で申し訳ないのですがよろしくお願いします。

2017/10/28(土) 10:03:13.85

>>842
VBAでやる必要がわからない
それこそpowershellとかの方が良いような

それかマウスやキーボードを操作するとか

2017/10/28(土) 10:43:35.88

>>843
ご返答ありがとうございます。
powershellについて調べてみました。
これをサーバに作成しておき、
ローカルPCの方のマクロボタンを押して起動させ、
ADの設定変更をするようなマクロを作りたいと考えています。

起動させたサーバ側を操作する方法があればご教授いただけないでしょうか？

よろしくお願いします。

2017/10/28(土) 10:55:53.23

>>844
ADって何？

2017/10/28(土) 10:56:14.17

>>837
これまた日本語がわかりにくなあ

とりあえず文字検索するだけなら

dim inpted
dim i as long,maxRow as long,list as new collection
'maxRowは最大行自分で求めて
for i = 1 to maxRow
　if .cells(i,1).value like inputed & "*" then
　　list.add .cells(i,j).value
　end if
next i

dim l as varianrt
for each l in list
　combobox.additem l
next l

とかこんな感じでいいんじゃねえの
コンボボックスに入力した値って要素0に入ってるんだっけか？

2017/10/28(土) 10:57:11.27

あ、コレクションに代入してるときのcolumnのｊは間違い
1ね
わかると思うけど

2017/10/28(土) 11:00:53.80

あ、あとinptedが入力済の文字列ね
どっから代入してくりゃいいか質問文からは読み取れないので

2017/10/28(土) 11:43:02.13

コピーのコードどが間違っているんでしょうか？
シート１とシート２にA３がオートフィルの題名でA4からA9にデータがあり
シート１のデータをシート2の最終行に張り付けたいのですがうまくいきません。
下記がコードです。
Sub Q_2_4()
Dim DataLastCell As Range

With Worksheets("Sheet2")
'「Sheet2」ワークシートの表で、A列のデータ入力行のセルを取得し、変数DataLastCellに代入する
Set DataLastCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
End With

'「Sheet1」ワークシートのA4からD9をコピーして「Sheet2」ワークシートに貼り付ける
' Worksheets("Sheet1").Range("A4:D9").Copy(コピーするセルの選択）
'コピーするセルたりないのは貼り付けをするを指定するセル
'
Worksheets("Sheet1").Range("A4:D9").Copy Destination:=DataLastCell
End Sub

下記の部分で止まってしまうのですがどこが間違っているかわかりません。
Worksheets("Sheet1").Range("A4:D9").Copy Destination:=DataLastCell

2017/10/28(土) 12:27:45.96

>>844
サーバー側にAPIをつくっておいて
HTTP-POSTでPowerShellを走らせるコマンドを送りつけるとか

そこまでやるなら、もはやVBAにこだわる必要ないし
セキュリティも知らないけど

2017/10/28(土) 12:33:46.75

>>849
なんというエラーで止まるのでしょう
構文には問題なさそうなので、コードではなく別の要因では

2017/10/28(土) 12:41:37.08

実行時エラー９
インデックスが有効範囲にありません。
とでます。
で、デバックをおすと該当部分が黄色くなります。
シート名は変更していません。

2017/10/28(土) 12:58:58.98

〉〉851
なぜかダウンロードした真っ皿なサンプルコードに移し替えたらできました。
シート名も変更せず、シート数も変えてないのに何かが変わっていたみたいです。

2017/10/28(土) 13:04:08.97

>>844
windowsならosの機能でリモート保守機能があるはず
それをpowershellから使えばいい

2017/10/28(土) 13:18:20.37

>>853
何故かじゃないでしょ。
うごかない環境で
Debug.Print DataLastCell.Address
くらいやっときなよ。

2017/10/28(土) 13:50:45.67

>>846

2文字目打ったら更に絞り込むにはどうしたらいいですか？
listは空にできますか？

2017/10/28(土) 14:12:38.74

>>853
ダウンロードってのがよくわからないけど色んな環境で使い回しているのなら
ExcelのバージョンまたぎでVBAが動かなくなることがある

同じブックを2003→2007→2003とかで使い回すと最後の2003の時だけエラーが出たり