ExcelVBA ～「ADO＋SQL＋CopyFromRecordset」と「Worksheet.Openメソッド+代入等々」のどちらが高速か？のテスト。（10行10列、10万行40列）

ExcelVBA ～ ExcelVBA ～「ADO＋SQL＋CopyFromRecordset」と「Worksheet.Openメソッド+代入等々」のどちらが高速か？のテスト。（10行10列、10万行40列）
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
サンプルダウンロード
ダウンロードしたら、読み込み先データのサンプルの中のファイルを好きな場所に置き、プログラムをそのパス（あるいはファイル名）に書き変えて、実行、テストします。

基本的には（吸い込むセル範囲が数百行とかそれをほど大きくなければ）、
「ADO＋SQL＋CopyFromRecordset」を使うほうが、ファイルを開かないせいか、
圧倒的に速いです。
「Worksheet.Openメソッド+代入等々」を使うよりも。
（Openメソッドは非表示にしたとしても「ファイルを開いてしまう」のでその分、遅くなります。例えば100ファイルとか読み込みたい時は遅くなります。）

ただし、もちろん、Openメソッドのほうが都合がいいこともあると思います。
適材適所で。

※補足
なお、「ODBC＋SQL＋QueryTableオブジェクト」の場合は、10万件のテストでは、
「ADO＋SQL＋CopyFromRecordset」よりも、2秒ほど遅かったです。
QueryTableオブジェクトで受けるメリットが大きい場合は、
それくらいの遅さなら、「ODBC＋SQL＋QueryTableオブジェクト」を使うのが
いいかもです。

　　
なお、「シートの部分的なセル範囲を吸い込みたい」という場合は、
いったん、「ADO+CopyFromRecordset」で表全体を吸い込んでおいてから、
吸い込んだデータから任意のセル範囲をRangeで指定して、
別のシートに転記するほうが速いかも？しれません。
（コピペだと遅いので、Variant型の変数にセル範囲の値を一括で代入して別シートに貼り付ける、などで。）

転記元（＝ソース）のシートのレイアウトがグッチャグチャだったり、１シート内に表がいくつも含まれてしまっている、とか、クロス集計表やリスト表がごちゃ混ぜ、、、ような場合は、Worksheet.Openのほうが便利かもです。
事前に実験してみて、SQLでシート全体が読める場合なら、いったん「ADO+CopyFromRecordset」で表全体を吸い込んでおいてから、吸い込んだデータから任意のセル範囲をRange＋Variant配列で転記、が高速かも？しれません。

　　
★★★★ 比較結果 ★★★★
（読み込むデータが、10列10行の場合。10万件40列の場合はまた別。
　0万件40列の場合は「ADO＋SQL＋CopyFromRecordset」のほうが
　１～１．５秒ほど早かったです。
　つまり、「方法02WS_OPEN」が意外と頑張ってた。）

●「ADO＋SQL＋CopyFromRecordset」を使った場合。
１回目 0.48046875 秒
２回目 0.078125 秒
３回目以降 2回目と変わらず。高速なまま。

●「Worksheet.Openメソッド+代入等々」を使った場合。
１回目 0.6015625 秒
２回目 0.57421875 秒
３回目以降 2回目とほぼ変わらず。遅いまま。

※ノートPCにて、AC電源につないでない結果です。
　ですので、つなぐと、両者とも１回目がもう少し速くなります。
　ただし、「方法01ADO」の2回目の速さは、
　AC電源につないでもつながなくても同様に高速でした。

　　
●相手のファイルを開くことなく、ADOでシートを吸い込むコード
（「ADO＋SQL＋CopyFromRecordset」）

基本、Worksheet.Openメソッドよりは高速。
（ただし、10万件以上とかになると、そのときそのときで違うかも。
　速いには速いけど、差が縮まる、とか。）

'
'

Option Explicit

