こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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)

以上、よろしくお願いいたします。

投稿日時 - 2008-08-16 14:00:54

QNo.4255076

困ってます

質問者が選んだベストアンサー

'-------------------------------------------------
Sub Test()
 Dim 店名
 Dim 月度

 店名 = 外部マクロ実行("'C:\[test.xls]出勤簿'!店名")
 月度 = 外部マクロ実行("'C:\[test.xls]出勤簿'!月度")

   If 店名 = 0 Or 月度 = 0 Then
     MsgBox "取得失敗", vbExclamation
   Else
     MsgBox 店名 & " @ " & Format(月度, "yyyy/m/d")
   End If

End Sub
'-----------------------------------------------

Function 外部マクロ実行(com As String)
  外部マクロ実行 = ExecuteExcel4Macro(com)
End Function
'-----------------------------------------------


●ただ今回の場合は、Functionは無しでもいいような。。。

'----------------------------------------------
Sub Test555()
 Dim 店名
 Dim 月度

 店名 = ExecuteExcel4Macro("'C:\[test.xls]出勤簿'!店名")
 月度 = ExecuteExcel4Macro("'C:\[test.xls]出勤簿'!月度")

   If 店名 = 0 Or 月度 = 0 Then
     MsgBox "取得失敗", vbExclamation
   Else
     MsgBox 店名 & " @ " & Format(月度, "yyyy/m/d")
   End If

End Sub
'----------------------------------------------------

以上。

投稿日時 - 2008-08-16 15:45:51

ANo.1

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

こんにちは。

ExecuteExcel4Macro というスタイルは、直接、マクロ関数を取る以外は、追いかけないほうがよいかもしれませんが、そのコード自体は間違いありません。

>「エラー 2042」という値が入ります。

という意味は、「#N/A 」の戻り値です。
だから、そのエラー値は、数式側だけの問題です。数式を調べないといけません。

"'c:\[test.xls]出勤簿'!店名"

私としては、その書き方は、一見良いのですが、実際、ルートに置くわけではないでしょうから、失敗しやすいです。

これは、目的のファイルが開いていない状態で扱うときですが、実務上では、ファイルが開いていなければ、開けて、Evaluate で取るというのが、一般的な書き方です。そうでなければ、DAOやADOを使って開くというスタイルになります。

全体的に、Excelマクロとしては変則的なコードだと思います。

今回の場合は、ユーザー定義関数側では、参照渡しは、あまり良いとは思いません。実行時エラーと両方を取ろうとしたので、Boolean値のユーザー定義関数自体を戻り値にしたのだと思いますが、この種のものは、実行時エラーが発生するのは、コードの自体の間違いか、ファイルが存在しない間違いですから、Dir 関数などの回避の仕方はあると思います。単に、数式の戻り値を、ユーザー定義関数側に返すだけで十分な気がします。

なお、そのコードは「外部マクロ実行」ではありません。

'----------------------------------------
  Dim 店名 As Variant, 月度 As Variant
  Dim nShiki1 As String
  Dim nShiki2 As String
  Const nPATH = "'C:\Documents and Settings\[UserName]\My Documents\"
  Const nWBNAME = "test.xls"
  Const nSHNAME = "出席簿"
  Const nADDRESS1 = "店名" '名前登録はそのまま、アドレスなら、R1C1方式
  Const nADDRESS2 = "月度"
  
  nShiki1 = "[" & nWBNAME & "]" & nSHNAME & "!" & nADDRESS1
  店名 = Evaluate(nShiki1) 'ファイルが開いているかチェックする
  
  If IsError(店名) Then
    nShiki1 = nPATH & "[" & nWBNAME & "]" & nSHNAME & "'!" & nADDRESS1
    nShiki2 = nPATH & "[" & nWBNAME & "]" & nSHNAME & "'!" & nADDRESS2
    
      'Debug.Print nShiki ''数式の内容チェック

    If 外部マクロ実行(nShiki1, 店名) = False Or _
      外部マクロ実行(nShiki2, 月度) = False Then
      MsgBox "取得失敗", vbExclamation
      Exit Sub
    End If
  Else
    nShiki2 = "[" & nWBNAME & "]" & nSHNAME & "!" & nADDRESS2
    月度 = Evaluate(nShiki2)
  End If

End Sub
'-----------------------------------

投稿日時 - 2008-08-16 15:57:40

あなたにオススメの質問