Excel VBA 質問スレ Part49©2ch.net

2017/06/26(月) 02:15:14.60

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512

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

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

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

※関連スレ
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/

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

**デフォルトの名無しさん** (JP 0Heb-oL0b) · 2017/08/22(火) 14:17:54.96

>>911
なんのためにIEを制御したいのかわからないけど、データ取得が目的ならIEを制御するという
方法をやめて、直接HTMLを取得して解析する方法にするとか。

2017/08/22(火) 14:32:57.14

変数が日本語で不具合でたことはないな
怪しいのは、変数の方をちゃんと指定してないんじゃないかなってとこ
最初の方でdoubleが入ってるって言ってたよね
～.Columnの参照持ってきちゃってるとかね
longで型指定してみたら

2017/08/22(火) 14:52:26.20

>>912
エクセルから値をコピー→会社のシステム(ie)のテキストボックスにペースト→サーチボタン押す→チェックボックス押す→リストボックスから選ぶ→決定ボタン押す
てのを毎日数百件。
こんなのはスクリプトでも手作業でもなく、エクセルアップロードして終わりとかにして欲しいんだけど。

2017/08/22(火) 15:03:25.63

>>914
ブラウザ側で、jsとか使ってcsv読み込めるようにした方が早そう

**デフォルトの名無しさん** (JP 0Heb-oL0b) · 2017/08/22(火) 15:31:32.03

>>914
RubyやPythonを使えるなら、すぐ作れそうだけどね
頑張って

2017/08/22(火) 19:14:59.94

ブラウザのコンソール使えばサイト変更せずに何でもできるよな
あれすげー好き

2017/08/22(火) 19:32:43.17

>>914 とりあえず>>791のツールを使ってみ。そうすれば自動的にどういうコード書けば良いかわかる。

2017/08/22(火) 19:53:03.47

>>836
とりあえず、>>745と合わせてプログラムを類推して、ツッコミどころを順々に修正しながら8つのパターンで組んでみた。
たぶん、5つ目のサンプルでB2からB101まで取得データを書き込めると思う。
ただし、実際にはTHタグが何個あるかわからないのでIE上でF12キーを押して問題のテーブルを表示させてTHが何個あるかを確認して修正すること。
現在のプログラムはTHが3個、TDの2番目からデータを取得する前提で組んでいる。
(0始まりでTHは0、1、2なのでTDの2番目は全体で4番目)

https://pastebin.com/3sBqwsN9

2017/08/22(火) 19:59:15.08

>>914
クソみたいな作業だなw
ご愁傷様

2017/08/22(火) 20:14:43.78

>>893
コード示してあるけど、変数宣言部分が無く、また変数名から変数の型も類推できない。
これでは変数がどうなっているのかわからない。
変数に日本語使うのは自分ならしないけど、それよりも変数名から型が分からないのはほかの人も困らないか？

特売コード名=""のところと書いてあるが、デバッグ時に黄色になっているのはその次の行？
この行が黄色になっているならその前の行の実行でリスト最終行が変化したことになる。

下に変更してDebug.Print Typename(特売コード名)のところにプレークポイント置いて実行してみるとか。
For i = ヘッダ列数 + 1 To 最終列 Step 7
特売コード = RIファイル.Sheets(1).Cells(ヘッダ行数 - 1, i).Value * 1 '特売コードはlongの13桁です（JANコードです）
特売コード=123
Debug.Print リスト最終行
特売コード名="ABC"
Debug.Print リスト最終行
Debug.Print Typename(リスト最終行)
Debug.Print Typename(特売コード)
Debug.Print Typename(特売コード名)
特売コード名 = "" '何故かここで変数変数「リスト最終行」が「特売コード」の値になります。13桁なのでものすごい量の処理になってしまいます
'以降、他の処理
Next

2017/08/22(火) 20:24:31.59

>>915
>>916
VBA以外やった事ないんだよね。ie操作はエラーになった時の原因や対処がブログとか個人の質問回答とかで情報少なくて決定版みたいなの見つけにくいんだけど、別言語は充実してる？

