人に聞けない痔の悩み、これでスッキリ >>

質問します。
Access初心者、VBA初中級者です。
VBAで、ExcelからAccessにデータを取り込んでいます。

ですが、Excelの表はAccessのDBの型と一致していません。取り込む場合、特定の列のデータをAccessの特定のフィールドに取り込むことはできますか?
(たとえば、ExcelのA列のデータは、Accessの「品番」フィールドに入れる、B列のデータはAccessの「商品名」フィールドに入れる、というように。)
一時エリアに保存して、それを取り込む方法でもいいです。



現在TransferSpreadsheetを使っていますが、Rangeを指定して一度に全部もってきたいわけではないので、できれば取り込み先を設定したいと思った次第です。

また、Excelは使用行が決まっていないため、取り込みも、できれば
「カラ行になるまで1行ずつ取り込む」というようなコードにしたいです。

お手数をおかけしますが宜しくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (3件)

#1です



> rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

ここの部分は、ただ単にテーブルをオープンしたでけになります。
その後、rs.MoveNext 等で、処理対象のレコードに移動したりします。

で、
>   rs.AddNew

は、新規レコードを追加しますよ、っていうものになります。

>  rs("品番") = oApp.Cells(iRow, 1)
>  rs("商品名") = oApp.Cells(iRow, 2)
 
でフィールドに値を代入(設定)し、

> rs.Update

で、登録しています。

処理の流れで、rs.AddNew しないでフィールドに値を代入し rs.Update すると
これは、更新、になります。(ADOの場合)

DAOの場合は、rs.Edit を記述し、これから更新しますよ、って指定します。

更新されるものは、現在参照できているレコードになります。


レコードセットを操作して、更新/削除/新規登録する場合は、
テーブルをオープンする際に、INSERT 等は指定する必要がありません。

SQLを発行して完結するもの(INSERT,DELETE,UPDATEなど)は、
一般的にアクションクエリとか呼ばれています。(クエリでの話ですが)

これらを実行する時には、レコードセットをオープンする操作ではなく
Execute するもので実行します。

例えば "DELETE * FROM テーブルA" を実行する時には、ADOでは
CurrentProject.Connection.Execute "DELETE * FROM テーブルA"
とかにします。


話がずれていっているような気がしてきましたが、わかる内容になって?ました?、、、
    • good
    • 0
この回答へのお礼

>30246kikuさま
おそくなりました!ありがとうございます!
まさにそのようなことが知りたかったのです。
本当に勉強になりました。ありがとうございます。

最後に一つお聞きしてもよいでしょうか(><)
色々、修正中に途中でバグったりすると、ExcelがOpenになったままになるらしく、
対象のExcelが編集中になったり、Excelが(手動で)開けなくなったりしてしまいます。
こういう場合はどうしたらよいのでしょうか。

Openしたら変数かなにかにExcelの内容を一括コピーして元のファイルはさっさとCloseする、とかできるのでしょうか。

甘えてしまってすみません。
どうぞよろしくお願いします。(><)

お礼日時:2009/11/04 15:04

#2です



> こういう場合はどうしたらよいのでしょうか。

もっといい方法があると思いますが、私はタスクマネージャからExcelを終了させてます。


VBAでやろうとすると、起動しているExcelは以下の方法で得ることができます。

  Dim oApp As Object

  On Error Resume Next
  Set oApp = GetObject(, "Excel.Application")
  If (Not oApp Is Nothing) Then
    ' ここで、ワークブックをループするなりして閉じるとか
    '
  End If


ただ、Excel起動中に

  Set oApp = CreateObject("Excel.Application")

でExcel起動すると、Excelが複数立ち上がることになります。
(タスクマネージャで見るとわかると思います)

なので、
  Set oApp = GetObject(, "Excel.Application")

で処理するのもループさせ、Excelが無くなるまで処理するのでしょうか。