'比較結果
'（読み込むデータが、10列10行の場合。10万件40列の場合はまた別。
'　「方法01ADO」のほうが１～１．５秒ほど早かったです。
'　つまり、「方法02WS_OPEN」が意外と頑張ってた。）

'方法01ADO     １回目   0.48046875
'方法01ADO     ２回目   0.078125
'方法01ADO     ３回目以降 2回目と変わらず高速。


'方法02WS_OPEN １回目   0.6015625
'方法02WS_OPEN ２回目   0.57421875
'方法02WS_OPEN ３回目以降 2回目とほぼ変わらず。

'※ノートPCにて、AC電源につないでない結果です。
'　ですので、つなぐと、両者とも１回目がもう少し速くなります。
'　ただし、「方法01ADO」の2回目の速さは、
'　AC電源につないでもつながなくても同様に高速でした。



Sub ReadByADO01_CopyFromRsPast0002()


Dim startTime As Double
Dim endTime As Double
Dim processTime As Double

'開始時間取得
startTime = Timer



    Dim Cn              As ADODB.Connection
    Dim Rs              As ADODB.Recordset
    Dim o_SrcWb01       As Workbook
    Dim s_SrcWbFlPath   As String
    Dim s_SrcWsNm       As String
    Dim s_SQL01         As String
    Dim o_DistWs01      As Worksheet
    Dim i               As Integer
    Dim j               As Integer
    Dim s_DistCellAddr01 As String


    Application.ScreenUpdating = False

 
    '★以下、メインプログラムです。


'    s_SrcWbFlPath = "D:\1\10man40c.xlsx"
    s_SrcWbFlPath = "D:\1\10r10c.xlsx"
    '◆◆◆◆◆◆ 相手（読み込先）のExcelのフルパスを指定
 
 
    Set o_DistWs01 = ThisWorkbook.Worksheets.Item("Sheet1")
    '◆◆◆◆◆◆ 読み込んだデータを転記する先（自ファイル）のシートを指定
    
     s_SrcWsNm = "SheetA" & "$"
     '◆◆◆◆◆◆ ソースとなる相手先（読み込み先）のシートの名前を指定。
     '            「& "$" 」は絶対に消さないこと。あとで必要なので。
     
     
    
    Set Cn = New ADODB.Connection
    Set Rs = New ADODB.Recordset
 
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & s_SrcWbFlPath & ";" & _
              "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=False"""
    
            
    '▼転記先のシートの内容をいったんクリア
    '　※転記先のシートが一番左にあることが前提です。
    o_DistWs01.Cells.ClearContents
          
    s_SQL01 = "SELECT * FROM [" & s_SrcWsNm & "]"

     
    Rs.Open s_SQL01, Cn, adOpenStatic, adLockOptimistic, adCmdText
    
    
    '列名だけを書き込み
    For j = 1 To Rs.Fields.Count
        o_DistWs01.Cells(1, j) = Rs.Fields(j - 1).Name
    Next j
    
    
    '実データ（今回テストは10万行40列分）の貼り付け
    s_DistCellAddr01 = "A2"
    o_DistWs01.Range(s_DistCellAddr01).CopyFromRecordset Rs
    
      
    Rs.Close
    Set Rs = Nothing
    
    Cn.Close
    Set Cn = Nothing
    
   
    Application.ScreenUpdating = True
       
 '終了時間取得
endTime = Timer

'処理時間計算
processTime = endTime - startTime
Debug.Print processTime

 End Sub



'#####################################################
'以下、コメントつきの、モトとなったコード。
'#####################################################