>>918
ありがとう。でも会社のパソコン外部ネットワークにつながらなくてUSBとかの持ち込みもだめなんだよ。
一応ieの部品に対して行って欲しい処理自体はできてるから、ループ中に止まりにくい方法調べてみます。

2017/08/22(火) 20:35:39.11

>>920
クソ&クソ作業。
皆当たり前のようにやってる。自分もだけど、プログラミング的な知識無いと、そのやり方に疑問を持つのすら難しい。

2017/08/22(火) 20:57:35.78

ExcelからIEを操作せずにバッチスクリプトでcURLやWgetみたいなHTTPクライアントを使ったらいいと思うけど
外部ネットワークにアクセスできなくて追加インストールもできないとなるといろいろ詰んでるね

Powershellが使えるならそれほど苦労せずスクリプト化できるよ
https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Utility/Invoke-WebRequest

2017/08/22(火) 20:59:25.09

>>923
なるほど。
うちの職場も無駄な作業が多いけどみんな疑問をいだかないことに対してなんてアホの集団なんだろうと思ってたけど
「プログラミング的な知識が無いとやり方に疑問を持てない」のが原因だったか。
これで疑問が氷解した。

あと1つこの職場で学んだことだが
せっかく自分が自動化するVBAのツールをつくって何人かにそのツールをあげても「使ってくれない」。
これは便利なのが嫌い」なのではなくて「自分が作ったツールじゃないからなにをしているかがさっぱりわからないから怖くて使えない」からだということだ。

2017/08/22(火) 21:01:25.05

だから完全なるアホの集団ではないのかもしれないが、やっぱりアホの集団にはかわらない。
そんな周りの人たちの心理がわかる自分は天才だということだ。

2017/08/22(火) 21:39:28.52

>>922
クリップボードとかSendkeysとか使って無いだろうな？
特にSendkeysは危険。
絶対使うべきじゃない。
分かって使うなら良いけど。

あと、Domを使ってるならクリック時にsubmitでなくClick使ってる場合は注意。
submitは送受信前提だから直後はIEがBusyになるけど、Clickは送受信前提じゃないから(と俺は思ってるけど別の理由かも)いっぱく置いてBusyになることがある。
つまり、Busyチェック時にはかからず、チェック抜けてからBusyになることがある。
俺の場合はClickの時だけチェック前に300msくらいSleepしておく。

2017/08/22(火) 22:07:32.73

>>927
やっぱスリープ入れるべきかな
なんとなくスマートじゃない感じして抜いても動くように作ろうとしてんだけどうまく行かん

2017/08/22(火) 22:56:13.57

>>928
そのページの作りによる部分は有るからね。
submitでさえSleep入れる必要があるかもしれない。
遷移した後で少し待ってからさらに遷移する場合とかもあるし、どうしてもそのページに合わせる部分は有るでしょう。

2017/08/22(火) 22:57:24.09

>>919
凄く作り方の参考になるし完璧な説明です
ありがとうございます。
<TABLE>
<TR>
<TH>Title1</TH>
<TD>DATA1</TD>
</TR>
<TR>
<TH>Title2</TH>
<TD>DATA2</TD> 取得すべき最初のデータ
</TR>
<TR>
<TH>Title3</TH>
<TD>DATA3</TD> 取得すべき2番目のデータ
</TR>
.
.
.
<TR>
<TH>Title9</TH>
<TD>DATA9</TD>
</TR>
</TABLE>
-------------------------------------------
この構造の場合、下記はどのようになるのでしょうか？
TABLE群 -> colTable(0)
TR群 -> colTable(0).childNodes
TH群 -> colTable(0).childNodes.Item(0).childNodes(0番目～2番目)
TD群 -> colTable(0).childNodes.Item(0).childNodes(3番目～11番目)

2017/08/23(水) 00:05:41.99

>>930
その場合は複数のTRがTABLEから見て子どもでしょ。
だからTRは最初のchildNodes.Item()の括弧の中が2番目からカウントアップ。
で、TDはそれぞれのTR内で常に2番目なのでchildNodes.Item(カウントアップ).childNodes.Item()の括弧の中は2番目。
各番目は0始まりだから2番目ってことは1ってことね。
この場合はcolTRも使わない。(Set colTR =で始まる文がいらない。)