※確認作業中はファイルが壊れてもいいようにコピーは取っておきます。
    • good
    • 0
この回答へのお礼

>30246kikuさま
ご回答ありがとうございました!
やはりタスクマネージャが早いですよね。。

使う人が素人なのと、タスクマネージャに対象のものが乗ってこないのが気になるのですが
とりあえずこちらに関しては、バグをださないこととOSの機能でなんとかしつつ、改善策を探そうと思います。
ありがとうございました!

お礼日時:2009/11/11 17:56

処理は遅くなると思いますが、以下雰囲気で(ADOでの例)



インポートしたいエクセルファイルが、mdb と同じフォルダに、
hogehoge.xls 名であったとします。(シート1枚)


Dim rs As New ADODB.Recordset
Dim oApp As Object
Dim iRow As Long

Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open Filename:=CurrentProject.Path & "\hogehoge.xls"

rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
iRow = 2 ' ★
While( 空白行の判別 )
  rs.AddNew
  rs("品番") = oApp.Cells(iRow, 1)
  rs("商品名") = oApp.Cells(iRow, 2)
  rs.Update
  iRow = iRow + 1
Wend
rs.Close
oApp.Quit
Set oApp = Nothing


※ エラー処理は省いてます。

※ ★では2行目から取り込みを設定

※ 空白行の判別は、正式な記述に

※ シートが複数あって選択する必要があるのなら、追加してください。

この回答への補足

たびたびすみません。
その後少しいじっていたら、出力されるようになりました!
ありがとうございます。

ちなみに、DBへの書き込みのロジック(?)が微妙にわかっていません。

re.AddNew

の部分が、書き込みますよ、という意味なのでしょうか?
ここがわからないので、先程のように「SQLでINSERTしなきゃいけないんじゃないのか?」と思ってしまいました。

補足日時:2009/10/31 17:35
    • good
    • 0
この回答へのお礼

>30246kiku様
ありがとうございます。
参考に使用してみたところ、

rs.Open "テーブル名", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

のところで、
エラー「SQLステートメントが正しくありません。'DELETE','INSERT','PROCEDURE','SELECT',または'UPDATE'を使用してください」
が出てしまいました。

おそらくINSERTをしなければいけないのだと思いますが、
どこにどのように記述すればよいのでしょうか?
お手数おかけしますがお教え下さいますか(><)

お礼日時:2009/10/31 16:09

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QAccessからExcelファイルのデータ取得

Access2000かExcelファイルのデータを取得したいのですが、
Excelファイルを一度 CSVファイルに変換し、その後Accessのテーブルへデータ取り込み といった手順をとりたいのです。
この方法って、どうコーディングすればいいのですか?
(Accessのインポート処理は使用しない方法です)

Aベストアンサー

VBAの基本的なことはご存知ですか?
でしたら、
1.Accessで対象のExcelファイルを選択
2.AccessからExcelを制御してCSVにコンバート
3.出来たCSVファイルをAccessに取り込む(テキスト取り込み インポート定義を使うと簡単)
この順序で行えばOKです。
ExcelでVBAを書くことで制御する方法もありますが、
Access側からExcelを制御する方がやりやすいと思います。

QAccessでボタンを押したらエクセルデータを読み込み処理をしたい

よろしくお願いします。
AccessからExcelを開けるようにしたいのですが、
コードの記述方法がいまいちわかりません。
教えて頂けますでしょうか。

最終的には、ボタンをクリックしたら、ExcelファイルにあるデータをAccessに取り込み、それでDM用のラベルを作りたいと考えています。
勉強が必要なので、少しずつやろうと考え、
開く為のコードからいろいろいじってみようと考えました。
もし、他に効率の良い学習方法があればそちらもお願い致します。

Aベストアンサー

お世話になります。

>コードの記述方法がいまいちわかりません。
と言う事でしたので,最も簡単な方法を。

