Excel VBA 質問スレ Part64

2019/12/02(月) 23:43:54.18

!extend:checked:vvvvv:1000:512

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

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

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

2020/01/02(木) 12:58:08.65

あと勝手に入ってるのではなく元々のテキストファイルに入ってるんだろう

2020/01/02(木) 15:17:06.96

216です

>>217
csvの中のデータとして配列に入っています。書き出したcsvは普通に数行のカンマ区切りです。
それをjavascriptで配列へ取り込みしたときに、
二次元配列データの一行の最後のデータの末尾に毎行追加されている感じです。

配列内
aaa,ddd,ccc%0D
aaa,ddd,ccc%0D
aaa,ddd,ccc%0D

csvとしての改行・配列構成は崩れていません。
ちなみに書き出しは
ActiveWorkbook.SaveAs Filename:=csvName, FileFormat:=xlCSV
です。win7時代に作ったマクロをwin10で使っていますが、それもあるんでしょうか…

>>219
EXCELの改行仕様は知りませんでした。ありがとうございます。
末尾に列を１つ増やしたら取りたいデータにはつかなくなったので
（おそらく、増やした列に%0Dが移動したのかとｗ）、
若干気持ち悪いですが、趣味プログラムなので運用で対応します。

みなさんありがとうございました。

2020/01/02(木) 16:37:15.04

北海道のグラサンスーパーハゲザーはここにはこないのー？

2020/01/02(木) 17:52:03.38

>>216
Excelのsheetに読み込んでから、CRLF等を""に置換する
ではダメ？

2020/01/02(木) 21:25:07.68

一般論として例えば、Ruby のCSV の規格にも、
行区切り文字・列区切り文字・クォート文字などがある

特に設定しないデフォルトでは、それらは、
改行コード・カンマ・ダブルクォーテーションになる

もし、それらが無ければ、CSVの要件を満たさないw
つまり、CSVの規格ではありませんw

2020/01/02(木) 23:23:58.57

ルビカスが何を言おうと、世の中ではExcelが出力するCSVが標準のCSVだ

2020/01/03(金) 10:32:03.45

CSVは自前でテキストファイル読み込み+自前で区切り扱いが基本。
区切りが何であろうと、データの形式が何であろうと全て自分でコントロール出来るし速度も速い。

2020/01/03(金) 12:29:48.12

同意。てか、Excel標準のCSV読み込み仕様もうちょっとなんとかなりませんかね・・・？

それはさておき>>216＆>>221（もう見てないかもだけど）
その「CSVファイルを配列に取り込むJavaScript」のコードに問題があるんじゃないかなと。
具体的には\n(%0A)でSplitしてるから\rが残ってるだけだと思うけどな。

2020/01/03(金) 18:33:14.85

使い続ける以上CSVとの戦いは続くのだ

2020/01/03(金) 18:50:10.73

モダンExcelが一般化してきた今ならクエリでCSVの開き方を指定して読み込むのがいいんじゃないの
CSVをエクセルで直接開くといろいろ勝手なことをしてくれるので

**デフォルトの名無しさん** (ｽﾌｯ Sd62-F9A1) · 2020/01/03(金) 19:06:41.78

PowerQueryのコードを書くのはVBA以上にハードルが高いぞ

2020/01/03(金) 19:55:22.39

PowerQueryコード書かなきゃいけないの?
GUIでできる範囲だと不十分なの？

2020/01/03(金) 19:58:54.81

そんなにハードル高いか？
テキストファイルの読み込みの処理をスクラッチするより明らかに楽だし処理速度も速いと思うが
例えば4列のCSVをSJIS(Windows-J31)でデコードしてカンマ区切りで読み込んでSheet1のA1セルから書き出すとしたらこんな感じ

Sub ReadCSV()
　　With Worksheets("Sheet1")
　　With .QueryTables.Add(Connection:="TEXT;ソースファイルのパス", Destination:=.Range("A1"))
　　　　.TextFilePlatform = 932
　　　　.TextFileCommaDelimiter = True
　　　　.RefreshStyle = xlOverwriteCells
　　　　.TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat)
　　　　.Refresh
　　　　.Delete
　　End With
　　End With
End Sub