Sub ReadByADO01_CopyFromRsPast0001()
 
 
   '★変数設定
    
    Dim Cn              As ADODB.Connection
    '読込対象のXLSファイルの
    '「ADO接続文字用のオブジェクト」用の変数
    
    Dim Rs              As ADODB.Recordset
    '読込対象のシートの
    '「ADOデータ範囲？設定用のオブジェクト
    '　＝レコードセット」用の変数。
        
    Dim o_SrcWb01      As Workbook
    '読込対象のブック
    'を格納（＝指定）するためのオブジェクト変数
 
    Dim s_SrcWbFlPath   As String
    '読込対象のXLSファイルの
    'フルパス格納用の変数
    
    Dim s_SrcWsNm       As String
    '読込対象のシート名の格納用の変数
    
    Dim s_SQL01         As String
    'どの列と行をどのように読み込むかの
    'SQL文を格納するための変数
        
    Dim o_DistWs01      As Worksheet
    '読み込んだデータを転記する先のシート
    'を格納（＝指定）するためのオブジェクト変数
    
    Dim i               As Integer
    'ソースのシートのインデックスを
    '格納（＝指定）するための変数
 
    Dim j                As Integer
    'レコードセットの列のインデックスを
    '格納（＝指定）するための変数
    
    Dim s_DistCellAddr01 As String
    '2つ目以降のソースシートを読み込んだ際に
    'それを転記先シートに書き込むときの、
    'その起点となるセルのアドレスを格納するための変数
    
    
    '▼一応、データが大量だと遅くなってしまうので
    '　画面描画をいったん停止。
    '　デバッグ時などはコメントアウトする。
 
    Application.ScreenUpdating = False
    'Excelの画面描画をいったん停止。
    '今の画面のまま変化させないようにする。
     
 
 
    '★以下、メインプログラムです。
 
'        Set o_SrcWb01 = ThisWorkbook
'        'データを読み込みたい、そのソースとなるブックの設定
'    Set o_SrcWb01 = Workbooks.Open("D:\1\縦結合テスト.xlsm")
'    Set o_SrcWb01 = Workbooks.Open("\\Fsvr01\Fld01\test.xlsm")
 
 
'    s_SrcWbFlPath = o_SrcWb01.FullName
'    'そのブックのフルパスを取得。
'    '（以下のような指定も可。）
    s_SrcWbFlPath = "D:\1\10man40c.xlsx"
    '◆◆◆◆◆◆ 読み込先のExcelのフルパスを指定
 
 
    Set Cn = New ADODB.Connection
    'ADOにてのデータ接続（＝ファイル内容の読み込み）
    'ができるようにするための準備
    
    
    Set Rs = New ADODB.Recordset
    'ADOにてのレコードセット（＝シート内データ範囲？）が
    '作れるようにするための準備
 
 
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & s_SrcWbFlPath & ";" & _
              "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=False"""
    'ADOで読込先のソースファイルに接続。
    '（＝読込先のソースファイルを開く）
    
        
    
    '▼転記先のシートの内容をいったんクリア
    '　※転記先のシートが一番左にあることが前提です。
    
    Set o_DistWs01 = ThisWorkbook.Worksheets.Item("Sheet1")
    '◆◆◆◆◆◆ 読み込んだデータを転記する先（自ファイル）のシートを指定
         
    o_DistWs01.Cells.ClearContents
    '読み込み先のファイルの列数や行数が増減するといけないので、
    '一応、転記するシートをきれいに空白しておく。
    '前回よりデータ列数や行数が少ないと、
    '前回のデータが残ってしまうので。
                                                            
                                                            
     s_SrcWsNm = "SheetA" & "$"
     '◆◆◆◆◆◆ ソースとなる相手先（読み込み先）のシートの名前を指定
     
     s_SQL01 = "SELECT * FROM [" & s_SrcWsNm & "]"
     'SQL文の作成
     
     Rs.Open s_SQL01, Cn, adOpenStatic, adLockOptimistic, adCmdText
     'SQL文に基づいてレコードセットを生成
        '「レコードセット＝データの入力された範囲？」を
        '決定しつつ。
 
        '▼SQL文にて、ソースシートからどの列と行をどのように読み込むかを設定。
        '基本、列名の書き換えだけでOKだと思います。
        'ただし、実際のソースシートの列名の末尾に、スペースなどが
        '隠れていないことが前提です。
        
        'なお、セル範囲を指定しない場合は、
        'ソースシート丸ごとのすべての範囲を見に行ってくれて、
        'データが入力されている部分を自動判別してくれて、
        'レコードセットを作ってくれます。
        's_SQL01 = "SELECT * FROM [" & s_SrcWsNm & "A1:N15" & "]"
        'このようにシート名のあとにセル範囲を追記指定することも可能。
        'そこまでの範囲を見に行って、その範囲内で、
        'データが入力されている部分を自動判別してくれて、
        'レコードセットを作ってくれます。
     
     s_DistCellAddr01 = "A2"
     '出力先のセルの番地の指定
     '列名はレコードセットの中には含まれていないので
     'ループで書き出さないといけないので、データのみの
     '起点セルを指定します。
     
     
     For j = 1 To Rs.Fields.Count
         o_DistWs01.Cells(1, j) = Rs.Fields(j - 1).Name
     Next j
     '列名のみを、シートへの転記
     '「HDR=Yes」でデータをレコードセットの取り込んだので、
     '列名は、Rsを貼り付けても貼り付かないため。
        
        
     o_DistWs01.Range(s_DistCellAddr01).CopyFromRecordset Rs
        'CopyFromRecordset にて、転記先のシートに
        'レコードセットの内容を書き出す。
        '「HDR=NO」だと、列名もシートに書き出されるが、
        '「HDR=Yes」はレコードのみとなる。
        ' なお、「HDR=NO」だと、転記後にセルに緑の三角エラーが
        ' 出るが「HDR=Yes」のときは出ない。
        ' 今回は、「HDR=Yes」でやっている。
 
 
    
    
    Rs.Close
    Set Rs = Nothing