(1)Accessのマクロを作ります.
 [アクション]で[ワークシート変換]を選択し,
 画面の下部にある,[アクションの引数]に
 適宜入力します。
 
 作成したマクロを適当な名前をつけて保存します。

(2)Accessメニューバーの
 [ツール]→[マクロ]→[マクロを VisualBasicに変換]を選択し,変換します。

変換されたモジュールを見てみれば,
どんな感じでAccessテーブルにインポートするか
解ると思います。

QADOを使用してExcelデータをAccess取込む際のExcelシートの選択について

表記のとおりADOを使用してExcelデータをAccess取込むのですが、Sheet1のデータを読込んだ後、引続きSheet2のデータを読込む様にVBAコードを書いたつもりですが、Sheet1をアクティブにした状態でExcelデータを保存していたらSheet1のデータを重複して取込み、Sheet2をアクティブにした状態でExcelデータを保存していたらSheet2のデータを重複して取込んでしまいます。どこをどうすればSheet1のデータを読込んだ後、引続きSheet2のデータを読込む様に出来るのでしょうか?

====== VBAコードの抜粋 =========
  Dim xlApp As Object       ' Excelのアプリケーション定義
  Dim xlBook As Object      ' ExcelのワークブックのフォルダPath+ファイル名を定義
  Dim xlSheet As Object      ' Excelの参照するシート名を定義
  Dim SheetName As String     ' シート名を代入
  Dim SheetCount As Byte     ' シートの選択

For SheetCount = 1 To 2       ' 1回目のループでSheet1から2回目のループでSheet2からデータを取り込む
If SheetCount = 1 Then SheetName = "Sheet1"
If SheetCount = 2 Then SheetName = "Sheet2"

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("ファイルPath+ファイル名")
Set xlSheet = xlBook.Worksheets(SheetName)
Set Wcell = xlSheet.Range("A1")
Set Cn = CurrentProject.Connection
Rs.Open "取込みテーブル", Cn, adOpenKeyset, adLockOptimistic
xlApp.Application.Visible = True

  データを取込むコードあり(省略)

xlBook.Close
xlApp.Visible = False
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Next SheetCount

表記のとおりADOを使用してExcelデータをAccess取込むのですが、Sheet1のデータを読込んだ後、引続きSheet2のデータを読込む様にVBAコードを書いたつもりですが、Sheet1をアクティブにした状態でExcelデータを保存していたらSheet1のデータを重複して取込み、Sheet2をアクティブにした状態でExcelデータを保存していたらSheet2のデータを重複して取込んでしまいます。どこをどうすればSheet1のデータを読込んだ後、引続きSheet2のデータを読込む様に出来るのでしょうか?

====== VBAコードの抜粋 =========
 ...続きを読む

Aベストアンサー

>ADOを使用してExcelデータをAccess取込む際のExcelシートの選択について

ソースを拝見しましたが、「取り込み」の部分はADOは使ってませんね。
(取り込んだデータを、テーブルに保存する所がADOです。)

「ADOで取り込む」が目的なら、No1さんの回答になると思います。

-----------------------------------------------------------
このままの方針で、話を進める場合・・・

質問のソースは、問題ありません。(動作確認しました)
省略した、「データを取込むコード」に問題があると思います。

この部分を、補足して下さい。

推測ですが、xlBook.ActiveSheet を使ってないですか?
ちゃんと、Wcell を使ってデータを参照してますか?

-----------------------------------------------------------
また、間違いではないですが、高速化の為に
修正した方が良いと思われる点があります。
下記のソースのコメントをご覧下さい。

