Excel'97で 実行時エラー '1004' が出る

質問

毎日更新されるCSVファイルがあります。
このファイルをExcelに変換して、他のファイルにリンクしています。
CSVファイルを開かなくても、データを更新できるよう、マクロを組みました。
Excel2000では、問題なく動くのですが、’97で実行すると、
実行時エラー '1004': アプリケーション定義またはオブジェクト定義のエラーと出てしまいます。
メインに使っているPCが'97なので、
色々調べてみたのですが、私の知識ではわからず、困っています。
詳しい方がいらっしゃれば、教えて頂きたく思います。
コードは以下のようなものです。

Private Sub Workbook_Open()

Dim Workbooks As Variant
Dim Sheets As Variant
Dim Filename As Variant
Dim wR As Long

ThisWorkbook.Sheets("Sheet1").Activate
Cells.ClearContents
Filename = Application.GetOpenFilename("CSV ファイル(*.csv),*.csv")

ここで、デバック
　　　↓
　　With ActiveSheet.QueryTables.Add _
(Connection:="TEXT;" & Filename, Destination:=Range("A1"))
.TextFileCommaDelimiter = True

さらにここでも、デバック(実行時エラー1004 外部データ範囲を
更新するためのテキスト　ファイルが見つかりません)
　　　　　　↓
.Refresh BackgroundQuery:=False

End With

With ActiveSheet
.Columns("B:C").Delete shift:=xlToLeft
'(CSVファイルのA,B列は不要のため、削除）
wR = .Range("B" & Rows.Count).End(xlUp).Row
.Range("A1") = "=B1&C1&D1"
.Range("A1").AutoFill Destination:=Range("A1:A" &wR), Type:=xlFillDefault

End With

End Sub

回答No.6

Wendy02 · ベストアンサー

追伸：

#換えればよいだけです
ちょっと、バカなことを書いてしまいました。（^^;
ではなくて、統一してしまえばよいです。

>#If numVer = 8 Then　　　'ディレクティブ
>　　　LineBuf = ex97Split(TextLine, DELIM)
>　　 #Else
>　　　LineBuf = Split(TextLine, DELIM)
>#End If
　　　↓

LineBuf =ex97Split(TextLine,DELIM)
としてしまってください。

お礼

このコードに替えたら、上手くいきました！！
しばらく悩んでいたので、とてもうれしいです。
本当にありがとうございました！！

補足

ありがとうございます。
試してみて、必ず結果をご報告致します。

回答No.5

Wendy02

こんにちは。

やはり、ダイレクティブがうまく行っていないようですね。初めて使うワザだったので、私は、勉強不足でした。その部分は、勉強しなおします。今回には、間に合いそうにはありませんが、前の文章に触れていましたが、直し方は、簡単です。

>　　　LineBuf = Split(TextLine, DELIM)
>　　　　　　　　　↑ここが反転

その反転したものを、

　　　LineBuf = ex97Split(TextLine, DELIM)

に換えればよいだけです。一応、97では確認済みです。

回答No.4

Wendy02

こんにちは。

一応、こちらで、その後で、Excel　97 に通して可動は確認しました。
私は、なるべく、標準モジュールに、これらを入れてあったほうが負担が少ないと考えていますが、もう一度調べてみます。

>＞LineBuf = ex97Split(TextLine, DELIM)
>の　Split　が反転した状態でエラーになるとのことでした。

この意味が、もう少し複雑な状況かもしれません。厳密にどこの部分なのかはっきりすればよいのですが、そうでないと、探すのに苦労します。

それで、だめなら、もう一度、ダイレクティブ(#IF)分岐で、コードの完全分離に切り替えます。

補足

こんばんは。
お返事が遅くなってしまい、大変申し訳ありません。
（別のファイルで悪戦苦闘してしまったので、こちらのファイルになかなか取り掛かれませんでした）

本日、自分で試してみたところ、
「sub 又は　functionが定義されていません」
とエラーが出てしまいました。

#If numVer = 8 Then　　　'ディレクティブ
　　　LineBuf = ex97Split(TextLine, DELIM)
　　 #Else
　　　LineBuf = Split(TextLine, DELIM)
　　　　　　　　　↑ここが反転
　何かまだ、設定の足りないものや、省略しない方が良い
コードがあるのでしょうか？
　社内にはマクロのわかる人がいない為、Wendy02さんのご回答のおかげで何とかやっていけている状況です。
お手隙の時でも構いませんので、どうぞ宜しくお願い申し上げます。

回答No.3

Wendy02

こんばんは。

今回のマクロは、私のとってはやはり難しかったです。初めて使うワザもあります。
初めて使うワザやお蔵入りのワザがうまく動くかどうかです。なんと言っても、Val 関数も97でないことに気がつきました。本当は、ちゃんと97をインストールしたほうがよいとは思うのですが、そのためには、今までの上位バージョンをアンインストールしなければならないのです。うまくいかなければ、また、手直しするつもりです。

それから、ご自身で作られたものかはわかりませんが、
>Dim Workbooks As Variant
>Dim Sheets As Variant

これは、絶対いけません。Excel VBAを知らない人で、VBを書く人に時々見られる変数ですが、エラーにはならないのですが、予約語として考えたほうがよく、それを使ったら、場合によっては、そのオブジェクトを殺してしまいます。

なお、ex97Split だけでも、上位バージョンで動くはずです。ただ、Split 関数よりも、多少処理スピードが遅いのです。

>Wendy02さんが、OpenText　や　QueryTables　は、相性の良くないコードだから、あまり使わない方が良いと書かれていた解答は目にしていたのですが、

私が書いたのは、ひとつは引数を確証なく省略すると失敗することがあると書いたのと、QueryTable を貼り付けた後は、その内部のQueryTable を削除したほうが後々処理に困らない、と書いたのです。

そういう私は、ここ１年半ぐらいは、QueryTables　を使ってお終いにすることもあります。理由は、皆が自分の手でマクロを書けるように願い、記録マクロを使って、ちょっと加工するだけで、こんなものが出来ることを分かってもらうために書いています。

中には、バカにされたと誤解する人もいるようですが、結果が同じなら、少しぐらい処理スピードが遅くても、簡単なほうがよいと思います。Visual Basic のように難しい書き方よりも、VBAは、VBAの書き方があってもよいのではないか、と思っています。そんな私でも、今回のように避けられない時もあります。

'ThisWorkbook モジュール
'------------------------------------------------
Private Sub Workbook_Open()
　Call CSVImportMacro
End Sub

'標準モジュール
'------------------------------------------------
Sub CSVImportMacro()
Dim FName As String
Dim Fno As Integer
Dim TextLine As String
Dim numVer As Integer　'ディレクティブIfの外で取るため
Dim LineBuf As Variant
Dim Ub As Integer
Dim i As Long
Const DELIM As String = ","　'区切り文字
Const START As String = "B1"　'B1 から
Const LINESTART As Integer = 3 '３列目から

　With ActiveWorkbook.Worksheets("Sheet1")
　　 .Activate
　　 .UsedRange.ClearContents

　FName = Application.GetOpenFilename("CSV ファイル(*.csv),*.csv")
　If StrComp(FName, "False") = 0 Or FName = "" Then Exit Sub
　Fno = FreeFile()
　Open FName For Input As #Fno
　Do Until EOF(Fno)
　 Line Input #Fno, TextLine
　　 TextLine = Application.Substitute(TextLine, """", "")　'Quotationを取る
　　 numVer = Left$(Application.Version, 2)
　　 #If numVer = 8 Then　　　'ディレクティブ
　　　LineBuf = ex97Split(TextLine, DELIM)
　　 #Else
　　　LineBuf = Split(TextLine, DELIM)
　　 #End If
　　 Ub = UBound(LineBuf)
　　 If Ub > LINESTART - 1 Then
　　 LineBuf = IndexLineArray(LineBuf, LINESTART - 1) '配列なので、-1
　　 Ub = UBound(LineBuf)
　　 .Range(START).Offset(i).Resize(, Ub).Value = LineBuf
　　 i = i + 1
　　 End If
　Loop
　Close #Fno
　.Range("A1", .Range("B65536").End(xlUp).Offset(, -1)).FormulaLocal = "=B1&C1&D1"
　.Range("A1").EntireColumn.AutoFit
　End With
End Sub
Private Function IndexLineArray(ByVal BaseArray, Optional StartCol As Integer = 0, Optional EndCol As Integer = 0)
'配列切り分け BaseArray =配列, StartCol スタート列(-1), EndCol =終了列(-1)
Dim v As Variant
Dim i As Integer
Dim Ar() As Variant
Dim cnt As Integer
cnt = 0
For Each v In BaseArray
　If cnt >= StartCol And EndCol = 0 Then
　ReDim Preserve Ar(i)
　　Ar(i) = v
　i = i + 1
　ElseIf cnt >= StartCol Then
　　If cnt <= EndCol Then
　　　ReDim Preserve Ar(i)
　　　Ar(i) = v
　　 i = i + 1
　　End If: End If
　cnt = cnt + 1
Next v
IndexLineArray = Ar()
End Function

Private Function ex97Split(TextLine As Variant, Optional DELIM As String = ",")
'Excel97 用Split関数
Dim OutPutArray() As Variant
Dim LineLength As Integer
Dim i As Long
Dim FirstLocate As Integer
Dim FindLocate As Integer
Dim WordLen As Integer
Dim QtFind As Integer
Dim QtNetFind As Integer
Dim QtFlg As Boolean

LineLength = Len(TextLine)
If LineLength = 0 Then Exit Function
FirstLocate = 1
　Do
　QtFind = InStr(FirstLocate, TextLine, Chr(34))
　FindLocate = InStr(FirstLocate, TextLine, ",")
　If FindLocate = 0 Then
　If Len(Mid$(TextLine, FirstLocate)) > 0 Then
　　ReDim Preserve OutPutArray(i)
　　OutPutArray(i) = Mid$(TextLine, FirstLocate)
　End If
　Exit Do
　End If

　If QtFlg Or QtFind = 1 Then
　QtNetFind = InStr(QtFind + 1, TextLine, Chr(34))
　WordLen = QtNetFind - QtFind - 1
　ReDim Preserve OutPutArray(i)
　OutPutArray(i) = Mid$(TextLine, QtFind + 1, WordLen)
　FirstLocate = QtNetFind + 2
　QtFlg = False
　Else
　WordLen = FindLocate - FirstLocate
　ReDim Preserve OutPutArray(i)
　OutPutArray(i) = Mid$(TextLine, FirstLocate, WordLen)
　FirstLocate = FindLocate + 1
　End If
　'オプショナル
　If QtFind = 2 Or FindLocate + 1 = QtFind Then
　　 QtFlg = True
　End If
　i = i + 1
Loop
　ex97Split = OutPutArray()
End Function

補足

こんなに複雑なコード、ありがとうございます。
その他の点につきましても、丁寧に教えて頂き、感謝この上ありません。

自分の使っているPC（2000）では、正常動作しました。
'97がインストールされているPCが私の勤務時間中、空かなかったので
手が空いたら、試してくれるようお願いしておいたところ、

＞LineBuf = ex97Split(TextLine, DELIM)
の　Split　が反転した状態でエラーになるとのことでした。
ただ、実行して頂いた方は、ほとんどBVAは知らないので、
エラーメッセージの内容も、確認できていない状態です。
せっかくコード書いて頂いたのに、すみません。

明日、自分でもう一度試してみて、結果をご報告したいと思います。
少々、ご猶予下さいませ。

回答No.2

Wendy02

こんばんは。

>Cells.ClearContents　　の後ろに入れてみましたが、
「実行時エラー424　オブジェクトが必要です」が出てしまいます。

これは、ActiveSheet.Cells.ClearContents とかしないと、ThisWorkbookなどは、ローカル・オブジェクトとしてのシートが存在しません。

ですが、簡単だと思っていたら、今、間違いに気が付きました。

Workbooks.OpenText _

これでは、そのファイル自体を開けてしまうことですね。それをコピーしなければならないのでは、話が違ってしまいます。私は、97用のこの内容のコードはギブアップしたことがあり、97で、この関連のコードは、それ以来何年も試したことがありません。

まず、配列のSplit関数が97にはないので、自分で作らなくてはなりません。上位バージョンで作って、最後に97に入れてみないと、一体、どこがエラーが出るか分からないのです。これは、私には、この部分を中途半端に知っているだけに大変な内容です。以前は、定番だったこのコードも、今は、見られないコードです。

それに、ExcelのVersion によってコードを換えるプログラムを書かなくてはなりません。統一的なコードは出来ません。甘く見すぎていました。少し、時間をください。

補足

こんばんは。
＞甘く見すぎていました＜いえ、甘く見ていたのは、私の方です。
部分的に少しだけ書き換えれば済むものと思っていました。
Wendy02さんが、OpenText　や　QueryTables　は、相性の良くないコードだから、あまり使わない方が良いと書かれていた解答は目にしていたのですが、VBAに取り組んで１ヶ月あまりの私には、他のコードの書き方がわからず、行き当たりばったり的にマクロを組んでいました。
＞配列のSplit関数が97にはないので、自分で作らなくてはなりません。
このことも全く知らず、お恥ずかしい限りです。
お手数をかけてしまって、本当に申し訳ありません。

やりたいことは、QNo.3084424　のままです。
この、ExcelファイルのＡ列（関数式）をVlookupの検索値として、他のファイル使用しています。
　今のところ、問題なく検索できているので、もしかしたら、文字列で取り出す必要はないのかもしれません。
（たまたま、探し出したコードに、文字列を取り出すコードが含まれていため、そのまま引用してしまいました。）
　バージョンの違いで、これほど大変なことになるとは、思いもかけないことでした。
　大変なご迷惑とは思いますが、どうぞよろしくお願い致します。

回答No.1

Wendy02

こんばんは。

Excel 97 で、

>ActiveSheet.QueryTables.

というのは可能なのでしょうか？97にはなかったはずです。

単に、Workbooks.OpenText Filenames:= ......
で、TextQualifier を決めてあげる方法で出来たはずです。

その部分を交換すれば、出来ると思います。

補足

ご回答ありがとうございます。
>ActiveSheet.QueryTables　
が'97でないということは、知りませんでした（汗）

甘えてしまって申し訳ないのですが、
>Workbooks.OpenText Filenames
はどのように設定したら、良いのでしょうか？

Cells.ClearContents　　の後ろに入れてみましたが、
「実行時エラー424　オブジェクトが必要です」が出てしまいます。
（意味がわかっていなくて、ネット上から探してきたコードを貼り付けていますので、不要なものも入っているかと思いますが…）

Workbooks.OpenText _
Filename:=Flname, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
　　　↑
今のコードでは、後でAB列を削除しているのですが、（ExファイルのA列には数式を設定、B列以降にデータ貼り付け）　
必要なのはCSVファイルのC：H（数字ですが、頭の００も抽出したいため）ですので、必要な列だけを選択するコードが入れられれば、いいと思うのですが、このコードがどこを指しているのが、理解していないままです。
なさけないこと、この上ないのですが、教えて頂ければ幸いです。
どうぞお願い致します。