'    ' 作ったオブジェクトの破棄：
'    '一応メモリリークを起こさないように？
'    '作ったオブジェクトをメモリから消す
    
    
    Cn.Close
    Set Cn = Nothing      ' 同上
    
    
    
    '▼Excelの画面描画を再開｡
    
    Application.ScreenUpdating = True
    '画面の変化が普通に表示されるようにする。
   
    
 End Sub



'
'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

Option Explicit

'比較結果

'（読み込むデータが、10列10行の場合。10万件40列の場合はまた別。

'　「方法01ADO」のほうが１～１．５秒ほど早かったです。

'　つまり、「方法02WS_OPEN」が意外と頑張ってた。）

'方法01ADO １回目 0.48046875

'方法01ADO ２回目 0.078125

'方法01ADO ３回目以降 2回目と変わらず高速。

'方法02WS_OPEN １回目 0.6015625

'方法02WS_OPEN ２回目 0.57421875

'方法02WS_OPEN ３回目以降 2回目とほぼ変わらず。

'※ノートPCにて、AC電源につないでない結果です。

'　ですので、つなぐと、両者とも１回目がもう少し速くなります。

'　ただし、「方法01ADO」の2回目の速さは、

'　AC電源につないでもつながなくても同様に高速でした。

Sub ReadByADO01_CopyFromRsPast0002()

Dim startTime As Double

Dim endTime As Double

Dim processTime As Double

'開始時間取得

startTime = Timer

Dim Cn As ADODB.Connection

Dim Rs As ADODB.Recordset

Dim o_SrcWb01 As Workbook

Dim s_SrcWbFlPath As String

Dim s_SrcWsNm As String

Dim s_SQL01 As String

Dim o_DistWs01 As Worksheet

Dim i As Integer

Dim j As Integer

Dim s_DistCellAddr01 As String

Application.ScreenUpdating = False

'★以下、メインプログラムです。

' s_SrcWbFlPath = "D:\1\10man40c.xlsx"

s_SrcWbFlPath = "D:\1\10r10c.xlsx"

'◆◆◆◆◆◆ 相手（読み込先）のExcelのフルパスを指定

Set o_DistWs01 = ThisWorkbook.Worksheets.Item("Sheet1")

