出産前後の痔にはご注意!

VBA超初心者です。

CSVファイルをインポートし、データ更新すると、下記エラーが起こります。

実行時エラー '-2147021882 (8007000e)'
データの消失を防ぐため、空白でないセルをワークシートの外にシフトすることはできません。
Ctrl+Endキーを押して最後の空白でないセルに移動し、そのセルとデータの末尾との間にある
すべてのセルを削除またはクリアしてください。その後、セルA1を選択し、ブックを保存して最後の
セルをリセットしてください。

(以下にも文章ありますが、一部しか表示されません)

この時、「デバック」ボタンを押すと、以下のマクロ表示となります。

Sub データ更新()

’データ更新日報出力Macro
’マクロ記録日:○○○ ユーザ名:○○


  ScreenUpdating = False
Worksheets("sheet1")Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
  Range("A2").Select
End Sub

Sub ピボットテーブル更新日報印刷()
   Sheets("Sheet2").Select
Range("B14").Select
ActiveSheet.PIvotTables("ピボットテーブル1").RefreshTable
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:True
End Sub


この構文で、Selection.QueryTable.Refresh BackgroundQuery:=False が
 間違っているようなのですが、どうすればいいのか良くわかりません。
 どなたか教えていただけないでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (1件)

>Selection.QueryTable.Refresh BackgroundQuery:=False


この『構文』自体が間違っているわけではありません。
"sheet1"のA2セルに設定されているQueryTable(外部データ取り込み)
を更新する時に、エラーが発生して更新できない、という意味です。

おそらく、エラー原因はエラーメッセージそのままではないでしょうか。
『..空白でないセルをワークシートの外にシフトすることはできません..』

QueryTableの設定によっては、取り込みデータを挿入するようになっています。
例えば、シートの最下行にデータがあって、
取り込みデータを挿入するとデータがはみ出てしまう、という場合は
そういったエラーメッセージが出る事も考えられます。
#こちらの環境では再現できなかったので半分自信なしですが -"-

対策もエラーメッセージに書かれている通りです。
例えば"sheet1"で使用中のデータの最終行が1,000行だと思っていても、
それ以下になんらかの使用領域が残っている可能性があります。
Ctrl+Endキーを押してみてください。
"sheet1"の実際の最終セルに移動します。
その行から1,001行目までを選択して削除してください。
(実際に必要なデータがないかどうかを確認の上で。)
その後ブックを保存してください。

以上で解消すれば良いのですが。

または、QueryTableの設定を変更してみる方法もあります。
"sheet1"のA2セルを選択して右クリック。
[データ範囲のプロパティ]を開きます。
『変更されたレコード(行)のデータ更新時の処理:』

○新しいデータのセルを挿入し、使用されていないセルを削除する
になっていたら、
○既存のセルを新規データで上書きし、使用されていないセルはクリアする
に変更してみてください。

いずれかでも解消できない場合は
現在のシート状態と実際の利用の仕方などの詳細情報があると
他にアドバイスあるかもしれません。
(例えば外部データを取り込んだ後に数式や書式を設定していたりとか、
 何かの図形を配置しているとか)
    • good
    • 0
この回答へのお礼

>対策もエラーメッセージに書かれている通りです。
例えば"sheet1"で使用中のデータの最終行が1,000行だと思っていても、
それ以下になんらかの使用領域が残っている可能性があります。
Ctrl+Endキーを押してみてください。
"sheet1"の実際の最終セルに移動します。
その行から1,001行目までを選択して削除してください。
(実際に必要なデータがないかどうかを確認の上で。)
その後ブックを保存してください。

end-u 様

教えていただいたとおり、やってみた所、正常に動きました。
周りに詳しい者がおらず、大変助かりました。
わかり易く教えていただき、ありがとうございました。

お礼日時:2010/11/22 14:48

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q外部データの更新がうまくできません(Excel VBA)

いつもお世話になってます。
以下のプログラムで外部データの更新を入れたのですがうまく作動しません。

Dim sh As Worksheet
Dim lr As Long
Dim tlr As Long

For Each sh In Worksheets
If sh.Name Like "*D" Then
sh.Select
Selection.QueryTable.Refresh
End If
Next

