Excel VBA インポートファイル名指定したい

質問

Excel VBA 初心者です。

スイッチを押すと、テキストファイルのインポートダイアログを開いて、外部データの取り込みをしたいと考えています。
指定セルにインポートを設定＋シートにマクロ実行スイッチを配置＋マクロの自動記録の記述を参照してマクロを登録し、そのスイッチを押す事で、正常にインポート作業を行なう事ができました。

しかし不明点が２箇所ほどあります。

１．ダイアログを開いたとき、ファイル拡張子を指定したいのですが、どうすれば指定できますか？
２．ダイアログを「キャンセル」で閉じると、「実行時エラー１００４：アプリケーション定義またはオブジェクト定義のエラー」と表示されるのですが、解決方法はどうすればいいですか？

　※マクロの自動記録を参照して記述したマクロ処理
Sub Macro1()

Sheets("HIN").Select
Range("A1").Select
ActiveCell.Offset(2, 0).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Sheet1").Select

End Sub

環境：EXCEL2000、VB6.0

以上、宜しく御願い致します。

回答No.2

end-u · ベストアンサー

>マクロ登録前の段階ではカンマ区切りでデータ更新できていたのですが、マクロ登録後はカンマ区切りになりません。
あら、失礼しました。
#そんな仕様だったかしらん。

Sub test2()
　　Dim qt As QueryTable
　　Dim b　As Boolean
　　Dim x　As Variant

　　On Error Resume Next
　　Set qt = Sheets("HIN").Range("A3").QueryTable
　　On Error GoTo 0
　　If qt Is Nothing Then
　　　　MsgBox "no QueryTable": Exit Sub
　　End If

　　x = Application.GetOpenFilename(",*.txt")
　　If VarType(x) = vbBoolean Then
　　　　MsgBox "cancel"
　　Else
　　　　b = qt.TextFilePromptOnRefresh
　　　　qt.TextFilePromptOnRefresh = False
　　　　qt.Connection = "TEXT;" & x
　　　　qt.TextFileCommaDelimiter = True '■カンマ区切りを設定
　　　　'qt.TextFileTabDelimiter = True 'タブ区切り。（必要に応じて）
　　　　qt.Refresh BackgroundQuery:=False
　　　　qt.TextFilePromptOnRefresh = b
　　End If
　　Sheets("Sheet1").Select
　　Set qt = Nothing
End Sub

>qt.TextFileCommaDelimiter = True
この行を挿入です。

ちなみに、（あくまで参考）
手動で更新する事がなければ、
毎回QueryTableでTextだけ残してQueryTableの定義は削除、という処理でも良いかもしれません。
以下はActiveSheetのRange("A3")起点でtextのみ取り込む例。

Sub test3()
　　Dim x As Variant
　　Dim n As Long

　　x = Application.GetOpenFilename(",*.txt")
　　If VarType(x) = vbBoolean Then
　　　　MsgBox "cancel"
　　　　Exit Sub
　　End If
　　With ActiveSheet
　　　　'場合によっては既存データ消去の必要あり
　　　　'n = .Cells(.Rows.Count, 1).End(xlUp).Row
　　　　'If n >= 3 Then
　　　　'　　.Rows("3:" & n).ClearContents
　　　　'End If
　　　　With .QueryTables.Add(Connection:="TEXT;" & x, _
　　　　　　　　　　　　　　　Destination:=.Range("A3"))
　　　　　　.RefreshStyle = xlOverwriteCells
　　　　　　.FillAdjacentFormulas = False
　　　　　　.AdjustColumnWidth = False
　　　　　　.PreserveFormatting = True
　　　　　　.TextFilePlatform = xlWindows
　　　　　　.TextFileStartRow = 1
　　　　　　.TextFileParseType = xlDelimited
　　　　　　.TextFileTextQualifier = xlTextQualifierDoubleQuote
　　　　　　.TextFileTabDelimiter = True
　　　　　　.TextFileCommaDelimiter = True
　　　　　　.TextFileConsecutiveDelimiter = False
　　　　　　.TextFileSemicolonDelimiter = False
　　　　　　.TextFileSpaceDelimiter = False
　　　　　　.Refresh BackgroundQuery:=False
　　　　　　.Parent.Names(.Name).Delete
　　　　　　.Delete
　　　　End With
　　End With
End Sub

お礼

end-u 様　アドバイス有難う御座います。

こちらの説明不足で何度もお手数取らせてしまい大変申し訳有りません。
　>qt.TextFileCommaDelimiter = True
の追加でうまくいく事ができました。

有難う御座いました。

回答No.1

end-u

既に作ってある[外部データ範囲]（QueryTable）を更新するケースですね？
その場合、[データの更新]ダイアログでファイル拡張子を指定するのはちょっと面倒です。（多分）
手動操作をエミュレートするようなコードになってしまいそう。

なので、[外部データ範囲のプロパティ]で指定するところの
「更新時にファイル名を確認」オプション（TextFilePromptOnRefreshプロパティ）の設定を一時的にOffにして、
GetOpenFilenameメソッドでファイルを選択するようにしたほうが簡単そうです。

Sub test()
　　Dim qt As QueryTable
　　Dim b　As Boolean
　　Dim x　As Variant 'キャンセルに対応するためVariant型でファイル名を受ける

　　'"HIN"SheetのA3セルが含まれているQueryTableをSet
　　On Error Resume Next
　　Set qt = Sheets("HIN").Range("A3").QueryTable
　　On Error GoTo 0
　　'QueryTableが無ければExit
　　If qt Is Nothing Then
　　　　MsgBox "no QueryTable": Exit Sub
　　End If

　　'GetOpenFilenameメソッドで拡張子を指定↓
　　x = Application.GetOpenFilename(",*.txt")
　　'キャンセル時はGetOpenFilenameメソッドはvbBoolean型を返す
　　If VarType(x) = vbBoolean Then
　　　　MsgBox "cancel"
　　Else
　　　　'「更新時にファイル名を確認」オプションを一時記憶
　　　　b = qt.TextFilePromptOnRefresh
　　　　'「更新時にファイル名を確認」オプションを外す
　　　　qt.TextFilePromptOnRefresh = False
　　　　'GetOpenFilenameで得たファイル名をセット
　　　　qt.Connection = "TEXT;" & x
　　　　qt.Refresh BackgroundQuery:=False
　　　　'「更新時にファイル名を確認」オプションを戻して手動時にも対応の余地を残す
　　　　qt.TextFilePromptOnRefresh = b
　　End If
　　Sheets("Sheet1").Select
　　Set qt = Nothing
End Sub

でも、既に作ってあるQueryTableの更新の為に『マクロ実行スイッチを配置』するより
[外部データ範囲]範囲（A3）右クリック[データの更新]か、
コマンドボタンユーザー設定から[データ]-[データの更新]ボタンをメインメニューに配置するだけでも良さそうな気もしますが。
#確かにファイル拡張子の指定はできないですけどね。

お礼

end-u 様、アドバイス有難う御座います。

ご指摘通り、現時点では[データの更新]ボタンで更新させています。
しかし、これを不特定の人が操作する時に操作が判り難い為、どうにかしたいと思っていました。
今時間が取れずにまだ確認出来ていませんが、近いうちに結果報告したいと思います。

補足

end-u 様、やっと試す事ができました。

結果、データを取り込めました。
しかしテキストデータをカンマ区切りで読み込みたいのですが、連続したデータになってしまいます。
マクロ登録前の段階ではカンマ区切りでデータ更新できていたのですが、マクロ登録後はカンマ区切りになりません。

どこを修正すればいいでしょうか？

お手数ですが、宜しく御願い致します。