'◆◆◆◆◆◆ 読み込んだデータを転記する先（自ファイル）のシートを指定

s_SrcWsNm = "SheetA" & "$"

'◆◆◆◆◆◆ ソースとなる相手先（読み込み先）のシートの名前を指定。

' 「& "$" 」は絶対に消さないこと。あとで必要なので。

Set Cn = New ADODB.Connection

Set Rs = New ADODB.Recordset

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & s_SrcWbFlPath & ";" & _

"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=False"""

'▼転記先のシートの内容をいったんクリア

'　※転記先のシートが一番左にあることが前提です。

o_DistWs01.Cells.ClearContents

s_SQL01 = "SELECT * FROM [" & s_SrcWsNm & "]"

Rs.Open s_SQL01, Cn, adOpenStatic, adLockOptimistic, adCmdText

'列名だけを書き込み

For j = 1 To Rs.Fields.Count

o_DistWs01.Cells(1, j) = Rs.Fields(j - 1).Name

Next j

'実データ（今回テストは10万行40列分）の貼り付け

s_DistCellAddr01 = "A2"

o_DistWs01.Range(s_DistCellAddr01).CopyFromRecordset Rs

Rs.Close

Set Rs = Nothing

Cn.Close

Set Cn = Nothing

Application.ScreenUpdating = True

'終了時間取得

endTime = Timer

'処理時間計算

processTime = endTime - startTime

Debug.Print processTime

End Sub

'#####################################################

'以下、コメントつきの、モトとなったコード。

'#####################################################

Sub ReadByADO01_CopyFromRsPast0001()

'★変数設定

Dim Cn As ADODB.Connection

'読込対象のXLSファイルの

'「ADO接続文字用のオブジェクト」用の変数

Dim Rs As ADODB.Recordset

'読込対象のシートの

'「ADOデータ範囲？設定用のオブジェクト

'　＝レコードセット」用の変数。

Dim o_SrcWb01 As Workbook

'読込対象のブック

'を格納（＝指定）するためのオブジェクト変数

Dim s_SrcWbFlPath As String

'読込対象のXLSファイルの

'フルパス格納用の変数

Dim s_SrcWsNm As String

'読込対象のシート名の格納用の変数

Dim s_SQL01 As String

'どの列と行をどのように読み込むかの

'SQL文を格納するための変数

Dim o_DistWs01 As Worksheet

'読み込んだデータを転記する先のシート

'を格納（＝指定）するためのオブジェクト変数

Dim i As Integer

'ソースのシートのインデックスを

'格納（＝指定）するための変数

Dim j As Integer

'レコードセットの列のインデックスを

'格納（＝指定）するための変数

Dim s_DistCellAddr01 As String

'2つ目以降のソースシートを読み込んだ際に

'それを転記先シートに書き込むときの、

'その起点となるセルのアドレスを格納するための変数

'▼一応、データが大量だと遅くなってしまうので

'　画面描画をいったん停止。

'　デバッグ時などはコメントアウトする。

Application.ScreenUpdating = False

'Excelの画面描画をいったん停止。

'今の画面のまま変化させないようにする。

'★以下、メインプログラムです。

' Set o_SrcWb01 = ThisWorkbook

' 'データを読み込みたい、そのソースとなるブックの設定

' Set o_SrcWb01 = Workbooks.Open("D:\1\縦結合テスト.xlsm")

' Set o_SrcWb01 = Workbooks.Open("\\Fsvr01\Fld01\test.xlsm")

' s_SrcWbFlPath = o_SrcWb01.FullName

' 'そのブックのフルパスを取得。

' '（以下のような指定も可。）

s_SrcWbFlPath = "D:\1\10man40c.xlsx"

'◆◆◆◆◆◆ 読み込先のExcelのフルパスを指定

Set Cn = New ADODB.Connection

'ADOにてのデータ接続（＝ファイル内容の読み込み）

'ができるようにするための準備

Set Rs = New ADODB.Recordset

