Excel VBA ファイル操作

Excel VBA のトップに戻る
Excel VBA 目次

索引

ADO接続して Excel ブックを更新

 Excel2007〜、Excelの仕様が変わって、一つのシートに登録できる最大行数が、1,048,576行にまで拡大しました。
(〜Excel2003 は、最大65,536行)
 このため、Excelブックをデータベースのようにして使うケースが出てきました。

 Excelのシートの行数が多いと、VBA で取り扱う時に、Range オブジェクトで取得したり、配列を使って更新しようとすると、「メモリ不足」となって処理できない場合があります。
 このため、ここでは、Excelブックを開かずに、ADO 接続して、直接シートの値を更新するプログラムを紹介します。
 当然のことながら、このマクロは、Excel2007〜がインストールされているパソコンでしか動きません。

 このマクロは、下記で公開していただいている情報を参考にさせていただきました。

 Visual Basic または VBA から ADO を Excel データで使用する
http://support.microsoft.com/kb/257819/ja

 いろいろ備忘録日記 の ADO.NET入門記-027
(Excelに接続してデータを取得・追加・更新)(Excel,OleDb,HDR,Extended Properties,xlsx)
http://d.hatena.ne.jp/gsf_zero1/20091116/p1

 ADO を使用して Excel ブックのデータの読み取りおよび書き込みを行う方法 (ExcelADO)
http://support.microsoft.com/kb/278973/ja

 Excel 2010(X64) VBA 他のBOOKを開かずにDataを読み込む時、最終のDataの認識の仕方
http://social.msdn.microsoft.com/Forums/en-US/704065b0-ae18-4dd8-b3c4-2c6e9721cd6c/excel-2010x64-vba-bookdatadata?forum=vbajp

 このマクロと、サンプル・データをダウンロードできます。
UseAOD00.zip

 ADO を使うためには、参照設定を追加する必要が有ります。
 VBE の、[ツール(T)]→[参照設定(R)]を選択します。
 表示される、参照設定用のダイアログボックスで、
「Microsoft ActiveX Data Objects *.* Library」にチェックを入れて、[OK]ボタンを押します。
 (注)"*.*"のところは、ADOのバージョンです。新しいものにすると、古いバージョンの Office では動かなくなるので、2.6 あたりを選択すると良いでしょう。


Option Explicit
Option Base 1

Dim 対象ファイル名 As String   'フルパス
Dim 処理中ファイル名 As String 'フルパス
Dim シート名 As String
Dim マスタ配列 As Variant
Dim 最終行 As Long
Dim 処理行 As Long

Const cnsProvider = "Microsoft.ACE.OLEDB.12.0"
Const cnsExtProp = "Extended Properties"
Const cnsExcel = "Excel 12.0;HDR=YES"


'*******************************************************************************
' シートをテーブルとして内容を更新する
'*******************************************************************************
Sub ADO_WS_TEST2()
   Dim ADODB接続 As ADODB.Connection
   Dim ADODBレコードセット As ADODB.Recordset
   Dim SQL文 As String
   Dim 金額 As Long
   Dim 最終行 As Integer
   Dim 修正データ配列() As Variant
   Dim 修正品目索引 As Object        'Scripting.Dictionary オブジェクト
   
   ThisWorkbook.Worksheets("Sheet1").Activate
   対象ファイル名 = Range("B1").Value
   対象ファイル名 = ThisWorkbook.Path & "\" & 対象ファイル名 ' フルパスに変更
   シート名 = Range("B2").Value
   
   '修正単価データを読み込む
   最終行 = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
   If 最終行 > 20 Then
      修正データ配列 = Range("A21").Resize(最終行 - 20, 2).Value
      
      Set 修正品目索引 = CreateObject("Scripting.Dictionary")    '★連想配列の定義

      For 処理行 = 1 To 最終行 - 20
         修正品目索引(修正データ配列(処理行, 1)) = 処理行
      Next 処理行
   End If
   
   Call 排他制御
   
   
   ' Connection生成
   Set ADODB接続 = New ADODB.Connection
   With ADODB接続
       .Provider = cnsProvider
       .Properties(cnsExtProp) = cnsExcel
       .Open 処理中ファイル名  'フルパス
   End With
   
   SQL文 = "SELECT * FROM [" & シート名 & "$]"
   ' RecordSet取得(更新用)
   Set ADODBレコードセット = New ADODB.Recordset
   
   ADODBレコードセット.Open SQL文, ADODB接続, adOpenKeyset, adLockOptimistic

    
   ' 一行ずつ読み出して、更新する
   Do Until ADODBレコードセット.EOF
      
      '修正単価がある場合
      If 最終行 > 20 Then
        If 修正品目索引.Exists(ADODBレコードセット.Fields("品目番号").Value) = True Then
            ADODBレコードセット.Update "単価", 修正データ配列(修正品目索引(ADODBレコードセット.Fields("品目番号").Value), 2)
            ADODBレコードセット.Update "備考", "単価修正"
         End If
      End If
      
      ' 金額と数量を取得
      If IsNull(ADODBレコードセット.Fields("単価").Value) = False Then
         If IsNull(ADODBレコードセット.Fields("数量").Value) = False Then

            ' 金額を計算
            金額 = WorksheetFunction.Round(ADODBレコードセット.Fields("単価").Value _
            * ADODBレコードセット.Fields("数量").Value, 0)
      
            ' 金額を更新
            ADODBレコードセット.Update "金額", 金額
         Else
            ' 備考を更新
             ADODBレコードセット.Update "備考", ADODBレコードセット.Fields("備考").Value & " 数量未登録"
         End If
      Else
         ' 備考を更新
         ADODBレコードセット.Update "備考", "単価未登録"
      End If
      '次の行に進む
      ADODBレコードセット.MoveNext
   Loop

   ' 終了処理
    
   ADODBレコードセット.Close
   Set ADODBレコードセット = Nothing
   ADODB接続.Close
   Set ADODB接続 = Nothing
   
   Name 処理中ファイル名 As 対象ファイル名
   
   MsgBox "更新を終了しました!"
    
End Sub

 解説:
 WorksheetFunction.Round 関数は、数値を四捨五入して指定された桁数にします。
 書式
 ROUND(数値,桁数)
 数値 四捨五入の対象となる数値を指定します。
 桁数 数値を四捨五入した結果の桁数を指定します。

 使用例
数式 説明 (計算結果)
=ROUND(2.15, 1) 2.15 を小数点第 1 位に四捨五入します (2.2)
=ROUND(2.149, 1) 2.149 を小数点第 1 位に四捨五入します (2.1)
=ROUND(-1.475, 2) -1.475 を小数点第 2 位に四捨五入します (-1.48)
=ROUND(21.5, -1) 21.5 を小数点の左側 1 桁の 1 の位で四捨五入します (20)

注:
 VBAのRound関数は、「銀行型丸め」または、「銀行丸め」と呼ばれる、特殊な四捨五入をします。
 「銀行丸め」は、最近接偶数への丸め (round to the nearest even; RN) とも言われ、端数が0.5より小さいなら切り捨て、端数が0.5より大きいならは切り上げ、端数がちょうど0.5なら切り捨てと切り上げのうち結果が偶数となる方へ丸めます。
 JIS Z 8401で規則Aとして定められていて、規則B(四捨五入)より「望ましい」とされています。

 上の例では、普通の四捨五入にするために、Excelの関数を使っています。

 比較:
データ切り捨て切り上げ四捨五入銀行丸め
11.411121111
11.511121212
11.611121212
12.412131212
12.512131312
12.612131313

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

ファイルを排他制御する

 上のマクロのように、他のファイルを扱う場合は、対象ファイルが開かれていない状態で行う必要があります。
 また、対象ファイルを更新しているときに、誰かに開かれないようにする必要があまります。

 ここでは、対象ファイルを排他制御するマクロを紹介します。

 このコードは、
 Excel97 で排他制御をする方法
