• 締切済み

ExecuteExcel4Macroを使って他のブックを開かずにセルを

ExecuteExcel4Macroを使って他のブックを開かずにセルを参照しているのですが、worksheet(1)という記述を使いたいのですがどうすればよいでしょうか? Target1 = "'" & Path & "[" & buf & "]sheet1'!R75C3" 上記だとsheet1という名前でなかったらエラーが出てしまうので・・・。

みんなの回答

  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.4

>ファイル数は3000ほどあるので、開いていたら処理が間に合わないような・・・。 テンプレートでファイルを作っているならともかく、シート名の定まらないようなブックを、3000個も作ってしまった後に、値を取りたいというのは、もう計画の時点で、失敗を示しているように思います。単に、以下は、実験的なものを越えることはありません。 ------------------------------------------- >worksheet(1)という記述を使いたいのですがどうすればよいでしょうか? Worksheets(1)のことだと思いますが、シートタブの左端という意味です。 以下は、Excel 2003 までのファイル構造のみ コードの使い方など、細かい解説はしません。 現在は、安全のために、100ファイル以上では働かないようにしてあります。 If i > 100 Then Exit Sub '安全のために、100ファイルを超えたら、マクロ中止 全部実行するには、 ''If i > 100 Then Exit Sub '安全のために、100ファイルを超えたら、マクロ中止 コメントブロック(')を加える。 '標準モジュール。以下のADOオブジェクトは、できる限り事前バインディングのほうが有利です。エラーが残っているかもしれません。 '========================================= Sub Main()   Dim myPath As String   Dim fn As String   Dim i As Long   Dim buf As Variant   Dim ret1 As Variant   i = 1   myPath = ThisWorkbook.Path & "\"   fn = Dir(myPath & "*.xls", vbNormal)   Do While fn <> ""     If fn Like "*.xls" Or StrComp(ThisWorkbook.Name, fn, 1) <> 0 Then       buf = getSheetsName(fn)     End If     If IsArray(buf) Then       '*注意       ret1 = ExecuteExcel4Macro("'" & myPath & "[" & fn & "]" & buf(0) & "'!R75C3")       If VarType(ret1) <> vbError Then         If ret1 <> "" Then           Cells(i, 1).Value = ret1           i = i + 1         End If       End If     End If     If i > 100 Then Exit Sub '安全のために、100ファイルを超えたら、マクロ中止     fn = Dir   Loop End Sub Function getSheetsName(FileName As String) 'ファイル名からシート名を取る関数   Dim oConn As Object 'New ADODB.Connection   Dim oRS As Object ' New ADODB.Recordset   Dim shName As String   Dim buf As String   Dim shLists() As String   Dim i As Long   Dim j As Long   On Error GoTo ErrHandler   Const adSchemaTables As Integer = 20   Set oConn = CreateObject("ADODB.Connection")   With oConn     .Provider = "Microsoft.Jet.OLEDB.4.0"     .Properties("Extended Properties").Value = "Excel 8.0"     .Open FileName   End With   Set oRS = oConn.OpenSchema(adSchemaTables)   Do Until oRS.EOF     shName = oRS.Fields("TABLE_NAME").Value     If Right(shName, 1) = "$" Or Right(shName, 1) = "'" Then       ReDim Preserve shLists(i)       buf = Mid$(shName, 1, Len(shName) - 1)       buf = Replace(buf, "$", "", 1)       buf = Replace(buf, "'", "")       shLists(i) = buf       i = i + 1     End If     oRS.MoveNext   Loop   oRS.Close   oConn.Close ErrHandler:   Set oRS = Nothing   Set oConn = Nothing   '確認用    If i > 0 Then    getSheetsName = shLists() '出力は、配列   End If End Function '------------------------------------------- ''掲示板で、あれこれ質問するよりも、自分の分かる範囲で、ファイルOpen, Close でコードを作ったほうが延べ時間では早いです。しかし、実際のマクロは遅いです。以下で実験してみました。しょせん、一回キリのようなマクロは、テクニックは必要ありません。 ただし、VBAマクロは、Run してみなければ分からないものが多いです。 '------------------------------------------- Sub Main2()   Dim myPath As String   Dim fn As String   Dim i As Long   Dim buf As Variant   i = 1   myPath = ThisWorkbook.Path & "\"   fn = Dir(myPath & "*.xls", vbNormal)   Do While fn <> ""     If fn Like "*.xls" Or StrComp(ThisWorkbook.Name, fn, 1) <> 0 Then       ''*シートの設定       buf = getSheetsNameVal(fn, 1, "C75")     Cells(i, 1).Value = buf     i = i + 1     End If     If i > 100 Then Exit Sub '実験用のため100ファイルに限定させる     fn = Dir   Loop End Sub Function getSheetsNameVal(FileName As String, iSh As Integer, nRng As String)   Dim objBook As Workbook   Dim sh As Worksheet   Dim ret As Variant   On Error GoTo ErrHandler   '開く時にブックを制御(Screen,Alert,自動計算,Event)   With Application     .ScreenUpdating = False     .DisplayAlerts = False     .Calculation = xlManual     .EnableEvents = False     Set objBook = Workbooks.Open(FileName, ReadOnly:=False)     With objBook       ret = .Worksheets(iSh).Range(nRng).Value     End With     objBook.Close False   End With ErrHandler:   With Application     .EnableEvents = True     .Calculation = xlCalculationAutomatic     .DisplayAlerts = True     .ScreenUpdating = True   End With   Set objBook = Nothing   If VarType(ret) <> vbError Then     getSheetsNameVal = ret   End If End Function '------------------------------------------- p.s.バイナリでシート名を取る方法は知らないわけではないけれども、私は、そのようなコードを書く気力がありません。VBAマクロは、Runを繰返してみないと分からないものがあります。

kokoro2-2
質問者

補足

そうですね。はじめの段階からおかしいです。 あまり知らないお客さんがご自分で作られたファイルですから、いろんなのがあって当然かと…。 1回きりの処理なのでちまちまやります。

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.3

>てことは、普通に開いて、閉じての繰り返しですか? えっと,本当にごめんなさい? どこが判らなくて/何が出来なくての,その追加ご質問でしょうか。 「ブックを開いて1枚目シートの所定の番地のセルから値を取得する」(取得して何をしたいのかは,当初ご質問より全く問われていません)と最初から繰り返しご説明している事なので,改めて補足してお話しすべき内容を追加ご質問から拾いあげる事が出来ません。

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.2

ブックを3000冊開くのが遅いと思いこんでいる?実証されている?のが問題だとすれば,出来るだけ早くブックを開くマクロを組んだ方が無い方法を模索するより早く結果を出せます。 ・screenupdatingを抑制して,若しくはgetobjectで開く ・再計算は手動(xlcalculationmanual)にしておいて開く ブックを開かないで今のアプローチを堅持したいのでしたら,次の資料 http://officetanaka.net/excel/vba/tips/tips28.htm と,そこからリンクされているその次の資料 http://officetanaka.net/excel/vba/tips/tips29.htm が参考になります。

kokoro2-2
質問者

補足

すばやいご回答ありがとうございます。 よく考えれば別に開いても問題ないレベルのスピードだと思います。 ってことは、普通に開いて、閉じての繰り返しですか?

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.1

ExecuteExcel4Macroを使いたければ,そこには実際の正しいシート名を記入しなければなりません。回避する方法はありません。 素直にマクロで(見えないように・邪魔にならないように)ブックを開き,worksheets(1)の所定のセルを取得するのが最も簡易な手です。それで何か困ることがある(或いはマクロが書けない)ようなら,困り所を具体的に挙げて別途ご相談を投稿してみてください。

kokoro2-2
質問者

補足

特にExecuteExcel4Macroを使う必要はないのです。 (ファイルを開かずにセルを参照したいのです。その他でできるならそれで良いんですが) あるフォルダにあるファイルを順次読みながらセルを参照してそのシートのセルの必要な 部分だけをとって、一覧表を作りたいのです。 ファイル数は3000ほどあるので、開いていたら処理が間に合わないような・・・。 分かれば、教えてください。

関連するQ&A

  • Application.ExecuteExcel4Macroなに?

    No.373903を拝見しまして、閉じたままのブックを参照できるらしいので使いたいのですが、ブックが複数ある場合、[ ]内はどのように書けば良いのでしょう? 例) Cells(1, 1) = Application.ExecuteExcel4Macro _ ("'C:\My Documents\[Book2.xls]Sheet1'!R1C1") Book2・sheet1の部分が可変。 また、 Application.ExecuteExcel4Macro("get.document(50)") や Application.ExecuteExcel4Macro ("Halt(True)") 等の使い方もあるようですが、どなたかわかりやすく教えて頂けませんか?

  • ExecuteExcel4Macroを使ったレコードの読み込み方法につ

    ExecuteExcel4Macroを使ったレコードの読み込み方法について 下記サンプルは、ファイルを指定して、指定したファイルの中にあるシートを選択し、その中にあるデータを読込むものになっています。 読込ませるファイルのsheet1には、『 ID,顧客番号,氏名,住所,電話番号 』 が入っています。 下記サンプルでは、顧客番号フィールドのデータは読込めるのですが、該当するレコード全体を読込むにはどう組み立てればいいかよくわかりません。 すみませんが、どなたかご教授いただけませんでしょうか。よろしくお願いいたします。 Public Sub testes() ' 変数の指定 Dim OpenFileName, SheetName, Target, buf As String Dim i, TargetCol As Long, GetNames() ' 対象ブックの選択 OpenFileName = Application.GetOpenFilename("Microsoft Excel ブック,*.xls") If OpenFileName = "False" Then Exit Sub ' ファイル名に[]を付ける OpenFileName = Replace(OpenFileName, Dir(OpenFileName), "[" & Dir(OpenFileName) & "]") ' 対象ワークシート名の指定と取得 SheetName = InputBox("対象ワークシート名を入力します") If SheetName = "" Then Exit Sub Target = "'" & OpenFileName & SheetName & "'!" ' ワークシートの正誤チェック On Error Resume Next buf = ExecuteExcel4Macro(Target & "R1C1") If Err <> 0 Then MsgBox "ワークシート [ " & SheetName & " ] を読めませんので終了します。", vbExclamation Exit Sub End If On Error GoTo 0 ' [顧客番号]フィールドを探す For i = 1 To 256 If ExecuteExcel4Macro(Target & "R1C" & i) = "顧客番号" Then TargetCol = i Exit For End If Next i If TargetCol = 0 Then MsgBox "[顧客番号]フィールドが確認できません。", vbExclamation Exit Sub End If ' データの読み込み For i = 1 To 10000 buf = ExecuteExcel4Macro(Target & "R" & i) If buf = "0" Then Exit For ' シートに出力する Worksheets("sheet3").Activate ActiveSheet.Cells(i, 1) = buf Next i End Sub

  • ExecuteExcel4Macroでセル値取得

    office2010 あるフォルダにファイルを入れて、ファイルを開かずに対象シートの対象セルの値を取得したいです。 この取得したいセル情報を、変数で指定したいのです。 C:\dataに取得元のファイルが入っています。 このファイル名((1))は、いろいろ変わりますが、中にH4という文字があります。 対象シートのシート名とセルアドレスは、別のファイル(これにマクロがあります) のsettingシートで指定します。 1例ですが、settingシートの B2に11_001 C2にAF9 と設定します。 (1)のファイルで11_001というシートのAF9セル値を取得したいのです。 Dim myPath As String Dim myFile As String myPath = "C:\data\" myFile = Dir(myPath & "*H4*.xlsm") Dim sheetname As String Dim cell As String sheetname = Worksheets("setting").Range("B2") cell = Worksheets("setting").Range("C2").Value ' 'Range("A1") = ExecuteExcel4Macro("'[" & myFile & "]" & sheetname & "'!R9C32") Range("A1") = ExecuteExcel4Macro("'[" & myFile & "]" & sheetname & "'!" & cell & "") 上記で、 Range("A1") = ExecuteExcel4Macro("'[" & myFile & "]" & sheetname & "'!R9C32") は、値取得できます。 このR9C32を変数にする所で、エラーが発生します。 実行時エラー1004と。 いろいろWEB見て、’,スペース等を入れてみましたが、どうしても分からず、 Range("A1") = ExecuteExcel4Macro("'[" & myFile & "]" & sheetname & "'!" & cell & "") の最後のセル指定を、変数で設定する方法を教えて頂きたく。

  • ExecuteExcel4Macroについて

    エクセルで、フォームで挿入した「ラベル」に他シートのセルを参照させてマクロの記録をとると以下のようになります。 Sub Macro1() ActiveSheet.Labels.Add(526.5, 301.5, 76.5, 18).Select ExecuteExcel4Macro "FORMULA(""=FACE!R[7]C[10]"")" End Sub では、逆にラベルに設定された参照元を取得するにはどう記述すればようでしょうか? For Each sp In ActiveSheet.DrawingObjects i = i + 1 On Error Resume Next sh.Cells(i, 2) = TypeName(sp) sh.Cells(i, 3) = sp.Name sh.Cells(i, 4) = sp.Formula next では、取得出来ませんでした。

  • VBA(ExecuteExcel4Macro)を用いた検索ツール

    VBA(ExecuteExcel4Macro)を用いた検索ツール はじめまして。 当方Excel2007、winXPでの環境下でVBAを用いた検索ツールを作成しています。 検索対象のxlsファイルには1行目に郵便番号、氏名、住所の項目タイトル、2行目以降にデータが入力されています。 【現在の仕様】 検索対象のxlsファイルを選択→検索したい氏名(3つまで)を入力すると氏名列を順に検索し、該当した氏名のみをシートへ出力 【作りたい仕様】 検索対象のxlsファイルを選択→検索したい氏名(3つまで)を入力すると氏名列を順に検索し、該当した氏名の入力された行をシートへ出力 現在のソースは下記になりますが、どのように書き変えればいいのかが分からず困っています。 教えていただけたらと思います。よろしくお願いいたします。 ------------------------- (略) ''対象ブックを選択します OpenFileName = Application.GetOpenFilename("Microsoft Excel ブック,*.xls") If OpenFileName = "False" Then Exit Sub OpenFileName = Replace(OpenFileName, Dir(OpenFileName), "[" & Dir(OpenFileName) & "]") SheetName = InputBox("読み込むワークシート名を入力してください。") If SheetName = "" Then Exit Sub Target = "'" & OpenFileName & SheetName & "'!" On Error Resume Next buf = ExecuteExcel4Macro(Target & "R1C1") If Err <> 0 Then MsgBox "ワークシート [ " & SheetName & " ] を読めませんでした。", vbExclamation Exit Sub End If On Error GoTo 0 key1 = InputBox("検索したい氏名1を入力してください。") If key1 = "" Then Exit Sub End If key2 = InputBox("検索したい氏名2を入力してください。") If key2 = "" Then Else key3 = InputBox("検索したい氏名3を入力してください。") End If For i = 1 To 256 If ExecuteExcel4Macro(Target & "R1C" & i) = "名前" Then TargetCol = i Exit For End If Next i If TargetCol = 0 Then MsgBox "[ 名前 ]フィールドが見つかりません。", vbExclamation Exit Sub End If Dim clm As Integer For i = 2 To 10000 buf = ExecuteExcel4Macro(Target & "R" & i & "C" & Target If buf = "0" Then Exit For If buf = key1 Then Sheet3.Cells(w, 3) = buf w = w + 1 ReDim Preserve GetNames(i - 1) GetNames(i - 1) = buf ElseIf buf = key2 Then Sheet3.Cells(w, 3) = buf w = w + 1 ReDim Preserve GetNames(i - 1) GetNames(i - 1) = buf (略) -----------------

  • ExecuteExcel4Macro "FORMULA(""=R1C1"")"

    ExcelのVBAのご質問です。 マクロの自動記録にて、オートシェイプに他セル参照を入れ込みました (つまり、オートシェイプを選択状態で数式バーに=A1などと入れ込んだ)。 すると、おそらくその部分に ExecuteExcel4Macro "FORMULA(""=R1C1"")" というプログラムが出て来るのですが、 ・・・これはどう理解したら良いのでしょうか。 調べてみたのですが、ExecuteExcel4Macroというものの理解が 今いちできないのと(他シートのマクロを実行させる時に 使ったりするらしいという事しかわかりませんでした) "FORMULA(""=R1C1"")" についても、FORMULAというのはR1C1形式でなくA1形式の時に 使うものじゃなかったっけ? なぜ"が2つ? などと混乱しています。 (=A1だからR1C1なので、A2ならR2C1だな、という事だけはわかる) 私の頭では、断片的な情報の統合ができず理解に苦しんでいます。 助けてくださる方いらしたらお願いいたします。

  • エクセルVBAで他Bookを閉じたまま参照・訂正

    Cells(1, 1) = Application.ExecuteExcel4Macro _ ("'C:\My Documents\[Book2.xls]Sheet1'!R1C1") でBook2を開かずにセルを参照できますが、 1.別の記述方法があるのでしょうか。 2.逆に、閉じたBook2のセルに対し、値を訂正できないでしょうか。 よろしくお願いいたします。

  • ExecuteExcel4Macroでのデータ取得

    office365 従来、ドメイン配下のサーバに登録されているファイルをExecuteExcel4Macroでファイルを開かずにセルの値を抽出していました。 今回、サーバがドメイン配下でなくなったので参照ツールを変更しようとしています。 サーバ名が○○○○ IDがID パスワードがPW で Gドライブのinフォルダに登録されてる ファイル名:A001.xslsm シート名 :1_001_01 セルアドレス:A1 の内容を refer.xslmファイルのセルアドレスR1にExecuteExcel4Macroで表示させるマクロをベタで教えていただきたく。 ExecuteExcel4Macroでデータ抽出する構成にしているのは、参照するファイル容量が大きいので、 ファイルを開いてコピペ方式だとレスポンスが悪いからです。 サーバにはリモートディスクトップ接続をバッチファイルで起動したら入れる様にはなってます。 kido.batの中身 @echo off Set SERVER=○○○○ Set USERNAME=ID Set PASSWORD=PW Cmdkey /generic:TERMSRV/%SERVER% /user:%USERNAME% /pass:%PASSWORD% Start mstsc /v:%SERVER% Timeout 3 Cmdkey /delete:TERMSRV/%SERVER% kido.batを起動するマクロは下記 Sub RunBatShell() Dim dProcessId As Double Dim sPath sPath = "C:\Users\ID\Desktop\kido.bat" dProcessId = Shell(sPath) End Sub 上記バッチ内容もマクロに記述出来たら1本化したく。

  • エクセルvbaに関する質問です

    ExecuteExcel4Macroを使った際について質問があります。 別ブックのセルを参照したいために、ExecuteExcel4Macroを使いました。 1つ目のmsgboxではパスを変数で、二つ目のmsgboxではパスを直書きしています。 下記のサンプルプログラムで2つとも同じものを表示させたいのですが、別の結果が表示されます。 =====サンプルプログラム===== Sub Sample1() Dim name As String Dim path As String Dim sheet As String path = "C:\Users\USER\Desktop\シフト表\新しいフォルダ\" name = "book1.xls" sheet = "Sheet1" Application.DisplayAlerts = False MsgBox ExecuteExcel4Macro("'" & path & "[" & name & "]" & sheet & "'!R1C1") MsgBox ExecuteExcel4Macro("'C:\Users\USER\Desktop\シフト表\新しいフォルダー\[book1.xls]Sheet1'!R1C1") Application.DisplayAlerts = True End Sub ===ここまで===== 実際のbook1.xlsのA1セルには「1」が入っているのですが、変数で書いた場合のみ「aaaaaa」が表示されます。 どうかご教授いただけたら幸いです。

  • VBAでのExecuteExcel4Macroの値取得でエラー

    こんにちは。 Excel VBAより、ExecuteExcel4Macroを実行して、 外部のExcelファイルの、名前(店名、月度)を定義したセルの値を取得したいと思っています。 店名:文字列型 月度:Date型 そこで、ExecuteExcel4Macro()を実行し、以下のような処理を加えました。 ------------------------------------------------------------------------------------ dim 店名 as Variant, 月度 as Variant If 外部マクロ実行("'c:\[test.xls]出勤簿'!店名", 店名) = False Or _ 外部マクロ実行("'c:\[test.xls]出勤簿'!月度", 月度) = False Then MsgBox "取得失敗", vbExclamation End End If Public Function 外部マクロ実行(com As String, ByRef result As Variant) As Boolean On Error GoTo erron3 result = ExecuteExcel4Macro(com) On Error GoTo 0 外部マクロ実行 = True Exit Function erron3: 外部マクロ実行 = False End Function ------------------------------------------------------------------------------------ これを実行したところ、「月度」の値は取得できるのですが、 「店名」の値には「エラー 2042」という値が入ります。 ※dirname, filenameは正しい値が入っています。 ※シート「出勤簿」および「月度」「店名」のセル名の定義も存在します。 test.xlsを開いてるときは、上記の現象は起こらず、 「店名」の値は正常に取得できます。 また、試しに、test.xlsを開き、 Worksheets("出勤簿").Range("店名")を実行すると、正常な値が取得できました。 まとめると、 ・閉じたブックの、あるシートにある、セルに定義された名前を指定して ・ExecuteExcel4Macroで、文字列が入っている値を取得しようとした時、 ・正常に値が取得できない という現象に遭遇しています。 3日ほど調べているのですが、どうしても原因が分かりません。 解決策をお持ちの方、いらっしゃいましたらアドバイスを頂けると助かります。 環境:WindowsXP Pro SP3 Excel 2003 (11.5612.5606) 以上、よろしくお願いいたします。

専門家に質問してみよう