🎄teratailクリスマスプレゼントキャンペーン2024🎄』開催中!

\teratail特別グッズやAmazonギフトカード最大2,000円分が当たる!/

詳細はこちら
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Q&A

解決済

3回答

55790閲覧

VBA PasteSpecialメソッドの値貼り付けをするとエラーが発生する

takamak

総合スコア7

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

0グッド

0クリップ

投稿2020/06/25 10:11

前提・実現したいこと

Accessで作成したデータを、計算式が入力されている既存のExcelに貼り付け
再計算後、全セルを値貼り付けしたい(計算式をなくしてファイルを軽くするため)

行数は1500前後、列数が850列あります。
計算式が入っている列を750列位に減らすとエラーなく実行できます。
列数に制限などありますでしょうか?

.Cells.PasteSpecial xlPasteValues 中に以下のエラーメッセージが発生しました。

発生している問題・エラーメッセージ

RangeクラスのPasteSpecialメソッドが失敗しました。

該当のソースコード

With xlBook.Worksheets("Sheet1")
.Cells.Copy
.Cells.PasteSpecial xlPasteValues ← ここでエラー
End with

試したこと

セルを範囲指定してクリップボードを使わず貼り付けしたり、
配列を使用した貼り付けというパターンを試しましたが
どちらも下記のエラーメッセージが表示されました。
また、列数が多すぎるかと思い、100列ずつに区切りloopさせてみましたが、後半(750列以降)になると同じエラーが発生しました。
列の右側から(逆から)処理をしてもやはりエラーになりましたので、Excelの計算式が問題ということではなさそうです。

エラーメッセージ:アプリケーション定義またはオブジェクト定義のエラーです。

Maxrow = .Cells(.Rows.Count, 8).End(xlUp).Row
Maxcol = .Cells(8, .Columns.Count).End(xlToLeft).Column

'セルの範囲指定で貼り付けバージョン
.Range(.Cells(1, 1), .Cells(Maxrow, Maxcol)).Value = .Range(.Cells(1, 1), .Cells(Maxrow, Maxcol)).Value ←エラー

'配列を使用して貼り付けバージョン
Dim MyArray As Variant
MyArray = .Range(.Cells(1, 1), .Cells(Maxrow, Maxcol))
.Range(.Cells(1, 1), .Cells(Maxrow, Maxcol)) = MyArray ←エラー

何かお気づきのことがありましたらご教授よろしくお願いいたします。

補足情報(FW/ツールのバージョンなど)

windows7 64bit CPU intel i7 メモリ16GB
Office2010

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

自己解決

kai_keitaiさん
hatena19さん

ご丁寧にご回答ありがとうございます。
教えていただいたことを色々なパターン(DoEventsを入れたり、自動計算を入れたり、配列にしてみたりUsedRangeにしてみたり)で試してみましたがエラーメッセージの内容は変わりませんでした。

今回の処理は、アクセス側でデータ加工し、既存Excelを開き "データ"シートへRecordsetを貼り付け
"Sheet1"で "データ"シートを参照した計算式を再計算後に値貼り付けをしておりました。

試しにデータシートへデータを貼り付けた後、いったんExcelファイルを保存し
開きなおした後に ExcelのVBEに上記の値貼り付けのコードを作成し実行したところ
5分以上かかっても貼り付けが完了しませんでした。(中断しました・・・)
Sheet1のほとんどのセルが計算式になっており、850列あるうち350列ほどsumifなどの処理の重い計算式になっています。
再計算が遅いのが原因ならと思い、計算式はまったくあってないのを承知の上で、
すべての計算式を単純にデータシートを参照するだけの式(=データ!A1 のような式)に変更して
実行したところ何なく実行できました。

以上のことから、Excelの計算式が重すぎるという結論になりました。
まずはExcelのsheet1の計算方法を見直すことを行っていきたいと思います。

多くのご助言大変ありがとうございました。

・・・・Sheet1の計算式の見直しとデータ加工の見直し。850列もあるものを見直すのは心が折れそうです(T.T)

投稿2020/06/25 13:15

takamak

総合スコア7

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2020/06/28 02:07

大変そうですね。 1.Access側に計算させる。 2.power queryを使用する。 3.スピル関数を使用する。 4.sumproduct()への置き換え。 みたいな感じで検討されてはどうでしょうか。 個人的には、 動作しなくなった際に変更を加えた箇所から、sumproduct()に式を置き換えるという事を行ってみますけどね。
guest

0

下記だとどうなりますか。

vba

1With xlBook.Worksheets("Sheet1") 2 .UsedRange.Value = .UsedRange.Value 3End with

あるいは、

vba

1Dim ary() As Variant 2With xlBook.Worksheets("Sheet1") 3 ary = .UsedRange.Value 4 .UsedRange.Value = ary 5End with

セル範囲が大きすぎるの原因だとしたら、下記のように1行ずつ上書きするとか。

vba

1Dim ary() As Variant 2With xlBook.Worksheets("Sheet1") 3 ary = .UsedRange.Value 4 .UsedRange.Value = ary 5End with

あとは、処理の前後に下記のコードを挿入して描画と計算を停止させるとか。

vba

1Application.ScreenUpdating = False 2Application.Calculation = xlCalculationManual 3 4'処理 5 6Application.Calculation = xlCalculationAutomatic 7Application.ScreenUpdating = True

投稿2020/06/25 11:20

編集2020/06/25 11:38
hatena19

総合スコア34073

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

Paste:= が抜けてます。

PasteSpecial Paste:=xlPasteValues

投稿2020/06/25 10:15

kai_keitai

総合スコア344

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takamak

2020/06/25 11:19

ご回答ありがとうございます。 早速試してみましたが、残念ながら 「RangeクラスのPasteSpecialメソッドが失敗しました」のエラーが出ました。
kai_keitai

2020/06/25 11:35

Sub Macro1() Dim xlBook As Workbook Set xlBook = ThisWorkbook With xlBook.Worksheets("Sheet1") Cells.Copy Cells.PasteSpecial Paste:=xlPasteValues End With End Sub 単純ではありますが、実行しても全く問題ありません。 もしかして、ワークシートが重い(計算に時間がかかる等)で、全セルを値貼り付けしているのか。 と思い、下記のコードは? Sub Macro1() Application.Calculation = xlCalculationManual DoEvents Dim xlBook As Workbook Set xlBook = ThisWorkbook With xlBook.Worksheets("Sheet1") Cells.Copy Cells.PasteSpecial Paste:=xlPasteValues End With Application.Calculation = xlCalculationAutomatic End Sub 自動計算の on と off を切替しながらは、どうですかね。 途中に、保存をするとか。 どうも、OSがらみの不安定要素もあるかもです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.36%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問