Excel VBAで○○をやる方法まとめ: 4月 2015

2015年4月26日日曜日

Excel VBAでファイルを開くときに実行しておいたほうがいいマクロ

VBAでファイルを開くときに必ず行う確認処理

Excel VBAを使ったマクロを作る中で、別のExcelファイル（ブック）を開く処理はしばしば登場します。例えば「同じフォーマットで作られたファイルから、特定のデータを取り出して集計する」というような処理です。

マクロでファイルを開く処理を行う前に、私は必ず下記の2つの確認を行うことにしています。

開こうとしているファイルは存在するか
すでに同名のファイルを開いているか

存在しないファイルを開こうとすれば、当然エラーとなってしまいます。また、現在開いているファイルと同じ名前のファイルは同時に開くことはできません。エラーを回避するために事前に確認を行い、処理を中断したりスキップしたりできるようにしています。

「開こうとしているファイルは存在するか」確認サンプルコード

Private Sub file_chk(filepath As String)
Dim filename As String
filename = dir(filepath)
If filename  = "" Then
       MsgBox "ファイルが存在しません"
End If
End Sub

Dir関数を使うことでfilepathが存在するか否か確認することができます。存在する場合はfilenameにファイル名、存在しない場合には空白が代入されます。

「すでに同名のファイルを開いているか」確認処理サンプルコード

Private Sub file_open_chk(filename)
Dim wbchk As Workbook
For Each wbchk In Workbooks
   If wbchk.Name = filename Then
     msgbox filename & "を開いています"
   End If
Next wbchk

現在開いているブックの中に、指定のファイルと同じものがあるかFor Eachを使って確認するマクロです。同名ファイルを開いているときには、メッセージを表示して処理を中断するという処理を挟むようなことができます。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

Excel VBAで参照設定のやり方

Excel VBAを使うにあたって、参照設定というものが必要になる場合があります。そのやり方について紹介します。

Excel VBAで参照設定のやり方

Excelの上部メニュー[開発」]タブから[Visula Basic]を選択。
[Visula Basic]の上部メニューからツール→参照設定の順にクリック。
目的のライブラリにチェックをつけます。一度チェックをつければ、その後も設定は引き継がれます。

Excel VBAで参照設定が必要な場合

たとえばExcel VBAの中でWordファイルを開いてページ数を取得するような場合には、参照設定が必要になります。参照設定を行わないと、ページ数ではなく文字数が取得されてしまいました。

その対策として参照設定の[Microsoft Word 14.0 Object Library]にチェックをつけることで、問題なくページ数を取得することができました。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

「オブジェクトの位置またはサイズが変更されます」エラーの解決方法（Excel2010）

Excelファイルを操作しているときに「オブジェクトの位置またはサイズが変更されます」というエラーメッセージが表示されることがあります。

このエラーが出てしまうと、その後Excelファイルの操作・編集ができなくなってしまいます。

いったいどのように回避したらいいのか、解決方法をご紹介します。

「オブジェクトの位置またはサイズが変更されます」エラーの解決方法

Excelのメニューから、ファイル→オプション→詳細設定と進む
[挿入したオブジェクトをセルと共に切り取り、コピー、並べ替えを行う] のチェックを外して終了

この方法はあくまで暫定措置です

挿入したオブジェクトをセルと共に切り取り、コピー、並べ替えを行う] のチェックを外すことで、自動的にオブジェクト（図形）のサイズが変わらなくなるようになります。
このチェックをつけておきたいのであれば、新しくファイルを作りなおしたほうがいいかもしれません。

私の場合、こんな時に「オブジェクトの位置またはサイズが変更されます」エラーが出ました

部署で使っている管理表ファイルのオートフィルタを操作していたら、「オブジェクトの位置またはサイズが変更されます」エラーが出ました。ファイルの中にはオブジェクトがないのにも関わらず、このエラーが出てしまったのが不思議でした。自分が作ったファイルではないので、もともとオブジェクトがあったのかどうか分からず。結局新しい管理表ファイルを作りました。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

Excel VBAでVisioのページ数を取得するマクロ

Excel VBAを使って、Visioのページ数を取得するマクロを紹介します。

Visioのページ数を取得マクロサンプルコード

Private Function get_page_cnt_vsd(sFile As String, sPath As String) As Long
'ページ数の取得を行う関数（visio)
'引数：処理対象のファイル名、処理対象ファイルの格納フォルダ
   Dim visio As Object
   Dim lCount As Long

   Set visio = CreateObject("Visio.Application”)

   With visio
       'ファイルオープン
       .Documents.Open sPath & "\" & sFile
       
       'シート数取得
       lCount = .ActiveDocument.Pages.Count
       
       'Visioを閉じる
       .Documents(sFile).Close
       .Quit
   End With
   
   'オブジェクト変数を解放
   Set visio = Nothing
   get_page_cnt_vsd = lCount

End Function

Excel VBAでVisioのページ数を取得するマクロの気になるところ

Wordおよびパワーポイントの場合は、Dsofile.dllを使用すればファイルを開かずにページ数・スライド数を取得することができます。取得元となるのは、ファイルのプロパティに表示されているページ数・スライド数です。

ただ、Visioの場合は、ファイルのプロパティにページ数の項目が無いようにみえます。そのため、ファイルを開いてページ数を取得して閉じるという方法で値を取得しています。ファイルを開くとどうしてもマクロの実行速度が落ちてしまうので、ファイルを開かずに値が取れたらいいのですが…。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

Excel VBAでパワーポイントのスライド数を取得するマクロ

ExcelVBAを使ってファイルを開かずにパワーポイントのスライド数を取得するマクロを紹介します。

ファイルを開かないでパワーポイントのスライド数を確認サンプルコード

Private Function get_page_cnt_ppt(sFile As String, sPath As String) As Long
'ページ数の取得を行う関数（パワポ)
'引数：処理対象のファイル名、処理対象ファイルの格納フォルダ
        
    Dim obj As Object
    
    'オブジェクトを取得
    Set obj = GetObject(sPath & "\" & sFile)
    
    'スライド数を取得
    get_page_cnt_ppt = obj.BuiltinDocumentProperties(25).Value
    
    Set obj = Nothing
    
End Function

スライド数を取得するためには、BuiltinDocumentPropertiesに"25"もしくは"Number of Slides"を指定します。

参考：Dsofile.dllのダウンロード・使い方

ファイルを開かずにパワーポイントのスライド数を確認するには、Dsofile.dllが必要

ファイルを開かずにスライド数を取得するためには、Dsofile.dllをインストールする必要があります。つまり、このマクロを不特定多数の人が使う場合には、各自のPCにDsofile.dllをインストールしなくてはいけないということです。Dsofile.dllインストールが難しいという場合には、どうしたらよいのでしょうか。

その答えとしては、マクロの中でファイルを開くことでスライド数を確認するという方法があります。マクロの実行速度は落ちますが、特別な設定なしでスライド数を確認することが可能です。

ファイルを開いてパワーポイントのスライド数を確認サンプルコード

Private Function get_page_cnt_ppt(sFile As String, sPath As String) As Long
'ページ数の取得を行う関数（パワポ)
'引数：処理対象のファイル名、処理対象ファイルの格納フォルダ

   Dim pp As Object
   Dim lCount As Long

   Set pp = CreateObject("PowerPoint.application”)

   With pp
      'ファイルオープン
       .Presentations.Open sPath  & "\" & sFile, ReadOnly:=True
      'スライド数取得
       lCount = .activepresentation.slides.Count
       
       'パワポを閉じる
       .Presentations(sFile).Close
       .Quit
   End With
   
   'オブジェクト変数を解放
   Set pp = Nothing
   get_page_cnt_ppt = lCount

End Function

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

Dsofile.dllのダウンロード・使い方

Excel VBAを使ってファイルのプロパティ情報を知りたいとき、いちいちファイルを開いて情報を取得するのでは時間がかかってしまいます。ファイルを開かずにユーザー設定のプロパティを取得するには、「Dsofile.dll」を使うのが便利です。

Dsofile.dllのダウンロード

Dsofile.dllは下記のサイトから無料で入手することができます。

Microsoft Developer Support OLE File Property Reader 2.1 Sample (KB 224351)

Dsofile.dllを使用することで、ファイルのプロパティを迅速に取得することが可能になります。

ファイルの概要プロパティというのは？

エクスプローラーを開いて適当なファイルを右クリックします。そして、プロパティを選択し、概要タブを開きます。（概要タブがない場合は、詳細タブをクリックします）
すると、ファイルには更新情報や作者名、会社名など様々なメタデータが保存されていることがわかるかと思います。

Dsofile.dllの使い方

Dsoファイルをインストール後、VBAではBuiltinDocumentPropertiesコレクションを利用してファイルのプロパティ値を取り出すことが可能になります。取り出したい値については、下記のように指定します。

BuiltinDocumentProperties(インデックス番号)
BuiltinDocumentProperties(プロパティ名)

インデックス番号とプロパティ名は下記の通りです。

Title：タイトル
Subject：サブタイトル
Author：作成者
Keywords：キーワード
Comments：コメント
Template：テンプレート
Last Author：更新者
Revision Number：改訂番号
Application Name：アプリケーション名
Last Print Date：印刷日時
Creation Date：作成日時
Last Save Time：更新日時
Total Editing Time：編集時間
Number of Pages：ページ数
Number of Words：単語数
Number of Characters：文字数
Security：セキュリティ
Category：分類
Format：形式
Manager：管理者
Company：会社名
Number of Bytes：バイト数
Number of Lines：行数
Number of Paragraphs：段落数
Number of Slides：スライドの数
Number of Notes：メモの数
Number of Hidden Slides：非表示スライドの数
Number of Multimedia Clips：マルチメディアクリップの数
Hyperlink Base：ハイパーリンクの基点
Number of Characters (with space)：文字数（スペースを含む）

サンプルコード

BuiltinDocumentPropertiesを使って、Wordのページ数を取得するサンプルコードです。

Private Function get_page_cnt_word(sFile As String, sPath As String) As Long
'ページ数の取得を行う関数（Word)
'引数：処理対象のファイル名、処理対象ファイルの格納フォルダ
  
    Dim wdDoc As Object
     
    'オブジェクトを取得
    Set wdDoc = GetObject(sPath & "\" & sFile)
     
    'ページ数を取得
    get_page_cnt_word =  wdDoc.BuiltinDocumentProperties(14).Value
    
   'オブジェクト変数を解放
   Set  wdDoc = Nothing
 
End Function

ExcelVBA（マクロ）でWordのページ数の取得を行う方法

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

2015年4月24日金曜日

VBAで指定のファイルに含まれるシート一覧を作成する方法

指定のエクセルファイルに含まれるシートの一覧が必要なときがたまにあります。
シートの一覧表から各シートへとべるようにリンクを張ったり、単純にどこまで設計書を作成したか確認したかったり…。
それほど複雑な処理が必要なわけではないので、覚えておくと結構役に立つのではないかと思います。

サンプルコード

Sub make_sh_list(sPath as string,sFile as string,sSh as string)
'引数は記入先のファイルパス、記入先のファイル名、記入先のシート名

  Dim sh As Object
  Dim row_num As Long
  Dim col_num As Long
  Dim wb As Workbook
  Dim ws As Worksheet
  

  Workbooks.Open sPath

  Set wb = Workbooks(sFile)
  Set ws = wb.Worksheets(sSh)
    
  row_num = 1
  col_num = 1

  For Each sh In wb.worksheets
    ws.Cells(row_num, col_num) = sh.Name
    row_num = row_num + 1
  Next sh

End Sub

For Eachステートメントは覚えておくと便利

上記のコードでは、For Eachをぐるぐる回しながら、指定ファイルのWorksheet名を取得しています。
For NextではWorksheetの数だけループが回るようになっているので、終了条件を気にする必要はありません。

シート名を記入した後にrow_numを1ずつ増やすことで、シート名を記入するセルの指定が1行ずつ下へずれていくようになっています。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

Excelで名前の定義に日本語は使わない方がいい理由

Excelで作ったファイルの中で、マクロや関数を多数利用する場合「名前の定義」は非常に便利な機能です。
特定のセル範囲に対して名前を定義することで、マクロの中で下記のように値を取得できるようになります。

dim rData as range
set rData = ws.range("ファイル名一覧")

たとえば、セルのA1:B15の範囲が"ファイル名一覧"として定義されていれば、下記のように処理を行ったものと同一の結果が得られます。

dim rData as range

set rData = ws.range("A1:B15")

シートレイアウトの都合で取得元のセルの位置が変わったとしても、名前が定義されていればマクロのコードを修正する必要はありません。

便利な「名前の定義」機能ですが、定義をする名前はアルファベットにしておいた方が無難なようです。

日本語で名前を定義したところ、Excelの起動に非常に時間がかかったことがありました。日本語名が10個くらいで、起動に10分以上かかったでしょうか。

あまりにも起動に時間がかかるので、途中であきらめてファイルは作り直しました。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

ExcelVBA（マクロ）でWordのページ数の取得を行う方法

ExcelVBA（マクロ）でWordのページ数の取得

Excel VBAでWordのページ数を取得するやり方を書いてみました。マクロを使ったページ数の確認方法は2つあります。マクロの中でWordファイルを開いて確認するか、開かずに確認するかという違いがあります。まず、実際のコードを紹介します。

Wordファイルを開かずにページ数を確認

Private Function get_page_cnt_word(sFile As String, sPath As String) As Long
'ページ数の取得を行う関数（Word)
'引数：処理対象のファイル名、処理対象ファイルの格納フォルダ
 
    Dim wdDoc As Object
    
    'オブジェクトを取得
    Set wdDoc = GetObject(sPath & "\" & sFile)
    
    'ページ数を取得
    get_page_cnt_word =  wdDoc.BuiltinDocumentProperties(14).Value
   
   'オブジェクト変数を解放
   Set  wdDoc = Nothing

End Function

ファイルを開かずにページ数を取得するためには、Dsofile.dllのインストールが必要になります。詳しくは下記の記事を参照ください。
Dsofile.dllのダウンロード・使い方

Wordファイルを開いてページ数を確認

Private Function get_page_cnt_word(sFile As String, sPath As String) As Long
‘ページ数の取得を行う関数（Word)
'引数：処理対象のファイル名、処理対象ファイルの格納フォルダ

   Dim wdApp As Object
   Dim wdDoc As Object
   Dim lCount As Long

   Set wdApp = CreateObject(“Word.Application”)

   'ファイルオープン
   Set wdDoc = wdApp.Documents.Open(sPath & “" & sFile)
   'ページ数取得
   lCount = wdDoc.ComputeStatistics(Statistic:=wdStatisticPages, IncludeFootnotesAndEndnotes:=True)

   'Wordを閉じる
   wdDoc.Close
   wdApp.Application.Quit

   'ページ数表示
   get_page_cnt_word = lCount

   'オブジェクト変数を解放
   Set wdDoc = Nothing
   Set wdApp = Nothing

End Function

それぞれの方法のメリット・デメリット

Wordファイルを開かずにページ数を確認
メリット

処理速度が速い

デメリット

実際のページ数と違った値を出すことがある
dsofile.dllをインストールする必要がある

Wordファイルを開いてページ数を確認
メリット

正確なページ数を取得することができる

デメリット

ファイルを開くため、処理速度が遅い
参照設定を行う必要がある

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

ExcelVBAで TSV/CSVファイルを読み込むとき、数値の先頭にある0を残す方法

ExcelVBAでTSV/CSVファイルを開くときに"Workbooks.open"を使うとある問題が起きることが発覚しました。数値の頭に"0"を含んでいると、それが消えてしまうのです。

あえて先頭の0を残すにはどうしたらいいのか、調べてみたところ"Workbooks.OpenText"を使えばよいことがわかりました。 "Workbooks.OpenText"で開くと書式が文字列になるので、先頭の0が残るんですね。

サンプルコード

praivate sub file_open(sFilePath as string)
'引数は開きたいファイルのパス

 Dim vTmpInfo(256) As Variant
    Dim i As Integer
    
    For i = 0 To 256
        vTmpInfo(i) = Array(i, 2)※
    Next
    
   Workbooks.OpenText filename:=sFilePath, _
   Origin:=932, DataType:=xlDelimited, TextQualifier:= xlDoubleQuote, Tab:=True, _
   FieldInfo:=vTmpInfo, TrailingMinusNumbers:=True

読み込むときのデータ形式の指定について

※vFieldInfoは読み込むときのデータの形式を指定するパラメーター。（行番号,データ形式）で指定する。データ形式はこちら。

xlGeneralFormat 1（一般形式）
xlTextFormat 2（テキスト形式）
xlYMDFormat 5（YMD 日付形式）

文字コードの指定

"Origin:=932"で行っているのは、UTF-8形式でファイルを開くという指定です。

この記事はお役に立てたでしょうか？
もし参考になると思っていただけたら、こちらのボタンから共有をお願いします。

登録: 投稿 (Atom)

2015年4月26日日曜日

VBAでファイルを開くときに必ず行う確認処理

「開こうとしているファイルは存在するか」確認サンプルコード

「すでに同名のファイルを開いているか」確認処理サンプルコード

Excel VBAで参照設定のやり方

Excel VBAで参照設定が必要な場合

「オブジェクトの位置またはサイズが変更されます」エラーの解決方法

この方法はあくまで暫定措置です

私の場合、こんな時に「オブジェクトの位置またはサイズが変更されます」エラーが出ました

Visioのページ数を取得マクロ サンプルコード

Excel VBAでVisioのページ数を取得するマクロの気になるところ

関連記事

ファイルを開かないでパワーポイントのスライド数を確認 サンプルコード

ファイルを開かずにパワーポイントのスライド数を確認するには、Dsofile.dllが必要

ファイルを開いてパワーポイントのスライド数を確認 サンプルコード

Dsofile.dllのダウンロード

ファイルの概要プロパティというのは？

Dsofile.dllの使い方

サンプルコード

関連記事

2015年4月24日金曜日

サンプルコード

For Eachステートメントは覚えておくと便利

ExcelVBA（マクロ）でWordのページ数の取得

Wordファイルを開かずにページ数を確認

Wordファイルを開いてページ数を確認

それぞれの方法のメリット・デメリット

サンプルコード

読み込むときのデータ形式の指定について

文字コードの指定

Visioのページ数を取得マクロサンプルコード

ファイルを開かないでパワーポイントのスライド数を確認サンプルコード

ファイルを開いてパワーポイントのスライド数を確認サンプルコード