ExcelVBAサンプルマクロ

ファイルを開くダイアログではじめに開くフォルダを設定する

GetOpenFilenameメソッドではオプションで設定されている
カレントフォルダが最初に開くようになっています。

GetOpenFilenameメソッドだけでは最初に開くフォルダを指定できないので、
入門書ではよく、カレントディレクトリを変更するChDirステートメントと
併用する方法が書かれています。

サンプルコード1



Sub ファイル選択1()

Dim strFileName As String

ChDir ThisWorkBook.Path & "¥"
strFileName = Application.GetOpenFilename

End Sub


しかし、このままではマクロを終了した後もカレントディレクトリが
マクロで指定したフォルダのままになってしまいます。

オプションは個人個人が自分のやりやすいように設定するもの。
それを勝手にいじってそのままにするのは反対です。


そこで、ChDirステートメントを使う前にCurDirステートメントで
変更前のカレントフォルダを取得し、処理実行後に元の設定に戻すか、
規定のフォルダを指定してダイアログを開ける
FileDialogオブジェクトを使うのがいいでしょう。

サンプルコード2



Sub ファイル選択2()

Dim strCurFolder As String '元のカレントフォルダのパス
Dim strFileName As String '選択したファイル名
Dim strFilePath As String 'ダイアログ表示時のカレントフォルダ

'◆初期設定
strCurFolder = CurDir
strFilePath = ThisWorkbook.Path & "¥"

'◆ファイルを開くダイアログ表示
ChDir strFilePath
strFileName = Application.GetOpenFilename(MultiSelect:=False)

If strFileName <> "False" Then
  MsgBox "選択したファイル名は" & strFileName & "です。"
Else
  MsgBox "キャンセルされました。"
End If

'◆カレントフォルダを元に戻す
ChDir strCurFolder

End Sub


ただし、CurDirステートメントはドライブが違うと変更できません。

たとえば、デスクトップ(Cドライブ)がカレントディレクトリの時に
他のドライブや、サーバーのフォルダを指定しても、カレントディレクトリは変わりません。

ローカルドライブならChDriveステートメントで変更できますが、
サーバーの場合、ChDriveステートメントで指定するとエラーが発生します。


最初に開くフォルダにサーバーを指定したい場合は、
FileDialogオブジェクトを使うといいでしょう。

ただし、FileDialogオブジェクトは2002以降でないと動きません。

サンプルコード3



Sub ファイル選択3()

Dim strFilePath As String
Dim strFileName As String

strFilePath = ThisWorkbook.Path & "¥"

With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = strFilePath
  .Show

  If .SelectedItems.Count = 0 Then
    MsgBox "キャンセルされました。"
    Exit Sub
  End If

  strFilePath = .SelectedItems(1)

End With

End Sub


InitialFileNameプロパティに規定のフォルダのパスを設定し、
Showメソッドでダイアログを開きます。
他にも、MultiSelectプロパティを指定すれば複数選択なども可能になっています。

SelectedItemsのCountプロパティから、選択したファイルの数がわかるので、
0の時は処理を終了させます。

ExcelVBAサンプルマクロ

ファイルを選択させるダイアログを表示する

ファイルを選択させるダイアログを表示するには、
GetOpenFileNameメソッドまたはGetSaveAsFilenameメソッドを使います。

サンプルコード1



Sub ファイル選択1()

Dim strFileName As String

strFileName = Application.GetOpenFilename
strFileName = Application.GetSaveAsFilename

End Sub


どちらもよく似ていますが、以下のような違いがあります。
・ボタンの名称、タイトルバーの文字が違う。
・GetOpenFileNameメソッドは引数MultiSelectにTrueを指定することで
 複数選択可能だが、GetSaveAsFilenameメソッドは単数のみ。
・GetSaveAsFilenameメソッドは規定のフォルダを指定できるが、
 GetOpenFileNameメソッドは指定できない。
・GetSaveAsFilenameメソッドはファイルを選ぶまで決定ボタン
 (保存)が押せない。

なお、GetOpenFileNameメソッド、GetSaveAsFilenameメソッドは
選択したファイル名を取得するだけで、実際の開く処理・
保存する処理は行われないため、別にOpenメソッドやSaveAsメソッドを実行する必要があります。


GetOpenFileNameメソッドは第1引数を指定する事で、
特定の拡張子のファイルのみ表示させることが出来ます。

サンプルコード2



Sub ファイル選択2()

Dim strFileName As String
strFileName = Application.GetOpenFilename("Excelファイルとテキストファイル,*.xls;*.txt")

End Sub


第1引数は「ファイルの種類に表示させる文字,拡張子」を文字列で指定します。
複数の拡張子を表示させたい場合は「;」で区切ります。


GetSaveAsFilenameメソッドは第1引数を指定する事で、
ダイアログを開いた時に最初に表示されるフォルダを指定することができます。

サンプルコード3



Sub ファイル選択3()

Dim strFileName As String
strFileName = Application.GetSaveAsFilename(ThisWorkbook.Path & "¥")

End Sub


GetOpenFileNameメソッドには規定のフォルダを指定する引数がないため、
最初に開くフォルダを指定する事ができません。
ファイルを開くダイアログを表示させつつ、規定のフォルダを指定したい場合は
ChDirステートメントなどを使います。


ExcelVBAサンプルマクロ

最新のファイルを開く

FileDateTime関数を使うと、ファイルの更新日付が取得できます。
この関数を使って、同一フォルダ内にある最新ファイルを開く
サンプルです。

サンプルコード



Sub 最新ファイルを開く()

Dim strFileName As String
Dim strFilePath As String

strFilePath = ThisWorkbook.Path & "¥"
strFileName = fncGetNewFile(strFilePath)

If strFileName = "" Then
  MsgBox "ファイルがありません。"
  Exit Sub
End If

Workbooks.Open strFilePath & strFileName

End Sub


Public Function fncGetNewFile(strFilePath As String) As String

Dim strFileName As String
Dim tmpFileName As String
Dim time As Date
Dim pretime As Date
 
tmpFileName = Dir(strFilePath & "*.xls")

Do Until tmpFileName = ""
  pretime = FileDateTime(strFilePath & tmpFileName)
  If pretime > time Then
    time = pretime
    strFileName = tmpFileName
  End If
  tmpFileName = Dir()
Loop

fncGetNewFile = strFileName

End Function


処理をすっきりするために、最新日付のファイル名を取得する部分を
ユーザー定義関数にして、ファイルパスの設定やファイルを開く処理とは分けました。

ユーザー定義関数fncGetNewFileで最新ファイル名を取得し、
呼び出し元のマクロで取得したファイル名を開いています。


ExcelVBAサンプルマクロ

フォルダ名を取得する

Dir関数の第2引数を指定すると、ファイル名だけではなく、
フォルダの名前なども取得できるようになります。


定数内容
vbNomal標準ファイルのみを取得対称にする
vbReadOnly読み取り専用ファイルを取得対象に含める
vbHidden隠しファイルを取得対象に含める
vbSystemシステムファイルを取得対象に含める(Winのみ)
vbVolumeボリュームラベル。すべての属性は無効。
vbDirectoryフォルダを取得対象に含める
vbAliasエイリアスファイルを取得対象に含める(Macのみ)


サンプルコード



Sub フォルダにあるフォルダ名をすべて取得()

Dim strFilePath As String
Dim strFileName As String

strFilePath = ThisWorkbook.Path & "¥"
strFileName = Dir(strFilePath, vbDirectory)

Do Until strFileName = ""
  If GetAttr(strFilePath & strFileName) = vbDirectory Then
    If strFileName <> "." And strFileName <> ".." Then
      MsgBox strFileName
    End If
  End If
  strFileName = Dir()
Loop

End Sub


引数にvbDirectoryを取得した場合は、フォルダのみを取得対象に
するのではなく、「通常のファイルとフォルダの名前を取得」という
動作になります。

そのため、フォルダ名だけを取得したい場合は、GetAttr関数で
フォルダかどうかを判定する必要があります。

◆GetAttr関数
GetAttr(パス)

また、現在のフォルダが「.」、親フォルダが「..」という名前で
取得されてしまうので、Ifステートメントで条件分岐し、
表示させないようにしています。

ExcelVBAサンプルマクロ

フォルダにあるファイル名を取得する

ファイル名やフォルダ名を取得するには、Dir関数を使います。

◆Dir関数
Dir(ファイルパス,[検索の種類])

ファイルパスには、取得したいフォルダパスとファイル名を指定します。
ファイル名には、あいまい検索が使えます。
「*.xls」とすれば、拡張子が.xlsのファイルをすべて取得します。

検索の種類は必須ではないですが指定するとフォルダなどを
取得対象に含めたりできます。
フォルダ名を取得する


ファイルが存在する場合はファイル名、存在しない場合は""が返ります。

ファイルパスに指定した条件に複数ファイル該当した場合は、
まずその中の1つが取得されます。
次のファイル名を取得するには、引数なしでDir関数を実行します。

サンプルコード



Sub フォルダにあるExcelファイル名をすべて取得()

Dim strFilePath As String
Dim strFileName As String

strFilePath = ThisWorkbook.Path & "¥"
strFileName = Dir(strFilePath & "*.xls")

Do Until strFileName = ""
  MsgBox strFileName
  strFileName = Dir()
Loop

End Sub


まず、ファイルパスを指定してDir関数を実行します。

ファイル名があれば、メッセージボックスにファイル名を表示し、
再度Dir関数を引数なしで実行することで次のファイル名を取得します。

すべてのファイル名を取得した後にstrFileName = Dir()を実行すると
""が返るので、繰り返し処理を抜けます。

ExcelVBAサンプルマクロ

データ型を変換する

VBAではある程度自動的にデータ型を判別してくれますが、
自分でデータ型を指定して処理したいときはデータ型変換関数を使います。
たとえば、Split関数で区切り文字で区切った配列を作成すると、
データ型は数値でもString型になります。

そこで計算しようとすると、文字列として結合してしまいます。

サンプルコード1



Sub データ型を変換する1()

Dim ary() As String

ary = Split("1,2,3,4,5", ",")
MsgBox ary(1) + ary(2)

End Sub


「2 + 3 = 5」となってほしいのですが、Split関数の戻り値が文字列なため、
文字列の「2」と「3」を結合した「23」が表示されてしまいます。

そこで、データ型を数値型に変換して同じ処理を行います。

サンプルコード2



Sub データ型を変換する2()

Dim ary() As String

ary = Split("1,2,3,4,5", ",")
MsgBox CInt(ary(1)) + CInt(ary(2))

End Sub


書き方に注意してください。
両方とも別々にCint関数で変換してから足しています。
MsgBox CInt(ary(1) + ary(2))ではありません。

Cintは、引数の値をInterger型に変換する関数です。
このほかにも、データ型ごとに変換関数があります。

データ型変換関数内容
CByteByte型に変換
CIntInterger型に変換
CLngLong型に変換
CSngSingle型に変換
CDblDouble型に変換
CCurCurrency型に変換
CDateDate型に変換
CStrString型に変換
CBoolBoolean型に変換
CDecDecimal型に変換
CVarVariant型に変換



ExcelVBAサンプルマクロ

書式を指定して表示する

Excelでは、数値には頭には0をつけられません。
「0001」と入力すると、Excel側で自動的に0が取られてしまいます。

数字の桁をそろえて表示したい、というような場合は関数を使って
書式を指定します。

書式を指定する関数にはとFormat関数とFormat$関数があります。
Format関数は結果がVariant型で返り、Format$関数は結果が
String型で返るという違いがあります。

しかし、Variant型で返ってもFormat関数の結果は文字列しか返らないので、
まったく意味がありません。
ムダにメモリを使うだけなので、常にFormat$関数でOKです。

◆Format$関数
Format$(値,書式)

Format$関数の書式は一般機能のセルの書式設定で使われる
書式文字とほぼ同じです。

数字の頭に0をつける以外にも、日付や時間などの書式設定もできます。

サンプルコード



Sub 数字の頭に0をつけて表示()

Dim i As Long

i = 1
MsgBox Format$(i, "0000")

End Sub


ExcelVBAサンプルマクロ

数値かどうか判定する

セルの値が数値かどうかを判断するのに、VBAには
IsNumeric関数というのがありますが、これだと11,1などの数字も
数値とみなしてしまいます。

サンプルコード1



Sub セルの値が数値か判断する1()

If IsNumeric("11,1") = False Then
 MsgBox "数値ではありません。"
End If

End Sub


より厳密に判断するにはワークシート関数のIsNumber関数を使います。

サンプルコード2



Sub セルの値が数値か判断する2()

If WorksheetFunction.IsNumber("11,1") = False Then
 MsgBox "数値ではありません。"
End If

End Sub


このほかにも、IsNumeric関数は「空白のセル = 0 = 数値」と判断しますが、
IsNumber関数は「空白のセル = 数値ではない」と判断します。

状況によって使い分けるといいでしょう。

ExcelVBAサンプルマクロ

ブックを閉じるときの保存確認のメッセージを非表示にする

通常、ブックを編集した後、ブックを閉じようとすると「変更を保存しますか?」のメッセージが表示されます。

しかし、ブックを編集をしていなくても、TODAY関数など
ブックを開いただけで計算される関数を使っている場合も
メッセージが表示されます。

このメッセージを表示させなくするには、Closeメソッドの引数
SaveChangesを指定します。


内容
TRUE保存してブックを閉じる
FALSE保存しないでブックを閉じる


True、Falseいずれかを指定した場合は、
保存確認のメッセージは表示されません。


サンプルコード



Sub メッセージを表示しないでブックを閉じる()

Dim strFileName As String

strFileName = "Book1.xls"
Workbooks(strFileName).Close SaveChanges:=False

End Sub

ExcelVBAサンプルマクロ

リンクの更新などのメッセージを表示しないで開く

ブックを開くときに使うOpenメソッドには、ファイルパス以外にも
引数があります。

これらの引数を指定すると、ファイルを開く前の
データの更新メッセージを表示させないなど、
開く時の動作を指定して開くことができます。

他のブックへの参照がある場合に表示される「ほかのデータソースへのリンクが設定されています」メッセージは、
引数UpdateLinksを指定することで表示されなくなります。

UpdateLinksは以下の値のいずれかを指定します。



内容
0外部参照・リモート参照ともに更新しない
1外部参照を更新、リモート参照を更新しない
2リモート参照を更新、外部参照を更新しない
3外部参照・リモート参照ともに更新する

また、保存時の全般オプションで「読み取り専用を推奨する」にチェックが入っている場合の「保存する必要がなければ、読み取り専用で開いてください。」のメッセージは、
引数IgnoreReadOnlyRecommendedをFalseに指定することで表示されなくなります。

サンプルコード



Sub メッセージ表示しないでブックを開く()

Dim strFilePath As String
Dim strFileName As String

strFilePath = ThisWorkbook.Path & "¥"
strFileName = "Book1.xls"

Workbooks.Open _
 Filename:=strFilePath & strFileName, UpdateLinks:=1, IgnoreReadOnlyRecommended:=False

End Sub

<< 1 2


Powered by Seesaa
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。