質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.35%

  • VBA

    1902questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1629questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

ExcelVBA 「フィールド指定せずに行で」Accessのテーブルへ書き込みしたい

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 322

morikawa0208

score 18

 前提・実現したいこと

Excel 1ファイルごとに1レコード(管理している項目は同じ内容・位置なので、必要なセルを1行にまとめて疑似的にレコードっぽくしてます)
この1行だけで良いので、Accessのテーブルに書き込みというか貼付けしたい
イメージしているのはExcelでコピーして、Accessのテーブルで貼り付けする動作

Excelのデータイメージ
Sheet1・・・物件名、号室、契約者名、仲介手数料などを計算・記録 印刷する形でエクセル方眼紙のように作られてます
その中から保管しておきたいデータをシートの下に「=○○」で引っ張って疑似的にレコードっぽく

※この動作は、Access側ではなくExcel側から行いたいです※
自分がやるならどんな形でもいいんですが、営業にやってもらおうと考えてるのでボタン1つで動作したい
(Access開いてAccessでボタン押させるのも、営業から問い合わせ来るだろうなって思うほどのスキル具合です)

 試したこと

ExcelVBA+AccessDBについて調べて、ADODB?でのデータベース操作はやってみました。
フィールドを指定する形であれば、読み書き更新削除は動かすことができます。
ただその後、すぐ見つかるだろうとExcelからAccessへの行コピペを調べてみたところ全然出ず、フィールドを指定するものしか出ませんでした
手動ではすぐできるし、Excelシート→ExcelDBは同じ処理をしている(それもテラテイルで教えてもらいました)ので、
簡単なのかと思ったんですが・・・

 補足情報(FW/ツールのバージョンなど)

Excel、Accessともに2016

以前質問させてもらい本当に色々と教えていただきました。
今まで難しく考えすぎてたのかなと要らないものを削ぎ落してみると、残ったのはExcelをDBとして使う不安くらいでした。
(毎月20~30、多くても100行かないくらいのレコード追加・変更、計算はExcelで行うのでDBから行うのは抽出のみ、やったとしても足し引きくらい)

追々はデータ自体の精度を高めるためにAccess等で入力フォーム作ったりとか、何か別のアプリケーションに移行したい気持ちはありますが、
自分も営業もスキルが足りていないので、最低限のデータの保管・抽出について意識付けをまずやりたいと思っています。

引き続き自分でも探してみます。何卒よろしくお願いいたします。

追記

'ExcelよりAccessDBへレコード追加・書き込み
Sub addRecords()

Dim strFileName As String
strFileName = "test2.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成


'***** データを登録 *****


Dim i As Long
Dim rng As Range


'・・・ レコードセットrsを開く処理(略)
adoRs.Open "sheet1", adoCn, adOpenKeyset


Set rng = Range("A1", "T2") '疑似レコードの範囲(1行目項目名、2行目値)

adoRs.AddNew
For i = 1 To rng.Columns.Count
    adoRs.Fields(rng.Cells(1, i).Value).Value = rng.Cells(2, i).Value
Next
adoRs.Update

'***** データベース終了処理 *****
adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

試しに作った分をちょっと加工してみましたが、Forの後の「adoRs.Fields~」の部分でエラーが出ます。
実行エラー3265 要求された名前、または序数に対応する項目がコレクションで見つかりません
そもそもレコードセットを開く処理あたりも不安ですが・・・

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2018/07/17 10:15

    項目を指定せず、行単位で更新を行いたい理由は何ですか?メンテナンスを減らすというのは考えにくいですけど

    キャンセル

  • morikawa0208

    2018/07/17 10:27

    目を通していただいて、ありがとうございます!行単位で行いたい理由は、管理項目の追加する状況が結構起こりそうだからです。直近だと仲介手数料に対しての歩合計算の方法が変わりまして、日付や担当や金額など15項目くらい一気に増えました。さらに今まで契約した分は今までの計算方法で支給するとのことで残して、新旧のフラグも追加し・・・という感じで今後も起こりそうです。行単位での管理であればExcelに項目追加とAccessテーブルにフィールド追加だけで済むかなと思いまして・・・

    キャンセル

回答 2

+1

回答を用意しているうちに解決済みになってしまいしましたが、せっかく書いたので。

ExcelVBA+AccessDBについて調べて、ADODB?でのデータベース操作はやってみました。
フィールドを指定する形であれば、読み書き更新削除は動かすことができます。
ただその後、すぐ見つかるだろうとExcelからAccessへの行コピペを調べてみたところ全然出ず、フィールドを指定するものしか出ませんでした

フィールドを指定しての書き込みはできているということですね。

AccessのテーブルとExcelの疑似レコードの列数、フィールド位置が同じなら、
ADODBで下記のようにすれば、フィールド名の指定は不要です。

Dim rs As ADODB.Recordset
Dim i As Long
Dim rng As Range

'・・・ レコードセットrsを開く処理(略)

Set rng = Range("A10";"G10") '疑似レコードの範囲

rs.AddNew
For i = 0 To rs.Fields.Count - 1
    rs.Fields(i).Value = rng.Cells(1, i+1).Value
Next
rs.Update

追記

エクセルの疑似レコードが下記のように項目名も持たせた場合の例

項目1 項目2 項目3 ・・・
値1 値2 値3 ・・・
Dim rs As ADODB.Recordset
Dim i As Long
Dim rng As Range

'・・・ レコードセットrsを開く処理(略)

Set rng = Range("A10";"G11") '疑似レコードの範囲(1行目項目名、2行目値)

rs.AddNew
For i = 1 To rng.Columns.Count
    rs.Fields(rng.Cells(1, i).Value).Value = rng.Cells(2, i).Value
Next
rs.Update

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/17 12:39

    それは考えたんですが、名称だけ意識すれば良いほうが扱う人にとって手間は少なくて済むと考えました。
    特にエクセルは見易さで項目位置を変更することもあるでしょうし。

    キャンセル

  • 2018/07/17 13:01

    私もエクセルの疑似レコードに項目名も持たせた方がいいと思います。
    質問が「フィールド指定せずに行で」ということだったのでそれに合わせしましたが。

    キャンセル

  • 2018/07/17 14:15

    hatena19様もいつもありがとうございます。サイトも参考にさせて頂いておりますm(__)m
    せっかく書いていただいたので動かしてみようと、追記の方を加工してみたんですが、
    エラーが出てしまい動きません・・・
    質問文に追記しておりますので、もしご都合よくお時間あれば見て頂けると助かります・・・

    キャンセル

  • 2018/07/17 14:58

    ちょっと色々と調べているうちに、今回質問した内容はおとなしくフィールド指定してデータの書き込みを行い、そもそものExcelを1つにしてメンテナンスを楽にする方向で動くほうが、シンプルで分かりやすいかなと思いました。せっかく回答いただいたのに、申し訳ございません・・・。ただ、やることがハッキリしたので助かりました。ありがとうございました。

    キャンセル

  • 2018/07/17 16:01

    > 実行エラー3265 要求された名前、または序数に対応する項目がコレクションで見つかりません

    エクセルの項目名(1行目)が、レコードセットのフィールド名で見つからないということだと。
    エクセルの項目名とAccessのテーブルのフィールド名は完全に一致してますか。全角/半角がちがっているとか。
    まは、フィールド名に空白やハイフンなどの記号が含まれている場合もエラーになります。

    キャンセル

checkベストアンサー

0

行単位というのはRecordsetに該当しますが、Recordsetも内部的に構造を持つ必要があります。
AccessにはDoCmd.TransferSpreadsheet メソッドがあり、エクセルのシートを指定するだけで内部的に構造解析を行ってくれますが、インポート/エクスポート用なので、更新には対応できません。

行単位で行いたい理由が、ACCESSとエクセルの項目の追加だけで済ませたいということなので、エクセル側でシートの項目タイトルを取得するように処理を組めば目的を果たすことができるかと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/17 12:09

    回答ありがとうございます!まだ頂いた回答について調べ中ですが、http://www.moug.net/tech/exvba/0030002.htmlこのあたり参考にしてみようと思います!
    このコピペのほかに重複の検索とか調整する部分もあるので、ちょっと時間かかりそうですが・・・

    上の補足情報を返信していた時に思ったんですが、フィールド名を指定してもしなくても、最初に設定しないといけないのは変わらず、そんなにこだわる部分じゃない気がしてきました。
    それより元のExcelを1つにできるよう設定して、メンテナンスしやすくするほうが費用対効果というか、成果が見えやすいのかもとも・・・こういう問題は思考がループしちゃいますね;0;

    調べたらコピペできるとのことで、また再度検討するきっかけとなりました。
    以前の質問でもそうですが、いつもありがとうございます。またよろしくお願いします!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.35%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • VBA

    1902questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1629questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。