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/08(日) 23:50:05.28

>>63
> 普通にシート指定すればよい

その「普通」がよく分からなくて。
>>59 で試行したように、
sheet上での関数に他のbookから参照させるのとは
勝手が違うようだ。

> ところでR_TargetFullPathはセル名と言ってなかったかな？

は、>>50 で書いたとおり、
Worksheet上で普通に
　$A$8 = R_TargetFullPath
と名前を定義して
としただけ。

2019/12/09(月) 00:26:53.00

>>64
シート指定とか調べず勘でやってるのか？
基本が無いのに勘で文法は書けないよ、知らないならちゃんと調べた方がいい

名前を定義とは、$A$8セルに「R_TargetFullPath」という名前を付けたと言ってる？
ちなみにVBAでR_TargetFullPathという名前の変数か定数作ったりしてない？

2019/12/09(月) 00:28:16.96

R_TargetFullPathに何を入れたいのかもう一度よく考えてコードを見直せ

50 (ﾜｯﾁｮｲ 4bda-Fgt1) · 2019/12/09(月) 18:07:53.39

いろいろ考えてくれた皆さん、どうもありがとう。
自分の考えを上手く伝えられず、申し訳ない。

自分なりの考えで、確証は得られていないけど、
・"BuiltinDocumentProperties" を使った「前回更新日時」の取得は、対象ファイルをいちいち開かないと取得出来ないため、Public Subでやるしかない。
・が、Range(定義名)では、例えアクティブな自sheet内の定義名でもなぜか上手く取得出来ない。
・ただし、アクティブなセルをCells(行数, 列数)なら値取得が出来る。
ということかと。

ならば、「1つのファイルの日時を取得するのに定義名を使う」考え方を変えて、
・Cells(行数, 列数)を活かして、フォルダにDLした .xl* ファイルの、全ての前回更新日時を取得する。
としたら、出来た。

3つめの "Public Sub LastSaveTime3()" をボタンにマクロ登録
https://i.imgur.com/19QWsrZ.png

Power Queryで取得したフォルダ内.xl*ファイル一覧表（タイトルが黄緑色の列）の右列に
上記VBAで「前回更新日時」を取得した結果（タイトルが深緑色の列）
https://i.imgur.com/jegG0b5.png

2019/12/09(月) 19:47:30.76

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

2019/12/09(月) 20:06:58.60

>>67
ブックとシートを指定せずにセルを指定してるから取得できないと何度…
指定してないからアクティブなシートを拾いに行く
アクティブなシートに名前の定義がなければ当然名前の定義を取得できるわけがない

2019/12/09(月) 20:26:18.92

>>67
Public Subによる制御フローじゃないとドキュメントプロパティを参照できないなんていう制約は存在しないと思う

2019/12/09(月) 20:35:27.90

>>70
ただのSubに変えたら、ダメだった。

2019/12/09(月) 20:37:14.17

>>69
仰いますけど、
参照しようとした定義名は、自bookのアクティブsheet内だす。

2019/12/09(月) 20:45:36.21

>>71
参考にしたのはMSコミュニティのこれ↓

https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_excel-mso_other-mso_2010/%E5%89%8D%E5%9B%9E%E4%BF%9D%E5%AD%98%E6%97%A5/97f992ad-416d-e011-8dfc-68b599b31bf5?messageId=33ce5330-cb6f-e011-8dfc-68b599b31bf5&;auth=1

2019/12/09(月) 21:50:46.52

Range("R_TargetFullPath").Value

2019/12/09(月) 22:19:06.53

>>72
そのシートがアクティブになっていれば取得できる
なってなければ取得できない
取得できないということはアクティブだと思い込んでるだけか、名前が間違ってるか、指定が間違ってるか

2019/12/09(月) 22:21:49.68

>>72
シートは一つ？別のシートにも同名範囲があって特定できないとか？

2019/12/09(月) 22:23:12.83

>>74
それだと1004エラーが出たらしい

2019/12/10(火) 00:08:00.24

>>73
これはサブルーチン呼び出しの都合でPublicスコープにしているだけで、BuildinDocumentPropertiesの仕様とは関係ないのでは

2019/12/10(火) 00:18:53.99

名前付き範囲のスコープの問題もありそう

2019/12/10(火) 06:11:01.50

>>77
その時は
Range(R_TargetFullPath).Value
としたんじゃないか？>>52-53

2019/12/10(火) 11:53:22.18

ページに設定しているフィルターの解除をする場合

Range("E5").Select
Selection.AutoFilter

といった具合にRangeとセットで使うしか無いのでしょうか？
ここでは、VBAで「E5」を指定していますが、中のデータが入力により空になる事もある為
Range指定する事なくフィルタを解除したいです。

2019/12/10(火) 20:17:21.65

>>81
ページって何？シートのこと？
それとも印刷範囲のこと？

2019/12/10(火) 21:36:21.94

シート１のJ１～J220のセルをシート２に行で張り付けて
それをどんどん繰り返して１～48400の列を220*220の表にしたいんだけど、forに対するnextが無いと怒られます
3つのforに対してnextが足りてないのはわかるのですが、適当な場所においてもエラーが出ます
どうしたら解決するでしょうか？

Sub TEST4()
Dim S1 As Worksheet, S2 As Worksheet
Dim i As Integer, y As Integer, z As Integer

For i = 1 To 220
For y = 1 To 48181 Step +220
For z = 220 To 48400 Step +220

Set S1 = Worksheets("Sheet1")
Set S2 = Worksheets("Sheet2")
S2.Range("A" & i & ":" & "HL" & i).Value = S1.Range("J" & y & ":" & "J" & z).Value
Next

End Sub

2019/12/10(火) 21:54:10.27

>>83
Sub TEST4()
　　Dim S1 As Worksheet, S2 As Worksheet
　　Dim r1 As Long, r2 As Long

　　Set S1 = Worksheets("Sheet1")
　　Set S2 = Worksheets("Sheet2")
　　r2 = 1

　　For r1 = 1 To 48400 Step 220
　　　　S2.Range("A" & r2 & ":HL" & r2) = WorksheetFunction.Transpose(S1.Range("J" & r1 & ":J" & r1 + 219))
　　　　r2 = r2 + 1
　　Next
End Sub

2019/12/10(火) 21:58:10.86

>>83
間違った場所に置くからエラーが出るんでしょうな

2019/12/10(火) 22:05:06.97

Sub TEST4()
　　Dim S1 As Worksheet, S2 As Worksheet
　　Dim r1 As Long, r2 As Long
　　Set S1 = Worksheets("Sheet1")
　　Set S2 = Worksheets("Sheet2")
　　r2 = 1
　　For r1 = 1 To 48400 Step 220
　　　　S2.Range("A" & r2).Resize(, 220) = WorksheetFunction.Transpose(S1.Range("J" & r1).Resize(220))
　　　　r2 = r2 + 1
　　Next
End Sub

2019/12/10(火) 22:33:04.15

>>83
シート1には既に220*220の表があって、それをシート2に縦横入れ換えて貼り付けたいってこと？

2019/12/10(火) 22:41:16.48

現在表示しているワークブックからファイル名の一部を取り出して、その数字をMsgBoxで表示するなど他の計算で使うことってできないでしょうか？

例えば「01-5.xlsx」の場合「-5」、「02+5.xlsx」の場合「5」、「03+1000.xlsx」の場合「1000」、「04-1234.xlsx」の場合「-1234」のような形
左の数字は2桁、右の数字は1～4桁でファイルによって違います
0や小数点以下の数字はありません
ファイル名がプラスの場合とマイナスの場合があります
正数の場合ファイル名は+表記ですが記号自体は必要ありません
「マクロを実行しているワークブック」ではなく「現在表示しているワークブック」のファイル名を参照したいです

プログラミングの経験が全くなくて先週からマクロを触り始めたので不可能なことを質問かもしれませんがよろしくお願いします

2019/12/10(火) 22:46:23.21

>>84、866
スゲーできたーありがとうございます。中身よく見て、勉強します。

>>87
シート１に１列で48400ほどデータがあるんで、それを別シートに220*220の表にしたかったんです。

2019/12/10(火) 23:00:44.84

>>88
+ または - を区切り文字としてThisworkbook.NameをSplit関数で分割して配列化する

得られた配列の二つ目の要素を取り出して整数型に型変換する

この二つのステップを実現すれば良い

2019/12/10(火) 23:02:34.06

>>88
よく要件を読んだらThisworkbook.Nameより
ActiveWorkbook.Nameの方が良いかも

2019/12/10(火) 23:04:16.14

>>90
-が消える上に.拡張子まで全部取得する羽目になる

2019/12/10(火) 23:06:54.98

ファイル名を.で区切った1個目の3文字目以降を取得して数値変換すればいい

2019/12/11(水) 01:12:21.51

>>82
シートです！

2019/12/11(水) 05:06:25.08

>>81
Worksheets("シート1").Autofiltermode ＝ false

2019/12/11(水) 09:15:52.19

>>83
For NextではNextの後を省略すべきじゃない。
For Eachならまだ分かるが。

2019/12/11(水) 11:27:42.94

>>95
サンキュー！！

2019/12/11(水) 19:02:34.08

それ多分フィルタ掛かってないとコケる。
On Error～か、If AutoFilterMode Then入れないと。

2019/12/11(水) 23:16:02.27

>>98
Falseにする分には問題無い。

2019/12/12(木) 20:44:42.97

>>88
Sub test2()
'整数を格納
Dim lngFileName As Long

'正規表現オブジェクトを作成
Dim objReg As Object, objRegMatch
Set objReg = CreateObject("VBScript.RegExp")
With objReg
'ファイル名のパターンを指定
'[半角数字2文字][区切り文字1文字][半角数字1～4文字][拡張子(xlsxまたはxls)]に一致
.Pattern = "^\d{2}.(\d{1,4})\.(?:xlsx|xls)$"
.Global = True
End With

'正規表現にマッチしないファイル名だった場合の処理
Set objRegMatch = objReg.Execute(ActiveWorkbook.Name)
If objRegMatch.Count < 1 Then
'メッセージボックスを表示
MsgBox ("正規表現アンマッチ")
'Label1にジャンプして処理を終了
GoTo Label1
End If

'正規表現のグループ化した部分を整数として格納
lngFileName = objReg.Replace(ActiveWorkbook.Name, "$1")

'メッセージボックスを表示
MsgBox (lngFileName)
Label1:
End Sub

2019/12/12(木) 21:07:09.62

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

2019/12/12(木) 22:26:00.96

ファイル名に + と - は使えないんじゃね

2019/12/12(木) 22:29:22.61

あれ？使えるのか

2019/12/12(木) 23:03:36.86

>>103
使えるけどコマンドプロンプトでトラブルが起きることがあるから推奨しないと主張する派閥もいる

2019/12/13(金) 08:53:38.24

特定のディレクトリに複数のエクセルがあってそのエクセルの特定のシートに値を設定するってマクロを作ったところなんですが特定のシートがない場合は処理を抜けるようにするのってどうすればいいんでしょうか

2019/12/13(金) 09:22:06.72

バイナリデータをエクセルに16列で読み込んで（ここまでは出来た）、
今度はそれの逆をしようとしているのですが最初で詰まっています

とりあえず1列だけでもと、やってみたものの全然うまくいきません
サンプルのOutputをBinaryに、Print をPut構文にすればできるような気がするのですが
出力ファイルにデータが反映されてなかったりといった状況です

Sub make()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim datFile As Variant
datFile = ActiveWorkbook.Path & "\data.bin"

Open datFile For Output As #1

Dim i As Long
i = 1
Do While ws.Cells(i, 1).Value <> ""
Print #1, ws.Cells(i, 1).Value
i = i + 1
Loop

Close #1

End Sub

2019/12/13(金) 09:29:21.11

>>105
Dir関数かFileSystemObject. FileExistsで調べられる

2019/12/13(金) 09:33:50.19

>>105
ファイル存在確認じゃなくてシート存在確認かごめん

2019/12/13(金) 10:57:50.32

>>108
シートがない場合の件はForで回して無事解決できました

2019/12/13(金) 12:12:29.79

VBAで名前定義がされたセルに値を書き込む際名前定義の範囲がシート指定の場合操作できないのでしょうか

2019/12/13(金) 14:57:25.33

>>106
出力するデータの型がバイナリになっていないとだめかも

2019/12/13(金) 18:47:12.50

>>110
そんなことないはず。

2019/12/13(金) 21:57:03.12

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

2019/12/14(土) 10:49:44.02

PDFからのデータ取得ってどうにかならんもんかな。
Adobeの有料版使ってエクセル変換してるが、フォーマットによってはレイアウト崩れが酷くて使い物にならん。
ハイライトリストも試してみたんだけど、文字がぶつ切りでしか取れなくて、速度も遅い。
座標見ながら文字結合するのもアレだし。
RPA的にコピペするのもマヌケだしなぁ。

2019/12/14(土) 11:40:24.17

そんな馬鹿なことをしなくてもいいように業務フローを見直す、が正解

2019/12/14(土) 11:59:38.55

>>114
理論上はPostscriptを完璧にシミュレートすれば可能なはずなんだけど、かなり面倒だしそこまでやってるアプリは今のところ見た事ない
レイアウトが崩れない変換アプリがあるんなら教えて欲しいわ

2019/12/14(土) 14:16:07.99

PDFのレンダリングは完璧なんだから
取得ができないのは本来おかしいんだけどね

2019/12/15(日) 09:28:05.88

環境は、OS:Windows10, Excel2016です。
初歩的な質問ですが、よろしくお願いします。

エクセル開発タブ⇒マクロの記録を選択
マクロの保存先を新しいブックにしてＯＫすると、
新しいブックが表示されてしまいます。（EXCEL2010までは表示はされなかった気がします。）
表示させないようにしたいですが方法はありますでしょうか？

2019/12/15(日) 12:10:44.95

Excelの使い方の質問ですな

2019/12/15(日) 13:30:33.36

>>118
そもそもそんな質問をする理由が分からない
マクロの保存先を作業中のブックに変えればいいだけの話では
RPAとかの都合なのか？

2019/12/15(日) 14:37:37.17

VBAのコードを長時間書いていたら肛門が痛くなりました。
これは何かの病気？いい薬はありますか？

2019/12/15(日) 15:56:49.13

Worksheets(1), Worksheets(2), Worksheets(3), ...
が常にワークシートの並び順になっている事を前提にコーディングしても大丈夫ですか？

2019/12/15(日) 16:04:03.19

止めは先が100%大惨事になるだろうな

2019/12/15(日) 16:08:21.42

>>122
OK
シートの並び順とシートのIndexは連動しているから

ただ他人と共有するブックでシートIndexに依存したコードを書くならば、シートの追加や削除や並び替えをユーザー側で行えなくするために、ブック構成の保護をかける必要はあるかも

2019/12/15(日) 20:43:05.99

>>122
非表示がなければ

2019/12/15(日) 21:19:26.62

インデックス値とシートの順にそんな保証あったかな
使い古しのワークブックだと最初のシートがSheet1とは限らない気がするし

2019/12/15(日) 21:53:45.41

sheet1じゃなくてsheet(1)だぞ
左端がsheet(1)

2019/12/15(日) 22:30:02.10

>>126
CodeNameの話じゃなくてシートインデックスの話だぞ

2019/12/15(日) 23:11:58.85

VBAで拡張子がDWGのCAD図面ファイルから文字列と数字を抜き出してエクセルに貼り付けたいんだけどなんかイイ方法ない？

2019/12/15(日) 23:42:09.44

AutocadのVBA使えば。

2019/12/15(日) 23:44:25.04

>>130
AutoCADのソフトは持ってないんだけど出来るの？

2019/12/16(月) 02:59:51.17

DXFファイルはないのか？

2019/12/16(月) 08:14:35.94

>>132
ないんだ

2019/12/16(月) 08:19:35.03

>>129
VBAからほかのアプリをコントロールする方法があるから、適当なフリーウェアでDXFに変換
DXFはテキストファイルだから文字列がそのまま入ってる

2019/12/16(月) 09:20:06.30

>>134
会社だからあんまりフリーソフト入れたくないんだよなぁ

2019/12/16(月) 12:08:57.72

どこまで読み取りたいのか分からんが、タイトルとかコメントとか作者とかは読み取れるんじゃない?

AutoCAD入ってなくても動くか分からんけどCOM用のAPI使って読み取るサンプルならAutodeskのフォーラムに有ったぞ。

2019/12/16(月) 18:36:30.35

>>135
業務上必要だと上司に相談して無理ならできませんでいいやん

2019/12/16(月) 19:13:38.22

DWGファイルを扱うってことはどっかの部署ではAutoCADが動いてるわけだから環境借りるなりすればいい

2019/12/16(月) 19:29:44.71

何がやりたいのか（最終目的が何なのか）分からないが、
AutoCADのスレで質問した方がいいと思う

AutoCAD総合スレ part7
https://mevius.5ch.net/test/read.cgi/bsoft/1556080032/

2019/12/16(月) 20:17:26.44

そんなデータの編集業務ってブラック企業じゃね

2019/12/16(月) 20:25:39.43

図面から読み取る仕事が多くてVBAで自動化できないかと考えてるだけだよ。ブラック企業でもないよ。
バイナリファイルじゃ読み取れないし、フリーソフトで一つ一つテキストファイルDXFに変換っていうのもなかなかナンセンスな気がする

2019/12/16(月) 21:37:32.27

>>141
お前が馬鹿なだけだろ

2019/12/16(月) 21:44:36.78

よかったな天才に教えて貰えるぞ

2019/12/16(月) 21:59:33.00

>>141だけど天才の>>142に教えてほしい。どうしたらいい？

2019/12/16(月) 23:05:55.21

その図面データに営利目的で使用してはいけないって書いてないか？

2019/12/16(月) 23:19:30.71

社内の人が作ってる図面なんだが

2019/12/17(火) 01:41:33.76

だったら保存形式かえてもらえばすむやん

2019/12/17(火) 02:29:00.49

CADファイルならVISIOで開けるじゃね
VISIOにVBA載ってなかったか

2019/12/17(火) 02:51:35.82

DWGもCOM経由でExcelから扱える

2019/12/17(火) 08:50:39.06

Excelで作成したものをフリーOfficeのCalcで開こうとするとエラーが出るので、違うPCのExcelにコードを写したいのですが、どうすれば良いでしょうか？
sheetは5枚でそれぞれデータが入力されています。
そのシートをコピーしてマクロコードをコピペすればできるのでしょうか？
コピーはできても貼り付ける場所が分かりません。教えてください。

2019/12/17(火) 09:41:37.65

マクロは互換性ほぼ無いからなあ

2019/12/17(火) 15:37:53.37

>>150
よく分からん。
ファイルをコピーしてそのファイルをそのまま使えば良いんじゃね?

要はさ、そのマクロのコ―ドがExcelの何を使っているかで変わるだろ。
コードを追って必要なものをコピーすれば使えるだろうよ。

2019/12/17(火) 20:23:29.92

慣れてる人ならできるかもしれんが
初心者じゃ無理だろ、互換性ないし

2019/12/17(火) 20:37:54.29

OOoのVBAは相当単純なものじゃないと動かなかったような
色々省略するのもダメだったような気がする

2019/12/17(火) 20:55:11.10

OOOBasicなんてあるんだな
勉強する人いるの？

2019/12/17(火) 21:06:28.96

OOOBasicというのか・・・
数年前きまぐれにちょっと触って、あまりの互換性のなさに逃げ出したわ

2019/12/17(火) 23:47:48.67

>>150
馬鹿は死ね

2019/12/18(水) 02:07:35.42

なんでBasicにしたんだろね。
後発なんだから選べたはずなのに。

2019/12/18(水) 02:16:13.86

Cに似せるほうが大変だろう

2019/12/18(水) 07:45:18.46

>>158
VBが売れたから、機能はほぼそのままにVBAとしてofficeに搭載した
VCの登場はもっと後
時系列的に仕方ないとは思う
俺もCの方が良いとは思う

2019/12/18(水) 07:46:31.18

oooの話だろ

2019/12/18(水) 08:15:59.63

>>152

PC1のデータをPC2に移したい。でも、PC2にUSBなど外部のものを差し込めないしネットには繋がるけど仮想？か分からないけどデータをPC2のデスクトップとかドキュメントに保存できないので、
データの中身をコピーしてPC2でExcelを開きペーストすれば良いのではと考えたのですが、
Excelのデータを丸々コピーするにはまずシートをコピペしてその後マクロコードをコピペすれば良いのでしょうか？どこに貼り付ければ良いのかわかりません。

2019/12/18(水) 14:12:35.99

PC2のどこに移したいの？
PC2でデータを開き　データの中身をコピーして　Excel（新規ブック）を開きペーストすれば良い
でもその新規ブックは保存できないんでしょ？