以下、私が動作確認したソース(抜粋)


 'ブックを開く部分は共通なのでループの外へ
 Set xlApp = CreateObject("Excel.Application")
 Set xlBook = xlApp.Workbooks.Open("c:\temp\001.xls")

 For SheetCount = 1 To 2       ' 1回目のループでSheet1から2回目のループでSheet2からデータを取り込む
  If SheetCount = 1 Then SheetName = "Sheet1"
  If SheetCount = 2 Then SheetName = "Sheet2"
 
  Set xlSheet = xlBook.Worksheets(SheetName)
  Set Wcell = xlSheet.Range("A1")
  
  '確認のため、A1 の内容を表示してみる
  MsgBox Wcell.cells(1,1)
 
 Next SheetCount

 'ブックを閉じる処理もループの外へ
 xlBook.Close

>ADOを使用してExcelデータをAccess取込む際のExcelシートの選択について

ソースを拝見しましたが、「取り込み」の部分はADOは使ってませんね。
(取り込んだデータを、テーブルに保存する所がADOです。)

「ADOで取り込む」が目的なら、No1さんの回答になると思います。

-----------------------------------------------------------
このままの方針で、話を進める場合・・・

質問のソースは、問題ありません。(動作確認しました)
省略した、「データを取込むコード」に問題があると思います...続きを読む

Qインポート時のエラー「データ型の変換エラー」

ACCESS2000を使用しています。
CSVファイルをマクロを使い、インポートしようとしているのですが、ある列に「33300G2」というデータがあります。
インポートのマクロを実行するとそのデータのみがインポートエラー(エラー内容は「データ型の変換エラー」)となってしまいます。
ちなみに、エラーが出る列は、「33300G2」以外全てが数字のみなのですが関係あるのでしょうか。
もし原因らしきものがわかればアドバイスよろしくお願いいたします。

Aベストアンサー

インポート定義をして下さい。
インポート→設定→データ型を変更して下さい。
何かあれば補足して下さい。
がんばれ

QAccess-VBAでExcelファイルを作成する。

こんにちわお世話になります。

「Excelにエクスポート」ボタンをクリックすると、Inputboxか何かが表示されて、Pathやファイル名やシート名を入力し、「実行」ボタンを押すと、新規にExcelのBookを、そのPath、ファイル名、シート名で作成する。その後、そのシートにデータを書き込むという作業をしたいのです。
AccessのデータをExcelの任意のシートを開いて書き込むという部分のVBAコードはわかりますので、新規Book作成部分のコードがわかるかたお願いします。
Office97を使用しています。

Aベストアンサー

プロジェクト→参照設定でMicrosoft Excel *.* ObjectLibraryにチェックを入
れて下さい。

オブジェクトへの参照を格納する変数を事前バインディング。
変数にオブジェクトの参照を代入します。Addメソッドを使って新規workbookと
worksheet オブジェクトを作成します。
値を代入し名前を付けて保存してオブジェクトを開放する。という流れで良いと
思います。

Private Sub Command1_Click()

  On Error Resume Next

  Dim xlApp  As Excel.Application
  Dim xlBook As Excel.Workbook
  Dim xlSheet As Excel.Worksheet

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Add
  Set xlSheet = xlBook.Worksheets(1)

  ' Excel のセルに値を代入します。
  xlSheet.Cells(1, 1).Value = " "

  ' Worksheet を名前をつけて保存します。ダイアグボックス等を使用して
   パスやBook名など入力できるようにしてもOKです。
  xlSheet.SaveAs "c:\Temp.xls"

  xlApp.Quit

  Set xlSheet = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing
End Sub

こんな感じでしょうか。

プロジェクト→参照設定でMicrosoft Excel *.* ObjectLibraryにチェックを入
れて下さい。

オブジェクトへの参照を格納する変数を事前バインディング。
変数にオブジェクトの参照を代入します。Addメソッドを使って新規workbookと
worksheet オブジェクトを作成します。
値を代入し名前を付けて保存してオブジェクトを開放する。という流れで良いと
思います。

Private Sub Command1_Click()

  On Error Resume Next

  Dim xlApp  As Excel.Application
  Dim xlBook As Excel.Workbook
...続きを読む