Cells(i, 2).Value = colTable(0).childNodes.Item(1).childNodes.Item(1).innerText
Cells(i, 3).Value = colTable(0).childNodes.Item(2).childNodes.Item(1).InnerText
Cells(i, 4).Value = colTable(0).childNodes.Item(3).childNodes.Item(1).innerText

多分、こんな感じ。

2017/08/23(水) 08:10:00.16

全くの素人です
質問させてください

A1=1の場合タブの色を赤
A1=2の場合タブの色を青
としたくて
A1にはsum関数で数式を組んでるんだが1や2になっても反映されない
手入力で数値を入れるかセルをダブルクリックしてエンターを押すと反映される

何故でしょうか。

2017/08/23(水) 08:11:08.70

>>932
実際に何をやったの？

2017/08/23(水) 08:15:41.26

>>932
イベントが間違ってる
そんなん条件付き書式でよくね？

2017/08/23(水) 08:18:48.53

コードは
private sub worksheet_change（ByVal
target as range）
If target.address〈〉"A1" then exit sub
If Target = "1" then Activesheet.Tab.ColorIndex = 9
If Target = "2" then Activesheet.Tab.ColorIndex = 3
End Sub

初心者ですのでここはこうした方がいいなどアドバイスいただけると幸いです

2017/08/23(水) 08:29:58.52

>>935
エラーになる

2017/08/23(水) 08:33:36.33

>>934
シートのタブ色変更なんですが条件付き書式でできますか？

2017/08/23(水) 08:36:18.74

>>932
どちらでも変わったけど
A1=SUM(1)
A1=SUM(2)

2017/08/23(水) 08:47:09.36

>>925
それはお前が信用されてないからでわw

2017/08/23(水) 08:49:04.48

>>937
条件付き「書式」なんだから、そういう事をするための物だよ

2017/08/23(水) 09:21:19.41

>>935
"1"と"2"の両脇の"外したら動かないか？

2017/08/23(水) 10:06:33.42

>>935
色が変わったあとに1,2以外が入っても戻らない

2017/08/23(水) 10:15:50.03

>>940
ご教示下さい

2017/08/23(水) 10:23:11.79

>>941
動きませんでした

2017/08/23(水) 10:24:44.20

>>942
A1の数字は一定期間ごとに変化するんですがそれには対応できないということになりますか？

2017/08/23(水) 10:47:29.53

>>937
ごめんタブか

2017/08/23(水) 11:13:45.38

>>935
そもそも
Target.addressって絶対参照が返ってくるんじゃなかったっけ？
でもそれなら手入力でも反映されない気がするなぁ

あと、たとえばB1のセルを変更してA1の計算結果が変わったとしても
A1の数式を変更したわけじゃないから
targetにはB1が渡されるだけじゃない？

全部予想ですまん

2017/08/23(水) 12:01:09.88

>>935
Worksheet.Change イベント (Excel)
https://msdn.microsoft.com/ja-jp/library/office/ff839775.aspx
＞セルが再計算時に変更されると、このイベントは発生しません。シートの再計算をトラップするには、Calculate イベントを使用します。

2017/08/23(水) 13:56:51.41

>>948
ありがとうございますどう組んでいいかわからないので
Worksheet_change
calculateを調べてみます

2017/08/23(水) 18:51:26.23

A1セルの数式を変えていいならユーザー定義関数でできる
例えばA1に=Sum(B1:B2)と定義してるなら標準モジュールに
Function XXX(R As Range) As Double
Dim Result As Double
Result = Application.WorksheetFunction.Sum(R)
Select Case R
Case 1: ActiveSheet.Tab.ColorIndex = 9
Case 2: ActiveSheet.Tab.ColorIndex = 3
End Select
XXX = R
End Function
を定義してA1セルを=XXX(B1:B2)に書き換えればいい

**950** (ﾄﾞｺｸﾞﾛ MMb3-x4Vn) · 2017/08/23(水) 18:54:14.54