2020/01/03(金) 20:07:37.72

PowerQueryだったらもっと簡単だろう
Csv.Documentにソースのパスとメタ情報を渡してテーブルを取得するだけだしGUIでステップ記録できる

2020/01/03(金) 20:56:20.20

CSV読み込みの話してんのになんで書き込みの回答してんだろうこの子

2020/01/03(金) 21:32:33.80

>>234
>>232のコードについて言ってるなら、読み込んでシートに書き出してるわけで、クエリの使い方としてはオーソドックス処理だぞ
読み込んだ結果をシートに書き出さないならODCにデータモデルとして保存すれば良い

2020/01/03(金) 22:10:30.43

>>235
ODCにデータモデルとして→×
データモデルとして→○

2020/01/03(金) 22:23:49.87

>>230
全然。

基本的にはリボンの機能使った操作が１つずつのステップになっていく。
ステップの１つ１つがマクロみたいなもの。
「詳細クエリー」を見て初めて「コードはこうなってるんだ・・・」
という感じ。

もっとも、M Functionのレファレンスやパラメータあたり説明が
英語でもまだ不親切なので、
使えてない部分が多分にはあるけど。
ただ、VBAのように出来る範囲が広範囲なわけではなく、
取得したデータの成形に特化してる。

スクレイピングについては
Pythonみたいに取得対象のWeb画面で
IDやパスワード入力するとか、
ボタンを押すみたいなことも出来ない模様。

2020/01/03(金) 22:43:05.12

データの成形に特化と言ってもVBAでスクラッチすると恐ろしく面倒なJoinとかのSQLチックな処理は全て実行できるので、データ処理の面ではVBAより手軽で便利

あとAccessDBやSQL ServerやOBDCはもちろん、ファイルシステムとかExchangeサーバとかSharePointリストとか、およそデータベースとして扱えるデータストアの情報は全て取得できる

フォルダ内のファイルの属性情報を取得する処理のためだけにいちいちFileSystemObjectのインスタンスとかを使ってスクラッチする必要がなくなると考えればめちゃくちゃ便利

2020/01/04(土) 15:59:54.32

>>227
みています
ご指摘の通り、\nでspritかけてました！
\nってCR+LFじゃなかったんですね…

この後都合があるのですぐには試せませんが、
その辺確認してみます。
ありがとうございます！

2020/01/04(土) 16:12:12.74

\nはLFじゃなかったか
windowsの改行は\r\nだったような

2020/01/04(土) 17:52:00.86

EditBoxでは\r\nでRichEditBoxでは\nだねWindows

2020/01/04(土) 17:56:48.54

CR+LFの定数の vbCrLf か vbNewLine でいいんじゃね

2020/01/04(土) 18:04:55.94

vbNewLineいいよね

2020/01/04(土) 18:23:56.78

>>239
やっぱり。
>>240-241の人もいうとおり、OSだけじゃなくAPI関数ごとに改行コードの取扱いが違ったりすることもあるし、
テキストエディタに内蔵されてる正規表現なんかでも「\n」でCRLFに対応させてる例もあったりするから、
その辺はトライ＆エラーで覚えていくといいとおもうよ。

2020/01/04(土) 18:40:58.78

北海道のグラサンスーパーハゲザーはここにはこないのー？

2020/01/05(日) 18:31:31.28

いつまで過去の人探してんだよ

2020/01/05(日) 21:01:37.71

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" ()
Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String,
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Expect end of statementとエラーが出ます。
なにがいけないのでしょうか？

2020/01/05(日) 22:30:48.28

>>247
・DLL名（urlmon）の後の()が余計
・1行目と2行目の末尾に「半角スペース」＋「半角アンダースコア」がちゃんとついているか確認
↓こんなかんじ
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

2020/01/05(日) 22:56:42.27

>>248
ありがとうございます。
スペースとアンダーバーが必要だったのですね。

2020/01/06(月) 00:41:12.95