'ADOにてのレコードセット（＝シート内データ範囲？）が

'作れるようにするための準備

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & s_SrcWbFlPath & ";" & _

"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=False"""

'ADOで読込先のソースファイルに接続。

'（＝読込先のソースファイルを開く）

'▼転記先のシートの内容をいったんクリア

'　※転記先のシートが一番左にあることが前提です。

Set o_DistWs01 = ThisWorkbook.Worksheets.Item("Sheet1")

'◆◆◆◆◆◆ 読み込んだデータを転記する先（自ファイル）のシートを指定

o_DistWs01.Cells.ClearContents

'読み込み先のファイルの列数や行数が増減するといけないので、

'一応、転記するシートをきれいに空白しておく。

'前回よりデータ列数や行数が少ないと、

'前回のデータが残ってしまうので。

s_SrcWsNm = "SheetA" & "$"

'◆◆◆◆◆◆ ソースとなる相手先（読み込み先）のシートの名前を指定

s_SQL01 = "SELECT * FROM [" & s_SrcWsNm & "]"

'SQL文の作成

Rs.Open s_SQL01, Cn, adOpenStatic, adLockOptimistic, adCmdText

'SQL文に基づいてレコードセットを生成

'「レコードセット＝データの入力された範囲？」を

'決定しつつ。

'▼SQL文にて、ソースシートからどの列と行をどのように読み込むかを設定。

'基本、列名の書き換えだけでOKだと思います。

'ただし、実際のソースシートの列名の末尾に、スペースなどが

'隠れていないことが前提です。

'なお、セル範囲を指定しない場合は、

'ソースシート丸ごとのすべての範囲を見に行ってくれて、

'データが入力されている部分を自動判別してくれて、

'レコードセットを作ってくれます。

's_SQL01 = "SELECT * FROM [" & s_SrcWsNm & "A1:N15" & "]"

'このようにシート名のあとにセル範囲を追記指定することも可能。

'そこまでの範囲を見に行って、その範囲内で、

'データが入力されている部分を自動判別してくれて、

'レコードセットを作ってくれます。

s_DistCellAddr01 = "A2"

'出力先のセルの番地の指定

'列名はレコードセットの中には含まれていないので

'ループで書き出さないといけないので、データのみの

'起点セルを指定します。

For j = 1 To Rs.Fields.Count

o_DistWs01.Cells(1, j) = Rs.Fields(j - 1).Name

Next j

'列名のみを、シートへの転記

'「HDR=Yes」でデータをレコードセットの取り込んだので、

'列名は、Rsを貼り付けても貼り付かないため。

o_DistWs01.Range(s_DistCellAddr01).CopyFromRecordset Rs

'CopyFromRecordset にて、転記先のシートに

'レコードセットの内容を書き出す。

'「HDR=NO」だと、列名もシートに書き出されるが、

'「HDR=Yes」はレコードのみとなる。

' なお、「HDR=NO」だと、転記後にセルに緑の三角エラーが

' 出るが「HDR=Yes」のときは出ない。

' 今回は、「HDR=Yes」でやっている。

Rs.Close

Set Rs = Nothing

' ' 作ったオブジェクトの破棄：

' '一応メモリリークを起こさないように？

' '作ったオブジェクトをメモリから消す

Cn.Close

Set Cn = Nothing ' 同上

'▼Excelの画面描画を再開｡

Application.ScreenUpdating = True

'画面の変化が普通に表示されるようにする。

End Sub

●相手のファイルを開き、Worksheet.Openメソッドでシートを吸い込むコード
（どちらかというと遅い）
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13300509618

'
'

Option Explicit

'比較結果
'（読み込むデータが、10列10行の場合。10万件40列の場合はまた別。
'　「方法01ADO」のほうが１～１．５秒ほど早かったです。
'　つまり、「方法02WS_OPEN」が意外と頑張ってた。）

'方法01ADO     １回目   0.48046875
'方法01ADO     ２回目   0.078125
'方法01ADO     ３回目以降 2回目と変わらず高速。


'方法02WS_OPEN １回目   0.6015625
'方法02WS_OPEN ２回目   0.57421875
'方法02WS_OPEN ３回目以降 2回目とほぼ変わらず。

'※ノートPCにて、AC電源につないでない結果です。
'　ですので、つなぐと、両者とも１回目がもう少し速くなります。
'　ただし、「方法01ADO」の2回目の速さは、
'　AC電源につないでもつながなくても同様に高速でした。





Sub ExtractDataFromClosedWorkbook()

Dim startTime As Double
Dim endTime As Double
Dim processTime As Double

'開始時間取得
startTime = Timer


Dim sourceFilePath As String
Dim sourceSheetName As String
Dim sourceSheetIndex As Long
Dim sourceRange As String
Dim destCell As Range
Dim sourceWorkbook As Workbook
Dim sourceWorksheet As Worksheet
Dim dataRange As Range

' 作業用シートの参照
Dim Worksheet As Worksheet
Set Worksheet = ThisWorkbook.Sheets("Sheet1") ' シート名を適宜変更してください

' 転記元ファイルパス、シート名、範囲、転記先セルの指定
'sourceFilePath = workSheet.Range("A1").Value ' 転記元ファイルパス

'sourceFilePath = "D:\1\10man40c.xlsx" ' 転記元ファイルパス
sourceFilePath = "D:\1\10r10c.xlsx" ' '◆◆◆◆◆◆ 転記元ファイルパス


'sourceSheetName = workSheet.Range("B1").Value ' 転記元シート名
sourceSheetName = "SheetA" ' '◆◆◆◆◆◆ 転記元シート名

'sourceSheetIndex = Worksheet.Range("C1").Value ' 転記元シート番号（シート名がない場合に使用）
sourceSheetIndex = 1 ' 転記元シート番号（シート名がない場合に使用）

'sourceRange = Worksheet.Range("A1").Value ' 転記元範囲
sourceRange = "A1:J10" ' '◆◆◆◆◆◆ 転記元範囲


Set destCell = Worksheet.Range("A1") ' '◆◆◆◆◆◆ 転記先セル

' 転記元ファイルを開かずにデータを取得
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

' 転記元ファイルを開く
Set sourceWorkbook = Workbooks.Open(sourceFilePath, ReadOnly:=True)
On Error GoTo 0

If sourceWorkbook Is Nothing Then
MsgBox "転記元ファイルを開くことができませんでした。ファイルパスを確認してください。"
Exit Sub
End If

' シートを選択
If sourceSheetName <> "" Then
Set sourceWorksheet = sourceWorkbook.Sheets(sourceSheetName)
Else
Set sourceWorksheet = sourceWorkbook.Sheets(sourceSheetIndex)
End If

' 転記元範囲のデータを取得
Set dataRange = sourceWorksheet.Range(sourceRange)

' 作業用シートの指定セルにデータを転記
destCell.Resize(dataRange.Rows.Count, dataRange.Columns.Count).Value = dataRange.Value

' 転記元ファイルを閉じる
sourceWorkbook.Close SaveChanges:=False

Application.ScreenUpdating = True
Application.EnableEvents = True

'終了時間取得
endTime = Timer

'処理時間計算
processTime = endTime - startTime
Debug.Print processTime


'MsgBox "データの転記が完了しました。"
End Sub


'
'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

Option Explicit

'比較結果

'（読み込むデータが、10列10行の場合。10万件40列の場合はまた別。

'　「方法01ADO」のほうが１～１．５秒ほど早かったです。

'　つまり、「方法02WS_OPEN」が意外と頑張ってた。）

'方法01ADO １回目 0.48046875

'方法01ADO ２回目 0.078125

'方法01ADO ３回目以降 2回目と変わらず高速。

'方法02WS_OPEN １回目 0.6015625

'方法02WS_OPEN ２回目 0.57421875