すまんちょっと間違えてたわ
Function XXX(R As Range) As Double
Dim Result As Double
Result = Application.WorksheetFunction.Sum(R)
Select Case Result
Case 1: ActiveSheet.Tab.ColorIndex = 9
Case 2: ActiveSheet.Tab.ColorIndex = 3
End Select
XXX = Result
End Function

2017/08/24(木) 01:43:32.58

>>935
ちなみに＜＞　を半角にして、"A1" を "$A$1" にしたら変わったぞ

**952** (ﾜｯﾁｮｲ ed6f-2x4P) · 2017/08/24(木) 02:23:07.38

>>935
もしA1に=SUM(B1:B2) ってなっているなら

If Cells(1, 1) = "1" Then ActiveSheet.Tab.ColorIndex = 9
If Cells(1, 1) = "2" Then ActiveSheet.Tab.ColorIndex = 3

これだけでいけた　Target は変更(入力)した場所が入っているから

2017/08/24(木) 05:57:45.02

SUMとしか言ってないので後だしっぽいが
=SUM(Sheet2!B1:B2)は考えなくていいかな

2017/08/24(木) 21:45:58.84

>>925
じゃあ関数ならわかるだろうと思って、
IFだらけのクソ長い関数作ってやったわ。

それもわからんて言われたけど。
当たり前か。

2017/08/24(木) 22:13:24.34

そのツールでやってるのは上司も知ってる？
それでも使わない？
何かあれば責任の所在は明らかなのに

2017/08/24(木) 23:00:01.92

ADOでエクセル上にあるテーブルに接続していますが、
テキストデータを追加したり、更新したりすると先頭にシングルクォテーションが付いてしまいます。
これは仕様だと思いますが、付かないようにする方法ないでしょうか？
後から消すって方法は無しでお願いします。

2017/08/25(金) 01:43:07.95

>>914
> エクセルアップロードして終わりとかにして欲しいんだけど。

システムへの大量レコードのアップロードって、ミスした時が大変なのよ。
アップロードするファイルを間違えたりするやつが必ずいるから。

場合によっては、システムを停めて修正しなければならなくなる。

なので、コピペによる数百件の手入力は言わば安全策。
派遣スタッフが多い職場でありがちだよね。

というわけで、手入力作業をIE制御で自動で行うツールはどうしても必要になるんだけど、エラートラップ（例外処理）をきちんと作り込まないと逆に使いづらくなるので要注意。

2017/08/25(金) 02:02:02.49

>>957
コードは？

2017/08/25(金) 06:46:28.19

>>957
元データに入ってる、というオチではない？

2017/08/25(金) 07:59:18.90

>>958
> アップロードするファイルを間違えたりするやつが必ずいるから。
そんな奴はコピペでも間違えたファイルからコピペするだろ w

2017/08/25(金) 08:16:24.76

大量のデータをエクセルでやるのに無理がある

2017/08/25(金) 08:39:54.31

>>962
エクセルだと無理ってことはアクセス使えってこと？
余計に問題が増えそうだが

2017/08/25(金) 11:34:23.91

>>958
なるほど
レコード数に関係なく一定の手間でできるとなると、一瞬で大量のエラーを発生させるやつが出てくるな

2017/08/25(金) 12:46:59.96

>>956
そういう責任問題はマクロや関数使わなくても同じ話。
というか、コンピューター使ってる時点で駄目だろ。

最後は何やってもお前のコンピューターの使い方が悪いという話になる。

2017/08/25(金) 12:55:04.81

>>958
それはそういうシステム作った奴が悪い。
いくらでも規定のフォーマットじゃなければ受け付けないシステムは作れるだろ。

CSVなんかでもカンマ区切りじゃなくて普通ならどうやってもキーボード入力出来ない文字で区切るなんてやるし。
それで区切ってなければ1行のデータ数不正で受け取らないなんて簡単。
それ以外にもチェック項目入れてEXCEL上で出力編集できるプログラム組めば良いし。

2017/08/25(金) 13:18:40.53

>>966
データの中身が間違っているものをアップロードしてしまうことを言ってるんじゃないだろうか

2017/08/25(金) 14:22:18.70

正しいデータを入れなおして直るならいいんだけど
取り返しのつかないミスで業務に支障がでたら知らん

**デフォルトの名無しさん** (JP 0H0e-2x4P) · 2017/08/25(金) 15:30:41.90

>>958
データのバリデーションが足りてないだけでしょ

2017/08/25(金) 16:19:40.23

バックアップを取ってリカバリしないの？

2017/08/25(金) 18:19:08.43

>>958
ウチの会社にもいましたわ、こんな事やらかす人達。
所詮、人間がやる事ですけど。

2017/08/25(金) 18:24:10.85

>>966
フォーマットの問題じゃないんだよね。

アップ済みのファイルを再度アップしてしまったり、修正前のファイルをアップしたりとか、フォーマットに問題はないのでシステムは受け付けてしまう。

その辺をリカバリできるシステムにしようとするとまた莫大な金がかかるので、上はなかなか動かない。

それによってどのくらいのコスト削減あるいは利益が見込めるのか、まずは数字出さないと話も聞いてもらえないよ。

2017/08/25(金) 22:45:26.71

>>972
？？？
後からでも受け付けるようにするだけだろ。
別に難しくないだろ。
後からアップしたのでデータ上書きすれば良い。
もちろん戻れるように履歴取る必要は有るけど。

2017/08/25(金) 23:17:31.65

>>973
たぶんキーが無いんじゃない？

2017/08/26(土) 07:13:30.51

>>973
働いたことないの？

2017/08/26(土) 07:22:16.86

>>974
一応、ファイルのフォーマットにはユニークなカラムはあるんだけどね。
システム側では、そのカラムはキーになってない。。。と思われ。

さすがにシステムの仕様まではわからない。

2017/08/26(土) 09:32:33.78

>>931
ありがとうございます。
あれから色々試しましたがいくつか問題点があります。

問題１
Dim el As IHTMLElement を削除するとTDが取得出来ず、table丸ごとの取得となります。
問題２
Dim htmlDoc As HTMLDocument　を削除するとエラーが出ます。
問題３
７番目のサンプルを実行するとこの部分がエラーをおこします。
objDOM.body.innerHTML = objXML.responseText

問題１についてtableデータ丸ごとでも取得後必要箇所を抜き出せばいいので用は足せます。
変わらなければそのままいこうと思っていますが相手サーバーへの負担は増えるのでしょうか？

問題２について削除しなければ問題ありませんがxml化時に下記３箇所をどのように記載を変更すればいいでしょうか？
Dim htmlDoc As HTMLDocument
Set htmlDoc = objIE.document
Set colTable = htmlDoc.getElementsByName("oppai")

問題３について下記の("htmlfile")が原因かと思ったのですがこのままでいいでしょうか？
それとも何かのシート名を入れるのでしょうか？
Set objDOM = CreateObject("htmlfile")

毎度すいませんがよろしくお願い致します。

2017/08/26(土) 13:59:12.95

>>958
>システムへの大量レコードのアップロードって、ミスした時が大変なのよ。
>アップロードするファイルを間違えたりするやつが必ずいるから

人間は、必ず間違うから、絶対に回復機能が必要。
普通は、バックアップ・スナップショット機能で、前の状態へ戻れる

銀行では、アルバイト3人が、同じデータを入力する。
3人とも同じ金額の時だけ、合格

バリデ、validation

2017/08/26(土) 14:10:14.30

>>977
JavaScript の、jQuery でやれば？
Groovy, Ruby でも良いし

何で、VBA でやる？
VBAには、クロージャがあるのか？

最初から、ライブラリが揃っている言語を、使えば良いだけ

2017/08/26(土) 14:14:47.08

>>978
＞銀行では、アルバイト3人が、同じデータを入力する。
どこの銀行だよｗｗｗ聞いたことないわｗｗｗ

2017/08/26(土) 15:49:08.64

データを２人で入力してチェックするのは割りと普通
https://www.noc-net.co.jp/blog/2015/06/column_050/
重要な奴なら３重チェックもあるんじゃね

2017/08/26(土) 16:55:31.30

>>972
>アップ済みのファイルを再度アップしてしまったり、修正前のファイルをアップしたりとか、

間違ったと気づいた時点でオペレータ自身が元に戻せるような仕組みを作るのはそれほど難しくないよ
手動でExcelを修正するステップやその前のステップを含めてシステム化するのが理想ではあるだろうけどね

ただ100~300万くらいのお金を莫大な金と言ってるんであれば自力で頑張るしかない
担当が変われば元の木阿弥だし、本来機械にやらせるべき仕事を人間がやってることによる逸失利益を加味したら安い買い物

2017/08/26(土) 17:26:45.76

>>982
要件も確認しないうちから
> 間違ったと気づいた時点でオペレータ自身が元に戻せるような仕組みを作るのはそれほど難しくないよ
とか言うアホ営業 w

2017/08/26(土) 17:45:46.64

在庫と直結するようなシステムだと現物が関わるからタイミングによっては取り戻すの大変だしね。

2017/08/26(土) 17:49:54.05

>>983
ははは、言いたいことはわかるけどそれは客が要件をすべて決めると思ってるやつの発想
費用に応じて提供出来る内容を変えてあげればいいだけ
そういう調整が出来ない人ばっかりだと苦労するよね

まあそこは論点じゃなく莫大な費用がかかると思ってるのは解決方法の選択肢が見えてないだけで
思ってるほどの費用がかからない方法も有るよってことよ
VBAのかわりにスクリプト書くだけなら100万もいらんし

2017/08/26(土) 17:55:27.18

>>984
それは今でも発生しうる業務フローの問題でしょ？
誰かが確認するっていうステップが必要なだけで1件1件コピペ入力しなきゃいけないのとは別問題
確認が必要ならそのフローをシステムに取り込んであげればいい

機械で自動確認できるほうが楽だけどそれは必須じゃないじゃん

2017/08/26(土) 17:56:27.22

Excel関係ない気がするからこの辺にしとくわ

2017/08/26(土) 18:39:24.64

>>985
> まあそこは論点じゃなく莫大な費用がかかると思ってるのは解決方法の選択肢が見えてないだけで
だから要件も見えてないのに選択肢が見えるわけないだろ w
>>984の件も数件なら後追いで修正できるけど１万件とかになると破綻するとかあるだろうし

2017/08/26(土) 19:30:25.78

株かなにかで金額間違えてニュースになったな

2017/08/26(土) 19:42:35.36

あれはトレーダーによる入力だからそれ自体が一次情報
複数人による入力対象ではない

2017/08/26(土) 21:03:15.65

いつから問題の対象が
複数人による入力
に限定されるようになったんだ

2017/08/26(土) 22:10:58.71

さあ

2017/08/27(日) 02:42:06.83

>>914が
> こんなのはスクリプトでも手作業でもなく、エクセルアップロードして終わりとかにして欲しいんだけど。
と書いた時から、サーバ側も巻き込んだシステム全体の話になってしまって
このスレの範疇を越えるものになったんだろうな
それは多分「単純作業をVBAでなんとかしたい」と思ってただけの>>914にとって
「そんなことを議論されても俺にはどうしようもない・・・」と思ってると思う

2017/08/27(日) 09:42:07.86

ほ

2017/08/27(日) 10:41:40.78

>>993
そもそもは>>958が手入力を安全策とか言い出したことからだろ。

手入力なんて全く安全策じゃないし、コンピューターを使う以上、安全性を追求しすぎると本末転倒になるという話。

2017/08/27(日) 12:36:10.64

銀行でバイトに入力させるとかないだろ

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

>>996
あるよ。金融業だけど、新規口座の個人情報入力は子会社の派遣君がやってた。
チェックは正社員がやる。

振込処理なんかはもちろん、絶対にないｗ

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

2017/08/27(日) 13:14:20.45

>>998
乙

2017/08/27(日) 13:34:20.02

ぬるぽ

**1001** · Over 1000

このスレッドは１０００を超えました。
もう書けないので、新しいスレッドを立ててくださいです。。。
life time: 62日 11時間 19分 6秒