初心者につき基本的な質問かもしれず恐縮なんですが、

①Range("A10:B12").Value = Range("A1:A2").Value
　とすると、A12:B12は#N/Aとなり、A10:A11、B10:B11はA1:A2の値が貼り付けられるのはなぜでしょうか。
②Range("A10").Value = Range("A1:B2").Value
　とすると、A10にA1の値が貼付けられるのはなぜでしょうか。

①については、入力元の行・列範囲が１の場合はそれをひと固まりと認識して、
　出力先の行列範囲＞１の部分も入力元の値を入れようとするため
②については、エクセルの構成上、選択範囲の開始は左上端であり、
　出力先が１セルしかない場合は入力元の範囲の開始セルのみを貼り付けようとするため

などと理由（というか状況でしかない）を言語化してみたのですがいまいち腑に落ちず…
もし仕組みをご存知の方がいらっしゃったら教えていただけませんか…？

2020/01/06(月) 02:06:32.04

>>250
原因はお前の知能障害の頭

2020/01/06(月) 08:01:31.75

Range の使い方ぐらい、検索すれば見つかるのじゃないの？

他人が作った仕様を、推測するのは時間のムダ

それに、推測して当たっているように見えても、
仕様書で確認していない推測では、どのみち仕事では使えない

君のその推測は、確実なんだろうね？
と、単に聞かれても、返答に困るw

たぶん、間違いないと思いますと言っても、
たぶんじゃ困る、仕様書を確認してくれと言われるので、
結局、推測した時間はムダw

2020/01/06(月) 08:04:47.15

>>250
まずは右辺のセル範囲が定数、（複数セル参照）式でどうなるか試してみよう
試す、予想する、検証するのが大切

2020/01/06(月) 08:31:23.15

Power Queryの式がVBAで取得編集出来るの初めて知った
凄い便利だわ

2020/01/06(月) 11:36:21.98

>>254
これとか？

PowerQueryの強力な機能をVBAから利用する方法
https://excel-ubara.com/excelvba4/EXCEL_VBA_408.html

2020/01/06(月) 16:14:57.44

>>240 - >>244
ありがとうございます、お陰様で完全解決しました！
結論としては、>>227 のご指摘が丸ごとそのままでした。
勉強になりました。。。

結局スレチになってしまいましたが、皆様ありがとうございました。

2020/01/06(月) 20:19:04.42

>>255
そうそうこのあたりの機能だな
流石にM言語はエディタ使って書いた方が綺麗に書けるからマクロ記録に頼る理由はないが
あとModelオブジェクトというのも追加されてて、そっちはデータモデル関連を扱える
VBAも本当にモダンExcel向けの機能が充実してきたなぁという感じ

2020/01/06(月) 22:02:59.64

モダンってオワコンWPFの印象が強すぎてちょっと・・・

2020/01/06(月) 22:53:04.03

WPFとSilverlightどっちを習得しようと悩んでた時期が、僕にもありました

2020/01/07(火) 00:47:29.56

>>259
silverlight懐かし～
自PCでサバ公開して遊んだな～
今では怖くてできないw
あのwebなのに何でもできる（未来）感は素晴らしかった

2020/01/07(火) 09:28:54.82

その未来はPythonで実現してます

2020/01/08(水) 23:15:43.07

これどういうこと？
https://forest.watch.impress.co.jp/docs/news/1227931.html

2020/01/08(水) 23:29:57.17

どうでもいいんじゃないの。
そんなのが会社のPCで普通に使えるようになるのは、どうせ何年も先の事だろう。

2020/01/09(木) 00:01:07.52

＞今後数週間以内に企業向けの“Office 365”（E3/E5ライセンス）で利用可能となる。

読めないのか？後数週間でＶＢＡは駆逐されるかもしれんのだぞ？

2020/01/09(木) 00:35:06.13

マクロウィルスの共有機能まであるのか

2020/01/09(木) 00:58:29.64

何ヶ月先もVBAの依頼で詰まってるけど

2020/01/09(木) 01:49:21.73