'方法02WS_OPEN ３回目以降 2回目とほぼ変わらず。

'※ノートPCにて、AC電源につないでない結果です。

'　ですので、つなぐと、両者とも１回目がもう少し速くなります。

'　ただし、「方法01ADO」の2回目の速さは、

'　AC電源につないでもつながなくても同様に高速でした。

Sub ExtractDataFromClosedWorkbook()

Dim startTime As Double

Dim endTime As Double

Dim processTime As Double

'開始時間取得

startTime = Timer

Dim sourceFilePath As String

Dim sourceSheetName As String

Dim sourceSheetIndex As Long

Dim sourceRange As String

Dim destCell As Range

Dim sourceWorkbook As Workbook

Dim sourceWorksheet As Worksheet

Dim dataRange As Range

' 作業用シートの参照

Dim Worksheet As Worksheet

Set Worksheet = ThisWorkbook.Sheets("Sheet1") ' シート名を適宜変更してください

' 転記元ファイルパス、シート名、範囲、転記先セルの指定

'sourceFilePath = workSheet.Range("A1").Value ' 転記元ファイルパス

'sourceFilePath = "D:\1\10man40c.xlsx" ' 転記元ファイルパス

sourceFilePath = "D:\1\10r10c.xlsx" ' '◆◆◆◆◆◆ 転記元ファイルパス

'sourceSheetName = workSheet.Range("B1").Value ' 転記元シート名

sourceSheetName = "SheetA" ' '◆◆◆◆◆◆ 転記元シート名

'sourceSheetIndex = Worksheet.Range("C1").Value ' 転記元シート番号（シート名がない場合に使用）

sourceSheetIndex = 1 ' 転記元シート番号（シート名がない場合に使用）

'sourceRange = Worksheet.Range("A1").Value ' 転記元範囲

sourceRange = "A1:J10" ' '◆◆◆◆◆◆ 転記元範囲

Set destCell = Worksheet.Range("A1") ' '◆◆◆◆◆◆ 転記先セル

' 転記元ファイルを開かずにデータを取得

On Error Resume Next

Application.ScreenUpdating = False

Application.EnableEvents = False

' 転記元ファイルを開く

Set sourceWorkbook = Workbooks.Open(sourceFilePath, ReadOnly:=True)

On Error GoTo 0

If sourceWorkbook Is Nothing Then

MsgBox "転記元ファイルを開くことができませんでした。ファイルパスを確認してください。"

Exit Sub

End If

' シートを選択

If sourceSheetName <> "" Then

Set sourceWorksheet = sourceWorkbook.Sheets(sourceSheetName)

Else

Set sourceWorksheet = sourceWorkbook.Sheets(sourceSheetIndex)

End If

' 転記元範囲のデータを取得

Set dataRange = sourceWorksheet.Range(sourceRange)

' 作業用シートの指定セルにデータを転記

destCell.Resize(dataRange.Rows.Count, dataRange.Columns.Count).Value = dataRange.Value

' 転記元ファイルを閉じる

sourceWorkbook.Close SaveChanges:=False

Application.ScreenUpdating = True

Application.EnableEvents = True

'終了時間取得

endTime = Timer

'処理時間計算

processTime = endTime - startTime

Debug.Print processTime

'MsgBox "データの転記が完了しました。"

End Sub

投稿タグ: ExcelSQL, ExcelVBA, Excelの独学, パソコンでの自動化, マクロ, 独学, 自動化

MENU

関連記事

最近の投稿

カテゴリー

MENU

ExcelVBA ～ 「ADO＋SQL＋CopyFromRecordset」と「Worksheet.Openメソッド+代入等々」のどちらが高速か？のテスト。（10行10列、10万行40列）

関連記事

最近の投稿

カテゴリー

タグ

ExcelVBA ～「ADO＋SQL＋CopyFromRecordset」と「Worksheet.Openメソッド+代入等々」のどちらが高速か？のテスト。（10行10列、10万行40列）