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

2019/12/18(水) 20:40:24.52

Officeクリップボードをクリアするにはどうしたらよいですか?

2019/12/18(水) 21:42:34.31

>>161
ちゅまん

2019/12/18(水) 22:44:40.50

>>171
かわいい

2019/12/18(水) 23:33:23.01

>>170
その文言でぐぐれ

2019/12/19(木) 17:59:43.75

ちょっと教えて下さい

自動的に新しいcsvファイルが保存されてくるフォルダがあって
新しいファイルが保存されたら処理をするってコードを作りたいんですが
どんな感じにしたらよいのかアイデアが浮かびません

イメージは処理開始としてから処理停止とするまでずっと監視と処理をし続ける感じです
正攻法ってどんな感じでしょう？

2019/12/19(木) 18:07:29.48

定期的にフォルダの更新日時かファイル数を比較

2019/12/19(木) 19:15:47.42

C#に乗り換えてFileSystemWatcherかな
そういうのはサービスにして常時起動したいだろ？どのみちVBAじゃ無理がある

2019/12/19(木) 20:13:02.64

>>174
csvデータは一般機能のpower queryで「フォルダ指定」で取得してから、何らかの加工をする必要があればVBAでやるというのはどうでしょう？

2019/12/19(木) 20:52:54.37

>>174
Application.OnTimeでググればいろいろ出てくる

Excel/VBA: 特定のフォルダ内のファイルの更新状況を監視するマクロ
http://pineplanter.moo.jp/non-it-salaryman/2016/12/30/excel-vba-folder-monitor/

**174** (ﾜｯﾁｮｲ f71f-M3rQ) · 2019/12/19(木) 21:50:34.66

コメントありがとうございます。
C#は知識がなくて・・・。1か月後に必要なんですが今からいけるかな。。。

>>177
すこし理解に時間がかかっています。内容確認してみます。

>>178
参考例ありがとうございます。
更新したファイルだけ処理をしたいのですが、この例だと毎回全ファイルを読むみたいなので
例えば処理したファイル名はシートに記録しておいて
更新起動時に毎回処理したかしてないか判断するのも追加するって感じですかね。

2019/12/19(木) 22:24:23.56

>>179
悪用すると色々できそうだな

2019/12/20(金) 10:20:51.92

ファイルのタイムスタンプで判断すれば？

2019/12/20(金) 11:57:58.87

Ruby で、ファイルの最終更新時刻を取得する

fs = File::Stat.new( "./a.txt" )
p fs.mtime #=> 2018-01-16 13:36:40 +0900 最終更新時刻

p current = Time.now #=> 2019-12-20 11:54:02 +0900
p current - fs.mtime, fs.mtime - current

#=> 60733042.351125, -60733042.351125

2019/12/20(金) 15:07:41.92

>>179
ファイルのアーカイヴ属性を変更すればいいよ。これなら新規以外にも、変更ファイルも拾えるはず。

2019/12/20(金) 15:08:25.80

馬鹿ばっか

2019/12/20(金) 15:24:50.75

ハゲばっか

2019/12/20(金) 20:34:34.24

馴鹿ばっか

2019/12/21(土) 11:10:49.09

クリスマスだから

2019/12/21(土) 19:08:13.95

セルに指定したフォントの情報などを変数（オブジェクト？）として
保持して、別のセルに貼り付けることはできないでしょうか

Public Sub textMacro()
ActiveSheet.Cells(1, 1).Select
ActiveCell.Value = "testTESTtest"
ActiveCell.Characters(Start:=5, Length:=4).Font.ColorIndex = 3
End Sub

こんな感じで、文字列の途中で色を変えたりフォントを変えたり
した情報を構造体配列として保持しておいて、ソートしたり
特定条件で抽出したりしたデータを、書式ごと別のセルに
貼り付けたいのです

上記のようなコードで一旦別のワークシートに書き込んでやって、
そこを参照してコピーすれば解決します
ですが作業用ワークシートを作るのが美しくないので、なにか
方法がないものかと悩んでいます

2019/12/21(土) 19:30:42.69

>>188
rangeにでも入れとけばいいんじゃないの

2019/12/21(土) 19:57:20.88

>>189
言葉足らずだったかもしれません

188で書いたコードのように、一旦どこかのセルに書式を設定するのではなく、
書式設定済みのデータを内部で生成したあとでセルに貼りたいのです

2019/12/21(土) 20:19:43.43

アドバンスフィルタ使うとか。

2019/12/21(土) 21:42:26.79

>>188
activecellは rangeオブジェクト
rangeオブジェクトは必ずどこかの実態セルに紐付けしないと設定、参照できない
なので、実態セルを使いたくなければ、仮想的に実装するしかない
全てを網羅するには、それは非現実的（ムダ）だから私ならやらない
あとの判断はお任せする
（may be. GL）

2019/12/21(土) 21:50:56.66

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

2019/12/21(土) 22:03:15.47

>>190
セルのコピーで済む処理をわざわざスクラッチする意味があるのか？
マクロ言語で車輪の再発明をするのは無駄だし結果的に美しくないものが出来上がって終わりなことが多いぞ

まぁ文字列書式の取得をするならRange.Charactersクラスを対象にForで一文字ずつループを回して、Fontプロパティで取れる各フォント属性を調べて構造体配列に格納していけば良い
Characters(i, 1)とか指定すれば一文字ずつ調べられる

具体的にはFontクラスのうち
Bold, ColorまたはColorIndex, FontStyle, Italic, Name, Size, ThemeColor, ThemeFont, Underlineあたりを取得しとけば何とかなるんじゃないか

2019/12/21(土) 22:26:10.14

>>192
>>194
内部データとして、特定の文字だけボールドにしたり赤字にしたりしたデータを
保持しておいて、それをそのままセルに貼れないだろうかという趣旨でした

全然実用性のない例ですが、何らかの名簿データを元データとして入力すると
して、「佐藤」という名字だけを赤で表示したい、というケースがあったとします。

このとき、表示するときに年齢別だったり性別だったりの条件で抽出したりする
際に、毎回セルにデータを書き込むときにフォントの設定をする必要があるのか、
一度設定済みのデータを貼れば済むような方法があるのか、という質問でした

現在は毎回貼るたびにフォントの設定をしているので、もっと効率的な方法が
あるのではないかと考えた次第

でも残念ながら、どうやらなさそうですね
確かにセルをコピーすれば済む話なので、断念します

2019/12/21(土) 23:09:32.16

>>195
鈴木健二
の鈴木だけを赤文字に設定したセルの書式コピーして
鈴木太郎
セルに貼り付けしても、鈴木だけを赤にはできなかった
（バージョンによって違うかもだが）
ロジックでやるしかないんじゃないかな
鈴木or宇都宮を赤にするとか

2019/12/21(土) 23:41:09.25

>>196
色々ありがとうございます

今回やりたいのは、鈴木だけを赤くした「鈴木　健二」というセルを作るのに、
事前にデータを作ってからセルに貼り付けたいということですから、
「鈴木　太郎」の「鈴木」が赤くならなくても特に問題ありません

この例で言うなら、コピペ以外の方法で、同じく鈴木だけが赤くなった鈴木健二の
セルを効率よく作りたい、ということですね

2019/12/22(日) 00:19:45.02

>>197
構造体とかでパラメータ設定すればやれないことはない
書式設定対象の文字列の開始オフセット位置と文字列長、フォント属性の設定値からなる構造体配列を作って、一括でRangeオブジェクトのプロパティに代入すれば良い

ただしパラメータを動的に指定する処理やらUIやらを構築する手間とか考えたら結局セルコピーでよくねってなる

2019/12/22(日) 09:13:02.87

office2010から2016へ変えたらマクロの遅さが気になって
セルコピーを別の方法でやれないか考えてる

2019/12/22(日) 10:54:11.14

マクロが遅いんじゃなくてPCのスペックが貧弱過ぎるんじゃないか

2019/12/22(日) 11:54:58.14

>>198
そこまでする価値はないですね
自分が知らないだけで、なにか簡単な方法があるのではないかと思って
いたのですが、そもそも需要がない動作なんでしょうね

2019/12/22(日) 12:26:41.63

>>199
変えたのはExcelだけか?
実はOSを10に変えた時にExcelも入れ替えたのではなく?

2019/12/22(日) 14:42:00.45

>>201
そこまでやるならmhtか何かでコード生成してからxls変換してレンダリングする方がいいよねって話になってしまうからな

2019/12/27(金) 23:18:51.19

んあ

2019/12/27(金) 23:55:11.89

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

2019/12/28(土) 10:04:17.04

北海道にいるんだろ

2019/12/28(土) 11:09:10.42

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

2019/12/28(土) 17:00:07.97

休みに入って大人しくなったな

2020/01/01(水) 22:49:57.07

フォルダ名を取得し名前を変える処理をしたいのですが、フォルダ名の頭に0がついているとセルに取り込んだ際、数値扱いされきえてしまいます。何かいい方法ないでしょうか？
ほぼネットから引用したものですが。。

Sub folder()
Dim folderPath As String
folderPath = Sheets(2).[B1]
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim n As Variant
n = fso.GetFolder(folderPath).SubFolders.Count

If (0 < n) Then
Dim I As Long
I = 1
Dim f As Object
For Each f In fso.GetFolder(folderPath).SubFolders
Cells(3 + I,1).Value=Str(f.Name)
I = I + 1
Next f
End If
End Sub

2020/01/01(水) 22:54:36.69

>>209
・セルの書式を予め文字列にしておく
・
Cells(3 + I,1).Value = "'" & Str(f.Name)
・セルに取り込まずに処理する

どれか

2020/01/01(水) 23:43:11.75

>>210
頭に「'」つける案いただきます！
すっかり頭からぬけておりました。
ありがとうございます！

2020/01/02(木) 00:01:52.13

>>209
頭に'付けるとかセルの表示形式を文字にするとか
それVBAじゃなくてExcelの範疇では

2020/01/02(木) 00:13:18.61

VBAはそもそもビジュアルベーシックフォーアプリケーション
エクセル（他office製品）のための言語
Excelの範疇も何もないよ

2020/01/02(木) 00:44:32.37

セルにフォルダ名を入れなきゃならない理由が全く分からない

2020/01/02(木) 00:52:53.68

PowerQueryにデータの参照元渡すとか

2020/01/02(木) 11:28:52.88

マクロでCSV書き出ししたデータですが
どうも１行ごとに最後のセルの末尾に勝手に改行コードが入っているようです
これはなんとかならないでしょうか

その改行コードもCRLFで言うところのCRだけのようで、見かけ上全くわかりません

aaa,ddd,ccc

とあったら、cccの末尾に見えないCFが張り付いている感じです
urlエンコードで見ると　ccc%0D みたいな感じです
どうしたら綺麗に書き出せますか？

2020/01/02(木) 12:02:15.68

>>216
改行コードをなくした場合、行の区切りはどうするの？
完全に思い通りにしたい時はテキストファイルの生成を自力でやるしかない

2020/01/02(木) 12:48:11.80

セル内で改行しても平気か確認した方がいい

2020/01/02(木) 12:53:49.13

>>216
ExcelではLFがセル内改行、CRLFが次の行
CRは意味を持たないので入ってても改行されない
消したければ普通にその文字を消せばいい

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終了の合図