若い人は知らないだろうけど、昔、リニアモーターカーというのがあってね。

2020/01/09(木) 07:58:16.77

>>264
そのくだりを読んで数週間で駆逐されると思う方がおかしい
IE非対応な時点で察しろ

2020/01/09(木) 09:59:33.76

>>262
VBA終了の合図

2020/01/09(木) 10:11:49.50

> 既存のVBAは比較的ハイスキルな開発者向けである
どこの時空の話だろう

2020/01/09(木) 10:23:13.99

これWeb版の機能だろ。
そもそもExcel95の時代からマクロの記録機能は有るだろ。

2020/01/09(木) 20:22:30.66

↓のマクロを実行するとエラー４３８になって動きません。原因を教えてくださいm(__)m

・ＤＥ３とＤＳ３の値のいずれかの高い数値が１であれば、１１行目～１２行目を削除
・ＤＥ３とＤＳ３の値のいずれか高い数値が２であれば何もしない
・ＤＥ３とＤＳ３の値のいずれか高い数値が３以上であれば、１１行目～１２行目をコピーして
　ＤＥ３とＤＳ３のいずれか高い数値－２回を、１３行目以降に２行づつ貼り付ける

＊ＤＥ３とＤＳ３はいずれも１以上の整数です

Dim i As Integer
Dim cnt As Integer, a As Range

With ActiveSheet
i = .Max(Range("DE3", "DS3"))
Select Case i
Case Is = 1
.Rows("11:12").Delete
Case Is = 2
' なにもしない
Case Is >= 3
.Rows("11:12").Copy
Set a = .Range("A13") ' 貼り付け開始位置
For cnt = 3 To i '３以上のときはその値回数分貼り付けを繰り返す
a.PasteSpecial xlPasteAllMergingConditionalFormats '貼り付ける種類はお好みで
Set a = a.Offset(2, 0) ' 貼り付け先を２行下へ
Next cnt
End Select
End With
End Sub

2020/01/09(木) 20:37:39.44

>>272
i = WorksheetFunction.Max(.Range("DE3", "DS3"))

**272** (ﾜｯﾁｮｲ ffcc-voP+) · 2020/01/09(木) 21:08:00.17

>>273
ありがとうございます

2020/01/09(木) 21:37:55.27

あなたのハゲにサクセスしたい

2020/01/09(木) 23:19:52.51

やっぱり少し騒ぎになってたか
お前らこれから少しは
Javascript勉強しといた方がいいぞ

2020/01/09(木) 23:35:37.48

>>276
JSは癖があるけど、あれはwebの話
既にあるオブジェクトに対してどうこうするならVBAと特に変わらんぞ

adobeのJSは本当にクソだった
※オブジェクトに名前がついてないので、「topプロパティが一番大きいオブジェクトにはxx」などと名付けをしなければならなかった

2020/01/09(木) 23:36:34.25

VBAで無名関数とか即時関数は別にやらんやろ
webのJS使ってる人が参入しやすいようにしてるだけやで

2020/01/10(金) 00:22:51.50

Googleスプレッドシートの
マクロ（GAS≒Javascript拡張）を移植するだけだぞ

2020/01/10(金) 00:52:55.45

TypeScriptベースってあるけど
本家と比べてどこまで厳格なのか分からんが

2020/01/10(金) 07:05:02.06

よほどテクニカルな記述しなければ普通に動くじゃろ

2020/01/10(金) 07:14:14.47

Typescriptはアクセス修飾子をサポートしてるから即時関数でのスコープ管理はしなくていいだろ
無名関数は使いどころあるかもしれないが

2020/01/10(金) 09:48:49.20

大慌てしてて草

2020/01/10(金) 22:41:08.84

wshのjscript使うのとはまったく違うのかな？

2020/01/11(土) 03:59:52.86

TSでも時代遅れなんだけどね

2020/01/11(土) 04:13:01.78

お前らってプログラミング言語の話題になると、何ですぐ時代遅れ論争始めるの？

2020/01/11(土) 06:14:53.73