QACCESSテーブルを RecordSet以外で1レコードづつ読む方法

Set inrec3 = CurrentDb.OpenRecordset("テーブル名")
でオープンして1レコードづつ読みたいのですが
上記の方法では、データが多いため(2万件ちょっと)
フリーズしてしまいます
そこでもっと単純にOpenする方法はないでしょうか
テキストファイルのオープンだと
今度は、フィールドの取り出しがやっかいかな
と思うのですが
よろしくおねがいします

Aベストアンサー

久しぶりにDAOを使ってみたので外しているかもしれませんが

Sub aaa()

Dim db As Database
Dim inrec3 As DAO.Recordset
Dim i As Long

Set db = CurrentDb
Set inrec3 = db.OpenRecordset("TEST")

Do Until inrec3.EOF

i = i + 1
If i Mod 100 = 0 Then
Debug.Print i, inrec3!XXX
DoEvents
If i = 30000 Then Exit Sub
End If

inrec3.MoveNext

Loop

inrec3.Close
Set inrec3 = Nothing

Set db = Nothing

End Sub

試しにやってみました。
200万件のレコードで30,000回ループ
何も問題ありませんでした。

気になったことですが、
(1)Set inrec3 = CurrentDb.OpenRecordset("テーブル名")
とせずに、"Select * From テーブル名 Where Field1 > 1000 " 見たいに
SQL文でくくれないでしょうか。
全レコードを読まなければならないのならば別ですが
(2)DoEventsを入れて見たらどうでしょうか。

久しぶりにDAOを使ってみたので外しているかもしれませんが

Sub aaa()

Dim db As Database
Dim inrec3 As DAO.Recordset
Dim i As Long

Set db = CurrentDb
Set inrec3 = db.OpenRecordset("TEST")

Do Until inrec3.EOF

i = i + 1
If i Mod 100 = 0 Then
Debug.Print i, inrec3!XXX
DoEvents
If i = 30000 Then Exit Sub
End If

inrec3.MoveNext

...続きを読む

Qアクセスの外部データ(エクセル)の取り込み。VBA

アクセスの外部データ(エクセル)の取り込み。VBAの質問です。

アクセス2003 エクセル2003

不要な行・列がある場合の外部データ取り込みVBAを教えて下さい。
今は、エクセルで削除して取り込んでいます。

アクセスに「得意先リスト」というテーブルを作っています。

コード(テキスト型)主キー
名称(テキスト型)
フリガナ(テキスト型)
郵便番号(テキスト型)
住所1(テキスト型)
住所2(テキスト型)
TEL(テキスト型)
FAX(テキスト型)

エクセルブック[得意先リスト.xls]
シートは「リスト形式」のみです。

1~4行は不要。
5行目が見出しです。

A~AZ列までデータがあります。
必要な列は、B・C・D・F・G・H・M・N列です。
セルの書式設定は「文字列」です。

[アクセス エクセル インポート 行 列 削除]などでサンプルVBAを探しましたが見つからなかったので質問させて頂きました。
申し訳ありませんが、教えて下さい。

Aベストアンサー

手作業でやっていることを、そのまま置き換えるのが一番簡単そうです。
Access からオートメーションでExcelのBookを開き
Access で取り出しやすいように整形してから取り込む。
こんな感じ
※MDB ファイルと XLS ファイルは同じフォルダにあると仮定しています。