http://support.microsoft.com/default.aspx?scid=kb;ja;408062
と、吉岡 照雄さんの、
 ファイルやフォルダを他者が使用してないときだけ排他使用(ロック)するVBScript
http://www.vector.co.jp/soft/win95/util/se373378.html
のアイデアを使わせていただきました。


Private Sub 排他制御()

   Dim MyFile As String
   
   On Error GoTo ErrorTrap
   'ファイルがあるか確認
   MyFile = Dir(対象ファイル名) 'フルパスでチェック
   If MyFile = "" Then
       MsgBox "指定したファイルはありません"
   Else
      '書き込み専用でオープン
      Open 対象ファイル名 For Binary Lock Read Write As #1
      Close #1
   
      処理中ファイル名 = ThisWorkbook.Path & "\使用中のため開かないでね.xlsx"
      Name 対象ファイル名 As 処理中ファイル名

   End If
   
   Exit Sub
    
ErrorTrap:
   MsgBox Err & Error(Err)
   MsgBox "このファイルは使用中です"
   End
End Sub

 解説:
Open ステートメント は、ファイルを開いて、ファイルへ入出力できるようにします。
 構文
 Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]

 Open ステートメントの構文は、次の指定項目から構成されます。
指定項目 内容
pathname 必ず指定します。文字列式にはファイル名を指定します。フォルダ名、またはドライブ名も含めて指定できます。
mode 必ず指定します。ファイル モードを示す、次のいずれかのキーワードを指定します。
 AppendBinaryInputOutput、または Random
 省略すると、ファイルはランダム アクセス モードで開かれます。
Access 省略可能です。開くファイルに対して行う処理を示す、次のいずれかのキーワードを指定します。
 ReadWrite、または Read Write
lock 省略可能です。開くファイルに対する、他のプロセスからのアクセスを制御する、次のいずれかのキーワードを指定します。
 SharedLock ReadLock Write、または Lock Read Write
filenumber 必ず指定します。1 〜 511 の範囲で任意のファイル番号を指定します。
FreeFile 関数を使用して次に使用可能なファイル番号を取得してください。
reclength 省略可能です。32,767 バイト以下の数値を指定します。
ランダム アクセス ファイルの場合は、レコード長を表します。
シーケンシャル ファイルの場合は、バッファの容量を表します。

 入出力処理を行うときには、その対象となるファイルを開く必要があります。
Open ステートメントはファイルに入出力のためのバッファを割り当て、バッファに対するアクセス モードを決定します。

 引数 pathname に指定したファイルが存在しない場合、ファイルを開くときに追加モード (Append)、バイナリ モード (Binary)、出力モード (Output)、またはランダム アクセス モード (Random) のいずれかのモードが指定されている場合、新規に作成されて開きます。

 引数 access は、開いたファイル上で行う操作を指定するキーワードです。
ファイルが別のプロセスのために既に開かれていて、指定したアクセスが実行できないとき、Open ステートメントはファイルを開くことができず、エラーが発生します。

 Len 節および引数 reclen は、引数 modeBinary を指定した場合は無視されます。
シーケンシャル ファイルの場合、シーケンシャル ファイル内のレコード サイズは一定でないため、引数 reclen の値はそれぞれのレコードのサイズと一致している必要はありません。

 重要
 バイナリ モード、入力モード、およびランダム アクセス モードでは、ファイルを開いたまま、別のファイル番号で同時に開くことができます。
一方、追加モードと出力モードでは、いったんファイルを閉じないと、別のファイル番号で再び開くことはできません。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

CSV項目のデータ形式を指定して読み込んでExcelブックとして保存する

 Excelで、CSV ファイルを読み込もうとすると、列のデータ形式は、自動で設定されてしまいます。
 このため、0で始まる数字列を、文字列として読み込みたい時にも、0 を除外した数字になってしまいます。

 すぐに役立つエクセルVBAマクロ集
CSVファイルを文字列として開きたいが?
http://www.max.hi-ho.ne.jp/happy/YNxv914.html

 このマクロは、CSV を Excel に読み込むときの列名毎に、データ形式を指定して読み込めるようにするものです。

データ形式を指定する
 Excel に、テキスト・ファイルを読み込むときには、ウィザードが開いて、項目毎にデータ形式を指定することができます。
 このマクロでは、前処理として、CSV ファイルを、TXTファイルに拡張子を変えて保存しています。txt ファイルとして読み込むことによって、データ形式を選択できるようにするためです。

このマクロをダウンロードできます。
CSV_SaveAsExcelVBA00.xls


Option Base 1

Sub CSVの拡張子をTEXに変更してテキスト読み込み()

   Dim CSVファイル名 As String
   Dim CSVファイルパス As String
   Dim 保存フォルダパス As String
   Dim TXTファイルパス As String
   Dim 保存フォルダ As String
   Dim 開始日時 As Variant
   Dim 終了日時 As Variant
   Dim 列項目数 As Integer
   Dim 最終行 As Integer
   Dim 列項目配列() As Variant 'CSV のデータ項目とデータ形式を指定する配列
   Dim 処理項目数 As Integer
   Dim XlColumnDataType列挙 As Integer
   Dim データ形式 As String
   Dim 保存Excelブック名 As String
   Dim 保存フォルダのレベル As Integer
   Dim パス As String
   Dim レベル As Integer


   開始日時 = Now 'マクロの開始時刻を変数に格納

   ThisWorkbook.Worksheets("Sheet1").Activate 'パラメータを読み込むシートを開く

   CSVファイル名 = Range("D7").Value

   保存Excelブック名 = Left(CSVファイル名, InStrRev(CSVファイル名, ".") - 1)

   保存フォルダパス = Range("D8").Value

   最終行 = Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row 'B列の最終セルで行数をカウント
   列項目数 = 最終行 - 10
   ReDim 列項目配列(列項目数) 'CSV のデータ項目とデータ形式を指定する配列

   For 処理項目数 = 1 To 列項目数

      データ形式 = Range("B11").Cells(処理項目数, 1).Value

      Select Case データ形式
         Case "G/標準"
            XlColumnDataType列挙 = 1
         Case "文字列"
            XlColumnDataType列挙 = 2
         Case "日付"
            XlColumnDataType列挙 = 5
         Case Else
            Stop
            MsgBox "データ形式エラー"
      End Select

      列項目配列(処理項目数) = Array(処理項目数, XlColumnDataType列挙)
   Next 処理項目数

   CSVファイルパス = ThisWorkbook.Path & "\" & CSVファイル名

   'CSVファイルを、拡張子txt に変更してコピー
   TXTファイルパス = Left(CSVファイルパス, InStrRev(CSVファイルパス, ".")) & "txt"

   FileCopy CSVファイルパス, TXTファイルパス

   'TXT ファイルを、Excelに読み込む
   Workbooks.OpenText _
   Filename:=TXTファイルパス _
   , Origin:=932 _
   , StartRow:=1 _
   , DataType:=xlDelimited _
   , TextQualifier:=xlDoubleQuote _
   , ConsecutiveDelimiter:=False _
   , Tab:=False _
   , Semicolon:=False _
   , Comma:=True _
   , Space:=False _
   , Other:=False _
   , FieldInfo:=列項目配列 _
   , TrailingMinusNumbers:=True


   ' 保存フォルダパスがなければ作る
   保存フォルダのレベル = 出現回数(保存フォルダパス, "\\")

   For レベル = 1 To 保存フォルダのレベル
      If レベル < 保存フォルダのレベル Then
         パス = Left(保存フォルダパス, 出現位置(保存フォルダパス, "\\", レベル + 1))
      Else
         パス = 保存フォルダパス
      End If

      If Dir(パス, vbDirectory) = "" Then
          MkDir パス
      End If
   Next レベル

   Application.DisplayAlerts = False '変更を保存しますか? のメッセージを表示させない
   'Excelブックとして保存する
   ActiveWorkbook.SaveAs _
      Filename:=保存フォルダパス & "\" & 保存Excelブック名 & ".xls" _
      , FileFormat:=xlWorkbookNormal
   ActiveWorkbook.Close

   Application.DisplayAlerts = True

   '作業用に作成した拡張子 txt のファイルを削除
   Kill TXTファイルパス

   終了日時 = Now

   MsgBox "処理時間は、" _
   & Format(終了日時 - 開始日時, "hh時間nn分ss秒") & " でした。"

End Sub

 解説:
 Workbooks.OpenText メソッド は、テキスト ファイルを分析して読み込みます。
テキスト ファイルを 1 枚のシートとして、それを含む新しいブックを開きます。

 構文
 .OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

    Workbooks オブジェクトを表す変数です。

名前必須/オプションデータ型説明
Filename必須文字列型 (String)読み込まれるテキスト ファイルの名前を指定します。
Originオプションバリアント型 (Variant)テキスト ファイルの元のプラットフォームを指定します。
使用できる定数は、XlPlatform クラスの xlMacintoshxlWindowsxlMSDOS のいずれかです。
また、目的のコード ページのコード ページ番号を表す整数値を指定することもできます。たとえば、"1256" の場合、ソース テキスト ファイルのエンコードは "アラビア語 (Windows)" であることを示します。
この引数を省略すると、このメソッドは、テキスト インポート ウィザードの [元のファイル] の現在の設定値を使用します。
StartRowオプションバリアント型 (Variant)取り込み開始行を指定します。最初の行を 1 として数えます。既定値は 1 です。
DataTypeオプションバリアント型 (Variant) ファイルに含まれるデータの形式を指定します。
使用できる定数は、XlTextParsingType クラスの xlDelimited または xlFixedWidth です。
この引数を省略すると、ファイルを開いたときにデータの形式が自動的に決められます。
TextQualifierオプションXlTextQualifier文字列の引用符を指定します。
ConsecutiveDelimiterオプションバリアント型 (Variant)連続した区切り文字を 1 文字として扱うときは True を指定します。
既定値は False です。
Tabオプションバリアント型 (Variant)引数 DataTypexlDelimited を指定し、区切り文字にタブを使うときは True を指定します。
既定値は False です。
Semicolonオプションバリアント型 (Variant)引数 DataTypexlDelimited を指定し、区切り文字にセミコロン (;) を使うときは True を指定します。
既定値は False です。
Commaオプションバリアント型 (Variant)引数 DataTypexlDelimited を指定し、区切り文字にコンマ (;) を使うときは True を指定します。
既定値は False です。
Spaceオプションバリアント型 (Variant)引数 DataTypexlDelimited を指定し、区切り文字にスペースを使うときは True を指定します。
既定値は False です。
Otherオプションバリアント型 (Variant)引数 DataTypexlDelimited を指定し、区切り文字に OtherChar で指定した文字を使うときは True を指定します。
既定値は False です。
OtherCharオプションバリアント型 (Variant)引数 OtherTrue のときは、必ずこの引数に区切り文字を指定します。
複数の文字を指定したときは、先頭の文字が区切り文字となり、残りの文字は無視されます。
FieldInfoオプションバリアント型 (Variant)各列のデータ形式を示す配列を指定します。データ形式の解釈は、引数 DataType の値によって異なります。
データが区切り記号で区切られている場合は、この引数に 2 要素配列の配列を使用して、配列内の各 2 要素配列が特定の列の変換オプションを指定するようにします。
1 番目の要素には 1 から始まる列番号を指定し、2 番目の要素には列のデータ形式を示す XlColumnDataType クラスの定数を指定します。
TextVisualLayoutオプションバリアント型 (Variant)テキストの視覚的な配置を指定します。
DecimalSeparatorオプションバリアント型 (Variant)Excel で数値を認識する場合に使う小数点の記号です。
既定はシステム設定です。
ThousandsSeparatorオプションバリアント型 (Variant)Excel で数値を認識する場合に使う桁区切り記号です。
既定はシステム設定です。
TrailingMinusNumbersオプションバリアント型 (Variant)末尾に負符号が付く数値を負の数値として扱う場合は、True を指定します。
False を指定するか、引数を省略した場合、末尾に負符号が付く数値は文字列として扱われます。
Localオプションバリアント型 (Variant)区切り記号、数値、およびデータの書式にコンピュータの地域設定を使用する場合は、True を指定します。

XlTextParsingType 列挙型メンバ
メンバ名説明
xlDelimited既定値。区切り文字によってファイルが区切られます。
xlFixedWidthファイルのデータが固定幅の列に配置されます。

XlTextQualifier クラス
xlTextQualifierDoubleQuote (既定値)
xlTextQualifierNone
xlTextQualifierSingleQuote

 備考
 FieldInfo パラメータ情報
 定数 xlEMDFormat は、簡易字中国語サポートがインストールおよび選択されている場合にのみ使用できます。
定数 xlEMDFormat は、日付形式に台湾の元号が使用されていることを指定します。
 列の指定は、どのような順番でもかまいません。指定されなかった列は、標準の形式だと解釈されます。

注:

 次の使用例は、3 番目の列を "月/日/年" (01/10/1970 など) の形式で解析し、1 番目の列をテキストとして解析し、ソース データの残りのデータを標準書式として解析します。
Array(Array(3, 3), Array(1, 2))

 引数 DataTypexlFixedWidth の場合 (データが固定長で区切られている場合) は、各 2 要素配列の 1 番目の要素に、列のどの位置から処理が行われるかを 0 から始まる整数で指定します。
2 番目の要素には列のデータ形式を 0 〜 9 の数値で指定します。

 ThousandsSeparator パラメータ情報
 次の表は、Excel へのテキストのインポートをさまざまなインポート設定で行った場合の結果を示したものです。数値の結果は右詰めで表示します。
システムの小数点の記号 システムの桁区切り記号 小数点の記号の値 桁区切りの記号の値 インポートするテキスト セルの値 (データ型)
ピリオド カンマ カンマ ピリオド 123,123.45 123,123.45 (数値)
ピリオド カンマ カンマ カンマ 123,123.45 123.123,45 (文字列)
カンマ ピリオド ピリオド カンマ 123,123.45 123,123.45 (数値)
ピリオド カンマ ピリオド カンマ 123,123.45 123 123.45 (文字列)
ピリオド カンマ ピリオド スペース 123,123.45 123,123.45 (数値)


 次の使用例は、Data.txt というテキスト ファイルを、タブを区切り文字として分析し、ワークシートに変換します。
Workbooks.OpenText filename:="DATA.TXT", _
    dataType:=xlDelimited, tab:=True

XlColumnDataType 列挙 とは、列が解析される方法を指定します。
名前説明
xlDMYFormat4DMY 日付形式
xlDYMFormat7DYM 日付形式
xlEMDFormat10EMD 日付形式
xlGeneralFormat1一般形式
xlMDYFormat3MDY 日付形式
xlMYDFormat6MYD 日付形式
xlSkipColumn9列は解析されません。
xlTextFormat2テキスト形式
xlYDMFormat8YDM 日付形式
xlYMDFormat5YMD 日付形式


Kill ステートメント は、ディスクからファイルを削除します。
構文Kill pathname
 引数 pathname は必ず指定します。引数 pathname には、1 つまたは複数の削除するファイル名を示す文字列式を指定します。フォルダおよびドライブを含めて指定できます。

 Windows の場合、Kill ステートメントでは、複数のファイルを指定するための "*" (アスタリスク) および "?" (疑問符) のワイルドカード文字を使用できます。
 Kill ステートメントを使って、開いているファイルを削除しようとすると、エラーが発生します。

 注: フォルダを削除するには、RmDir ステートメントを使用してください。

 次の例は、Kill ステートメントを使って、ファイルをディスクから削除します。
' ファイル TESTFILE には、データが含まれているものと仮定します。
Kill "TestFile"                ' ファイルを削除します。

' 現在のフォルダにあるすべての *.TXT ファイルを削除します。
Kill "*.TXT"

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


ホームページのトップに戻る