Excel97 VBA とVBSのsplit関数

質問

新しい職場で、Excel VBAでデータを取り込み、それを改行文字ごとにsplit関数で区切って配列に入れようとしました。

arr=split(文字列,vbcrlf)

って感じでですね。しかし、なんとその職場のExcelのバージョンが97のため、VBAでsplit関数が使えず、この意図が実現できませんでした。そのため、ネットで調べてVBScriptのsplit関数を流用しようとしたのですが、「終了していない文字列型の定数です。」とエラーが出てしまいます。ソースは次のとおりなのですが、どう直せばいいでしょうか。改行文字で区切るのでなく、カンマなどで区切るのなら、上手く出来るのですが・・・。

Sub sample()
Dim vs As Object
Dim rtn As Variant
Dim q As String
Dim buf As String

q = Chr$(34) 'ダブルクォーテーション
Set vs = CreateObject("ScriptControl")

vs.Language = "VBScript" 'Excel97のVBAにはsplit関数がないのでVBSからsplitを流用

buf = "1" & vbCrLf & "2" & vbCrLf & "3" '改行で区切った文字。実際は、もっと複雑な文字を取り込みます。
rtn = vs.Eval("split(" & q & buf & q & ", vbCrLf)") 'Excel97のVBAにはsplit関数がないのでVBSからsplitを流用
Debug.Print rtn(1) 'ここで「終了していない文字列型の定数です。」エラーが出る

End Sub

回答No.2

WindFaller · ベストアンサー

こんばんは。

97を想定して作ってみました。97で試したわけではありませんが、試してみてください。
他にも、Replace関数がなかったと思います。

'//標準モジュール
Function mSplit(ByVal TextLine As String, ByVal Delim As String) As Variant
　Dim k As Integer
　Dim i As Integer
　Dim t As Integer
　Dim f As Integer
　Dim Ar As Variant
　TextLine = Trim(TextLine)
　k = Len(TextLine) - Len(Application.Substitute(TextLine, Delim, ""))
　k = k / Len(Delim)
　f = 1
　ReDim Ar(k)
　t = f
　For i = 1 To k + 1
　　t = InStr(t, TextLine, Delim)
　　If t > 0 Then
　　　Ar(i - 1) = Mid(TextLine, f, t - f)
　　　f = t + Len(Delim)
　　　t = t + Len(Delim)
　　Else
　　　Ar(i - 1) = Mid(TextLine, f)
　　End If
　Next i
　mSplit = Ar
End Function

'//

お礼

う～ん。短い文字列なら良いのですが、CSVとかから取り込んだ複雑な文字列だと「アプリケーション定義またはオブジェクト定義のエラーです」エラーが出ちゃいました。自分でも考えて、色々やってみます。

回答No.5

WindFaller

#4の追加：(解決に結びつくものではありませんので、すみません）

Split は別として、ここでは、成功しているようですが、問題は、そのデータの長さかな？
そもそも、対象は、テキストファイルとしたら、一回にどれだけ読み込むかにもよります。

エクセルマクロでｃｒｌｆを含むデータを複数セルに分割して入力させたいのですが」
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1259608847
まあ、この回答者のような手抜きのコードをふつうは書きませんが。

つくづく、この手(Text Qualifier)の質問は、千差万別です。未だ、別の質問も解決していません。
しかし、こちらの自作Splitが問題あれば、考えなおさなくてはなりません。

お礼

splitの代わりを自作すること自体は、割りと何とかなりそうです。ありがとうございました。

回答No.4

WindFaller

#2の回答者です。

>CSVとかから取り込んだ複雑な文字列だと「アプリケーション定義またはオブジェクト定義のエラーです」エラーが出ちゃいました。自分でも考えて、色々やってみます。　

どのような形でインポートしているかしりませんが、私の所のコードでエラーが出たのでしょうか？
ふつう、そのエラーは、名指ししたオブジェクトが、その範囲ではないところのものだと、そのようなエラーが出ます。

典型的な例は、Sheet2のローカルモジュールで、
　Sheets("Sheet1").Range(Cells(3, 3), Cells(5, 4)).Select
と書いた場合ですね。Rangeの中のCellsプロパティとのつなぎが合っていません。
こういう場合は、標準モジュールに書くべきなのですね。

昔、作ったCSVインポート用のマクロをアップしようと思いましたが、急遽取りやめることにしました。

うっかりしていました。今、気がついたのですが、よく考えると、"1" & vbCrLf & "2" & vbCrLf & "3" の一般的な行の終わりが存在していないので、これは、CSVではありません。こういうファイル形式をなんと呼ぶのかは分かりませんが、シーケンシャルの類だと思います。通信機器などに発生するデータのようですね。それをインポートする方法は、まったく違いますね。

まず、データ数をカウントして、ストックして、それを強制的に行の桁数を作っていかなくてはなりません。
そういうことではありませんか？もしくは、縦を横にする工夫が必要です。

>"1" & vbCrLf & "2" & vbCrLf & "3"
vbCrLf は、デリミタには違いありませんが、こういうスタイルが途切れることなく続くのではないでしょうか？もしそうなら、今までの方向性は、考え方を変えないと無理です。

お礼

ありがとうございました。

回答No.3

WindFaller

#2の回答者の追加です。

>Debug.Print rtn(1) 'ここで「終了していない文字列型の定数です。」エラーが出る

これは、CR+LF　（改行コード）が原因では？VBScript などのスクリプト言語では、CR+LFコードは、正しく処理できないのではないかと思います。おそらく、カンマ区切りなどでは、通るはずです。
CR+LFを別のものに置き換えれば、可能かと思いますが、97では、Replace関数がないので、置き換えには、Mid関数ぐらいしか思い当たりません。

お礼

VBScriptを苦肉の策で使うのには、色々と面倒なことが多いですね。

回答No.1

bin-chan

> rtn = vs.Eval("split(" & q & buf & q & ", vbCrLf)") 'Excel97のVBAにはsplit関数がないのでVB Sからsplitを流用
唐突に q が出てきたが、これは何？ダブルクォーテーション？

rtnが何を受け取ったか、確認が先ですね
現在のままで、rtn(0)を表示できるなら、デリミタが存在しないから。

お礼

ありがとうございました。

補足

上の方のコードに書いていますが、qはダブルクォーテーションです。Evalの中にダブルクォーテーションを幾つもネストさせると分かりにくいので、代わりにこれをやってみました。