Sub test()
  Dim oXL As Object
  Dim oBK As Object
  Dim oSH As Object
  Dim tmpXls As String

  Set oXL = CreateObject("excel.application")
  Set oBK = oXL.Workbooks.Open(CurrentProject.Path & "\得意先リスト.xls")
  Set oSH = oBK.Sheets("リスト形式")
  tmpXls = CurrentProject.Path & "\得意先リストtmp.xls"

  With oSH
   .Rows("1:4").Delete Shift:=xlUp
   .Columns("O:AZ").Delete Shift:=xlToLeft
   .Columns("I:L").Delete Shift:=xlToLeft
   .Columns("E:E").Delete Shift:=xlToLeft
   .Columns("A:A").Delete Shift:=xlToLeft
  End With
  If Dir(tmpXls) <> "" Then
   Kill tmpXls
  End If
  oBK.saveas filename:=tmpXls
  oBK.Close SaveChanges:=False
  oXL.Quit
  Set oXL = Nothing

  'CurrentDb.Execute ("delete * from 得意先リスト")
  '↑既存レコード削除ですので必要に応じて!!
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  "得意先リスト", tmpXls, hasFieldNames:=True
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  "得意先リスト参考までに", tmpXls, hasFieldNames:=True

  MsgBox "終了"
End Sub

参考になるかと思われるところ(私も以前どっぷりとハマリましたorz)
http://hanatyan.sakura.ne.jp/vbhlp/ExcelErr.htm
http://homepage1.nifty.com/rucio/main/technique/teq_15.htm

手作業でやっていることを、そのまま置き換えるのが一番簡単そうです。
Access からオートメーションでExcelのBookを開き
Access で取り出しやすいように整形してから取り込む。
こんな感じ
※MDB ファイルと XLS ファイルは同じフォルダにあると仮定しています。

Sub test()
  Dim oXL As Object
  Dim oBK As Object
  Dim oSH As Object
  Dim tmpXls As String

  Set oXL = CreateObject("excel.application")
  Set oBK = oXL.Workbooks.Open(CurrentProject.Path & "\得意先リスト.xls")
  S...続きを読む

Q「Nullの使い方が不正です」のエラー

AccsessとVB間でデータの参照・登録・削除を行うプログラムを作成中です。
テキストボックスにデータを入力せずに更新するとAccess側はNull値が入るので、参照をした際に
「Nullの使い方が不正です」のエラーが表示されます。

Null値が表示可能ならその方法を教えてください。

Aベストアンサー

読み込んだ際にIsNull()で判断してみては?
If IsNull(Fields) Then
Text1.Text = ""
Else
Text1.Text = Fields
End If

QAccess サブフォームでの選択行の取得

こんにちは。

Access初心者です。

サブフォームでテーブルの項目を表示させていますが、
選択された行を取得する方法はありますか?
サボフォームの下の方に現在選択されているレコード数が表示されてますが、その値でかまいません。

調べているのですが、なかなか検討がつきません。
宜しくお願い致します。

Aベストアンサー

フォーム名がフォーム1、サブフォームコントロールの名前がサブフォーム1だとすると、

Forms!フォーム1!サブフォーム1.Form.CurrentRecord

で取得できます。
(「Forms」と「Form」がありますのでご注意下さい)


また、フォーム1にコードを記述する場合であれば

Me!サブフォーム1.Form.CurrentRecord

サブフォーム1へのコード記述であれば

Me.CurrentRecord

という構文によっても、それぞれ取得が可能です。

Q【Excel VBA】マクロでExcel自体を終了させたい

環境:WindowsXP、Excel2003

マクロでエクセルを終了(ブックを閉じて、アプリケーション自体も終了)させたいのですが、以下のコードではアプリケーションが閉じてくれません。

ThisWorkbook.Close
ExcObj.Quit
Application.Quit

どこか悪いところはありますでしょうか?

よろしくお願いします。

Aベストアンサー

普通に考えれば質問者のコードで上手くいきそうですが
hana-hana3さんの回答にもあるようにThisWorkBook.Closeでコード終了となりますので
Application.QuitをThisWorkBook.Closeの前にもってこないといけません。
Application.Quitはそれがあるプロシージャのコードが全て終わるまで
その実行を保留するちょと特別動作をします。

'-------------------------------------
 Application.Quit
 ThisWorkbook.Close
'-------------------------------------
 
 


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング