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

解決済みの質問

EXCEL VBAでダイアログを表示、テーブル選択

win7/EXCEL、ACCESSともに2010を使用しています。

EXCEL/VBAで、ACCESSのテーブル名を指定しワークシートにコピーする下記のコードなんですが
コピーしたいテーブルをユーザがダイアログボックスから選択するように修正できればと思って
おります。Application.Filedialog(msofileDialogPicker)を合わせればいいのでしょうか?
色々試してみていますが、うまくいってくれません。どのようコードを組み合わせればよいのか教えていただきたく投稿させていただきました、何卒、よろしくお願いいたします。

'データをワークシートにコピー

Sub テーブルデータコピー()

'テーブルのデータをワークシートにコピーする
'Microsoft ActiveX Data Object xx Libraryを選択

Dim objDB As New ADODB.Connection
Dim myTBL As New ADODB.Recordset

'Accessデータベースに接続する
'Access2007/2010は「ACE.OLEDB.12.0」
'Access2000/2002/2003は「Jet.OLEDB.4.0」

objDB.Open _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\junior\Desktop\ACTESTフォルダ\ACTEST.accdb"

'データベースのテーブルを開く
myTBL.Open "T_TESTTABLE", objDB

'テーブルをワークシートにコピーする
Range("A3").CopyFromRecordset Data:=myTBL

'データベースを切断する
objDB.Close
'オブジェクトを解放する
Set objDB = Nothing

End Sub

投稿日時 - 2011-10-15 03:23:08

QNo.7072249

すぐに回答ほしいです

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

Application.Filedialog(msofileDialogPicker)ではデータベースの選択はできても、テーブルの選択は出来ません。
テーブルを選択するニーズは経験がありませんが、試しにやってみました。
UserFormにListBox一個と、CommandButton一個を置きます。
ADOに参照設定しないコードになっているので、ご質問のコードとは若干異なります。ご参考まで。
なお、エラー処理はしていないので、あしからず。
☆標準モジュール
Sub test()
UserForm1.Show
End Sub

☆UserForm1モジュール
Dim cnn As Object 'ADODB.Connection

Private Sub CommandButton1_Click()
Dim rst As Object 'ADODB.Recordset
Dim myTBLname As String

myTBLname = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Set rst = CreateObject("ADODB.Recordset")
rst.Open myTBLname, cnn
Range("A1").CopyFromRecordset Data:=rst
rst.Close
Set rst = Nothing
Unload Me
End Sub

'http://www.nurs.or.jp/~ppoy/access/excel/xlM068.html#ADO
Private Sub UserForm_Initialize()
'ADO OpenSchema メソッドを使用
Dim rst As Object 'ADODB.Recordset
Dim stProvider As String
Dim stPath As String
Dim i As Long
Dim myList() As String

Const adSchemaTables = 20

'Access2007,Access2010の場合
stProvider = "Microsoft.ACE.OLEDB.12.0"
stPath = getFilePath
If Dir(stPath) = "" Then Exit Sub
Set cnn = CreateObject("ADODB.connection")
cnn.Provider = stProvider
cnn.Open stPath
Set rst = cnn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "TABLE"))
i = 0
While Not rst.EOF
ReDim Preserve myList(0 To i)
myList(i) = rst.Fields("TABLE_NAME").Value
i = i + 1
rst.MoveNext
Wend
Me.ListBox1.List = myList
rst.Close
Set rst = Nothing
End Sub

Private Sub UserForm_Terminate()
cnn.Close
Set cnn = Nothing
End Sub

Private Function getFilePath() As String
'2002以降
With Application.FileDialog(msoFileDialogFilePicker)
If .Show = -1 Then
getFilePath = .SelectedItems(1)
Else
getFilePath = ""
End If
End With
End Function

投稿日時 - 2011-10-15 16:44:41

お礼

mitarashi様

お礼が大変遅くなり申し訳ございません。
mitarashi様のおかげで希望通りの処理をすることができました。
丁寧なご回答をいただき大変わかりやすく、また勉強になりました。
ありがとうございました。

投稿日時 - 2011-11-01 00:34:34

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

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

回答(2)

ANo.1

どうしてもどうしてもマクロを使いたいならスルーしてください。

>コピーしたいテーブルをユーザがダイアログボックスから選択するように修正できれば

エクセル画面でデータタブのAccessデータベースを開始します。
希望のアクセスDBファイルを指定すると,テーブルを聞いてくるダイアログが開きます。
希望のテーブルを指定し,希望のコピー先を指定し,OKすると,データをシートにコピーできます。

投稿日時 - 2011-10-15 11:02:09

補足

keithinさん

ご投稿いただきありがとうございます。

EXCEL側でヘッダーをあらかじめ用意しているので、フィールド名を省いてコピーしたい事と
テーブルスタイルやフィルターの設定ないようにしたいと思っています。
コピー後にそれらを削除すれば済むのですが、色々な人が常に使うようになるのでそれは
避けたいかなと思っています。

こちらの説明不足を認識できました、ありがとうございます。

投稿日時 - 2011-10-15 13:00:55

あなたにオススメの質問