結局仕事の効率を上げれるかどうかが肝だけど新しい言語を使ってる俺すごいで思考停止しちゃうんだろうなー

2020/01/11(土) 06:26:51.15

TSが時代遅れはともかくVBAはさすがにもう仕事の効率とかそういう次元の話じゃない
クレジットカードの有効期限が迫ってるようなもんだ

2020/01/11(土) 12:18:46.28

Option Explicit
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub TableControl()
Dim ie As InternetExplorer
Dim targetURL As String: targetURL = "http://www.ikachi.org/sample/sample10.html";
Set ie = GetIEObject(targetURL)

If (ie.LocationURL = "") Then
MsgBox "Plz Open the Web page"
Exit Sub
End If
End Sub

Public Function GetIEObject(ByVal targetURL As String) As InternetExplorer
Set GetIEObject = Nothing

Dim sh As Object
Set sh = CreateObject("Shell.Application")

Dim w As Object
For Each w In sh.Windows
If (TypeOf w Is InternetExplorer) And (w.LocationURL = targetURL) Then
Set GetIEObject = w
Exit For
End If
Next
End Function
でie.LocationURL = ""でエラーが出るのですが、booleanだがらでしょうか？

2020/01/11(土) 12:56:17.13

>>289
参照設定してないんちゃう

2020/01/11(土) 12:58:17.36

何にも分かってないバカが多い。
汎用言語は正に競合だらけだからこそ、VBAの優位性が理解出来ない。

VBAの対象となってるのは他言語じゃゴミしか出来ない。
今後、TSで同じようなことが出来るならTSに移って行くだけのこと。

2020/01/11(土) 13:10:09.62

>>289
> ie.LocationURL = ""でエラーが出るのですが
なぜかエラーの内容は極秘なんだよね…

2020/01/11(土) 13:31:11.90

>>289
そもそも、これVBAのコードなの?
なんか、初心者がコピペしたんじゃないの?

個人的に間違っちゃいないけど気になるのは、InternetExplorerで宣言してるのにCreateObject使ってる所。

間違ってると思うのはTypeOf。
VBAでこんな単語使ってるの初めて見た。
構文的にこの部分でエラーにならないのにびっくりした。

どちらにしろGetIEObjectでieが空っぽで帰ってる。

2020/01/11(土) 13:37:40.62

一応言っておくがTypeOf自体が間違いじゃない。
こういう単語を使ったコードもあり得る。
でも自分は見たこと無いし、今回のコードもここが間違ってる。

2020/01/11(土) 14:02:25.84

>>293-294
いや、コード的には間違ってない
>GetIEObjectでieが空っぽで帰ってる
のは間違いないけど、このコードが正しく動く前提として
IEで指定されたURLが開いてる必要があるだけ

まあ指定URLが開いていたら、下のIFがスルーされるから
示された部分だけでは何がしたいコードがよくわからんが

2020/01/11(土) 14:13:44.58

一応Win10の365で動くか試したみた結果ね

つか気になってちょっと調べてみたけど
TypeOf って日本語ドキュメントに記載ないっぽいな
VBAのバージョンとかで使えないかもしれん

なんにせよShellでIE取得して操作とかお勧めしかねるけどな

2020/01/11(土) 16:02:20.96

289です。
>> 292
すみません、エラー内容は「オブジェクト変数または With ブロック変数が設定されていません」です。

>> 293
Excel VBAでクローリング＆スクレイピング（五十嵐貴之著）のP137に書かれているコードなのですが。
余りよくないのでしょうか。

2020/01/11(土) 16:36:52.33

>>297
> すみません、エラー内容は「オブジェクト変数または With ブロック変数が設定されていません」です。
原因は>>295が書いてる通りだと思うのでとりあえずIEでサイト開いてから実行してみて

> 余りよくないのでしょうか。
残念ながらあんまり良くはない
例えば
> If (TypeOf w Is InternetExplorer) And (w.LocationURL = targetURL) Then
とかVBAが短絡評価しない事を知らないみたいだし

2020/01/11(土) 16:57:09.64

北海道のグラサンスーパーハゲザーはここにはこないのー？

2020/01/11(土) 18:06:59.10

ｷﾀ━━━━｡ﾟ+.ヽ(´∀`*)ﾉﾟ+.ﾟ━━━━!

2020/01/12(日) 10:23:37.27

>>298
そもそもShellWindowsオブジェクトはイテレータでIEオブジェクトを渡してるから型判定自体不要

修正版

Sub TableControl()
Const targetURL$ = "http://www.ikachi.org/sample/sample10.html";
Dim ie As InternetExplorer: Set ie = GetIEObject(targetURL)
If ie Is Nothing Then MsgBox "Plz Open the Web page": Exit Sub
'残りのコード
End Sub

Public Function GetIEObject(ByVal targetURL As String) As InternetExplorer
For Each GetIEObject In CreateObject("Shell.Application").Windows
If GetIEObject.LocationURL Like targetURL Then Exit Function
Next
Set GetIEObject = Nothing
End Function

久々に書いたから変な部分あるかもしれないが、これで一応MsgBoxは開く
個人的には、こんなややこしい事せずに普通にVBAで開いた方が確実だと思う

2020/01/13(月) 12:47:26.86

AndAlsoってないんだっけ？

2020/01/13(月) 12:51:11.64

AndAlsoやOrElseはVB.NET
もうVB.NETでもいいからExcelに載せろよとは思う

2020/01/13(月) 15:28:45.56

はいネトウヨ論破ｗｗｗｗ
現実という劇薬を投与されて憤死したなｗｗｗｗｗ

2020/01/13(月) 15:29:12.31

誤爆したわ

2020/01/13(月) 15:35:18.58

イラン「誤爆した」

2020/01/13(月) 18:49:02.06

>>301
ありがとうございます。
メッセージがちゃんと出ました。

2020/01/14(火) 03:53:35.18

>>303
短絡評価ぇ…

2020/01/15(水) 09:32:44.41

基本的な事ですいません。
ActiveWorkbook.SaveAs Filename:=Cells(2, 36) & yyyy & "年" & mm & "月" & dd & "日" & ".xlsm"

セル内の文章と日付でセーブする機能を盛り込んでいます。
実行時ファイル名が変わってエラー無く、VBAが動き続けているのですが、

saveするフォルダ内に該当のエクセルファイルそのものが見当たりません。
見かけ上の問題だけか？と思い、停止してフォルダを検索しましたがエクセルファイルはありませんでした。

ActiveWorkbook.SaveAs Filenameというのは、新しく名前を付けて保存なので、中身はともかく実行されたらエクセルファイルは生成されますよね？

**309** (ﾜｯﾁｮｲ a9f8-otum) · 2020/01/15(水) 09:49:48.07

すいません。自己解決しました。

指定フォルダにsaveする記述がズレていただけでした。

2020/01/15(水) 19:10:48.65

あなたのハゲにサクセスしたい

2020/01/15(水) 21:59:00.70

ｷﾀ━━━━｡ﾟ+.ヽ(´∀`*)ﾉﾟ+.ﾟ━━━━!

2020/01/16(木) 21:35:54.79

Excel2019で、
.ActiveWorkbook.SaveAs FileName:=FilePass, FileFormat:=51

ってやると、

'Book1' の変更内容を保存しますか？
ってダイアログが出て保存できないね。
どうしたらいいだろう？お手上げです。

2020/01/16(木) 21:46:08.50

「はい」押したらいいんでないの

2020/01/16(木) 21:51:01.14

>>314
はいを押しても
'Book1' の変更内容を保存しますか？
って同じダイアログが出続けて保存できません。

2020/01/16(木) 21:56:43.44

じゃあいいえ押せば

2020/01/16(木) 22:25:40.00

>>315
Application.DisplayAlerts = False

2020/01/16(木) 23:46:15.85

そんなの出たっけ?
CSVだとしつこく聞いてきたような記憶があるが。

2020/01/16(木) 23:49:24.36

保存せず閉じる時に出るメッセージかな