For Each sh In Worksheets
If sh.Name Like "*D" Then
lr = sh.Cells(Rows.Count, 5).End(xlUp).Row
sh.Rows("1:" & lr).Copy
tlr = Sheets("統合データ").Cells(Rows.Count, 5).End(xlUp).Row
Sheets("統合データ").Range("A" & tlr + 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next

プログラムの内容としては
(1)末尾が"D "のシートのデータを更新する(これらのシートは外部データを取込んでいます)
(2)"*D"シートのデータを全て"統合データ"シートに上から順に貼り付ける
ですが、上のプログラムだともとあるデータを貼り付けてからデータを更新しています。更新後のデータを貼り付けるにはどのようにすればよいでしょうか。
よろしくお願いします。

いつもお世話になってます。
以下のプログラムで外部データの更新を入れたのですがうまく作動しません。

Dim sh As Worksheet
Dim lr As Long
Dim tlr As Long

For Each sh In Worksheets
If sh.Name Like "*D" Then
sh.Select
Selection.QueryTable.Refresh
End If
Next

For Each sh In Worksheets
If sh.Name Like "*D" Then
lr = sh.Cells(Rows.Count, 5).End(xlUp).Row
sh.Ro...続きを読む

Aベストアンサー

こんにちは。KenKen_SP です。

QueryTable はディフォルトでは非同期ですからね...更新前にどんどん先へ
コードを実行してしまいます。同期させたいなら、BackgroundQuery プロパティー
を False にしてください。

それから、Worksheets コレクションで同一のものを2度ループさせるさせる
のは冗長なので、一回で済ませましょう。

こんな感じでどうですか?

Sub SampleProc()

  Dim Sh     As Worksheet
  Dim QT     As QueryTable
  Dim lSrcLastRow As Long
  Dim lDstLastRow As Long
  
  For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name Like "*D" Then
      For Each QT In Sh.QueryTables
        ' 更新を待機するためには BackgroundQuery = True にします
        QT.BackgroundQuery = False
        QT.Refresh
      Next
      ' データコピー
      lSrcLastRow = Sh.Cells(Rows.Count, "E").End(xlUp).Row
      Sh.Rows("1:" & CStr(lSrcLastRow)).Copy
      ' データペースト
      With ThisWorkbook.Sheets("統合データ")
        lDstLastRow = .Cells(Rows.Count, "E").End(xlUp).Row
        .Rows(lDstLastRow + 1).PasteSpecial _
        Paste:=xlPasteValuesAndNumberFormats
      End With
    End If
  Next
  Application.CutCopyMode = False

End Sub

こんにちは。KenKen_SP です。

QueryTable はディフォルトでは非同期ですからね...更新前にどんどん先へ
コードを実行してしまいます。同期させたいなら、BackgroundQuery プロパティー
を False にしてください。

それから、Worksheets コレクションで同一のものを2度ループさせるさせる
のは冗長なので、一回で済ませましょう。

こんな感じでどうですか?

Sub SampleProc()

  Dim Sh     As Worksheet
  Dim QT     As QueryTable
  Dim lSrcLastRow As Long
  Dim lDs...続きを読む

Qexcel データ更新 VBA

Excel2003外部データの取り込み→データの取り込み→データファイルの選択で
取り込み設定しました。
常にデータ→データの更新をクリックして新しいデータを読み込みます。
このデータ更新をクリックする作業をVBAにしたいのですができますか?
教えてください
※ それ以降の作業はVBAで完了しています。
よろしくお願いします

Aベストアンサー

こんにちは。

  ActiveWorkbook.RefreshAll
とか、
  Range("A1").QueryTable.Refresh
とかで、どうでしょう。
それぞれ、Workbook オブジェクト、または、Range オブジェクト
を適宜指定してください。

もし、エラーが出る場合は、
それぞれのクエリーテーブルのオプション設定を変えてみて下さい。

(#xl2003では確認していませんので、違っていたらスミマセン)

QExcel VBAでWebクエリ更新

VBA初心者です。

Excel VBAで、Webクエリを更新させ、取得したデータを同じBook内の別のSheetに
コピーさせたうえで、データのソート等を行いたいと考えています。

上記の操作をマクロで記録し、走らせてみると、Webクエリのデータ更新(取得)が
完了する前に、それ以降の操作に進んでしまいます。

Webクエリのデータ更新(取得)が完了してから、次の操作に進ませるためには、
どのようにすれば良いでしょうか?
よろしくお願いします。

Aベストアンサー

WEBクエリを「更新する」マクロをキチンと書いていますね?
(しばしば更新じゃなく「毎回WEBクエリをイチから埋め込み直す」マクロを書いてる初心者さんが多いので)

で、通常は
sub macro1()
 activesheet.querytables(1).refresh backgroundquery:=false
’以下続きの作業
 msgbox "DONE"
end sub

とかで十分です。


#シートにこれまでに何度も何度もWEBクエリを埋め込み直していて、収拾がつかない状態になっていないよう気を付けてください。まっさらのシートに一つWEBクエリを埋めた状態から作業してみて下さい。

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

Qエクセル:マクロ「Application.CutCopyMode = False」って?

エクセルのマクロを記録していると

「Application.CutCopyMode = False」

というものがよく出てきますが、これは何でしょう?
どういう意味のものかわかりません。
削除しても差し支えないのもでしょうか?

Aベストアンサー

「Application.CutCopyMode = False」の前で
セルのコピー、または切り取りを行っていると思います。
これは、その操作(セルのコピー、または切り取り)を無効にしているだけです。
------------
Range("A1").Select
Selection.Copy ← これを無効にしている
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
------------
上記の場合であれば、「Application.CutCopyMode = False」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
Range("A1").Select
Selection.Copy
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Paste ← ココでエラー
------------
ご自分で、セルをコピーしてみると分かると思いますが、コピーした範囲が点線で点滅されます。
「Application.CutCopyMode = False」をすると、
その点滅がなくなります。

「Application.CutCopyMode = False」の前で
セルのコピー、または切り取りを行っていると思います。
これは、その操作(セルのコピー、または切り取り)を無効にしているだけです。
------------
Range("A1").Select
Selection.Copy ← これを無効にしている
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
------------
上記の場合であれば、「Application.CutCopyMode = False」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
...続きを読む

Q【Excel 2007】クエリが実行できません

データタブ⇒その他のデータソース⇒Microsoft Query⇒クエリウィザードを使ってデータ抽出を行ないました。同一ブック内に元となるデータシート、抽出データシートを作成しています。こここまではうまくできました。しかし、作成したブックを同一パソコンの別フォルダに移動したところ、エラーが出てデータ接続ができません。具体的には、「実行時エラー'1004' 一般エラー ODBCエラー」がでます。ブックを元の場所に戻すとエラーが出ずきちんと集計できます。作成したブックを移動してもデータ接続を行い、クエリがきちんと実行できるようにするにはどうしたら解決するでしょうか?もう一度ウィザードからやるのは大変です。

データ参照場所を再設定すればできそうなのですが、やり方が分かりません。例えばマイドキュメント⇒デスクトップにブックを移動した場合だとどのようになりますか?自分でも調べて試してみたのですがうまくできません。よろしくお願いします。

Aベストアンサー

2003互換Bookの場合、
[データ]-[接続]、[プロパティ]、[接続のプロパティ]の[定義]タブの
「コマンド文字列」内の FROM句 にもファイルフルパスがあるようですね。
ここも書き換えてください。
書き換え後、ダイアログを閉じる時にエラーメッセージが出る事もあるかもしれません。
その場合は取り敢えず続行させてください。
改めて[更新]すれば、その後は継続して更新可能になるかと思います。

QEXCELファイルのカレントフォルダを取得するには?

EXCELファイルのカレントフォルダを取得するには?

C:\経理\予算.xls

D:\2005年度\予算.xls

EXCEL97ファイルがあります。

VBAで
  カレントフォルダ名
(C:\経理\,D:\2005年度\)
を取得する事は可能でしょうか?

CURDIRでは上手い方法が見つかりませんでした。

Aベストアンサー

こんばんは。
Excel97 でも、同じですね。以下で試してみてください。

Sub test()
'このブックのパス
a = ThisWorkbook.Path
'アクティブブックのパス
b = ActiveWorkbook.Path
'Excelで設定されたデフォルトパス
c = Application.DefaultFilePath
'カレントディレクトリ
d = CurDir
MsgBox "このブックのパス   : " & a & Chr(13) & _
   "アクティブブックのパス: " & b & Chr(13) & _
   "デフォルトパス    : " & c & Chr(13) & _
   "カレントディレクトリ : " & d & Chr(13)
End Sub

QEXCEL VBAマクロ作成で、他のEXCELからデータを取り込みたい

メインプログラム(EXCEL VBA)より、
他のフォルダーにあるEXCELの項目の内容を取り込みたいです。
たとえば他のフォルダーのEXCELのRange("A2:A3").ValueをメインプログラムのRange("C2:C3").Valueにセットしたい時です。

・コマンドボタン押したら、どこのEXCELから取り込むかのポップアップ(?)は、表示はできてます。
・作業者が選んだパスとブックもMsgBoxで表示できてるので、もらう相手の場所も取得できてます。

・となると次はOPEN,INPUTですか?
テキストデータの取り込みですと、Inputでそのバッファを定義してるのですが、なんか違うような。。。

よろしくお願いします!

Aベストアンサー

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Cells(2, 2).Value ' 相手シートの B2 の値を自分自身の A1 に書き込む

readBook.Close False ' 相手ブックを閉じる
Set readSheet = Nothing
Set readBook = Nothing

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Ce...続きを読む

QVBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。

マクロを含むエクセル(EXCEL2000)をHTMLのページからハイパーリンクで呼び出しています。そのエクセルでボタン操作に従い罫線やセルの着色を行っています。しかし、着色結果が更新されません。スクロールバー等で画面を移動すると正しく着色されています。このエクセルを通常に起動した場合は、問題なく動作するのですが、シート全体を更新する方法を教えて下さい。
各関数では、以下のスクリーンアップデータの処理を入れています。
Application.ScreenUpdating = False
    (処理)
Application.ScreenUpdating = False

Aベストアンサー

たぶん、EXCEL独特の問題だと思うのですが、HTML の場合、すでに色の部分を表面上で使用しているので、それでメモリが占有させているのではないかと私は思っています。

他にも、

 ActiveWorkbook.HTMLProject.RefreshDocument True

というのがありますね。
ホスト アプリケーション内のブックに含まれる HTML プロジェクトを更新する、というのがありますね。

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング