SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
東南アジアをバックパッカーしながら転職活動をした記録
東南アジアをバックパッカーしながら転職活動をした記録
7年目で初の転職活動 2カ月で内定獲得した記録
7年目で初の転職活動 2カ月で内定獲得した記録
おすすめのプログラミングスクール めざせ就職・年収アップ!
おすすめのプログラミングスクール めざせ就職・年収アップ!
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
ブログ開始3年が経った月収が1万超えてた!
ブログ開始3年が経った月収が1万超えてた!
プログラミング独学・スクール・就職(目次)
プログラミング独学・スクール・就職(目次)
previous arrowprevious arrow
next arrownext arrow
 
Shadow
VBA

Access エクセル取込み 複数シート

Accessに複数シートをのエクセルを一括で取り込める機能が欲しかったのですが、ありませんでした。

作ってみたので残しておきます。

実際に作ったファイルはこちらからダウンロードできます。

エクセル取込み – エクセル参照設定

まずAccessファイルにエクセルを取り込むときに、エクセルを操作しなければなりません。

そのため、エクセル操作ができるよう参照設定を行います。

手順はコードエディターから「ツール(T)」→「参照設定(R)」と移動します。

その後、Microsoft Excel 16.0 Object Libraryを追加してください。

16.0はバージョンに寄ります。

エクセル取込みのコード

処理の流れは以下の順です。

①ファイルダイアログを開いて取込むエクセルを選択
②エクセルのシート名を取得
③シート毎にテーブルに取込む

※1テーブルをAccessに作っておく必要があります。
※2テーブル名とシート名は同じ必要があります。

Access VBA ダイアログからパス取得VBAでファイルダイアログを開く処理について紹介します。 実際に作ったファイルはこちらからダウンロードできます。 ダイアログ用の...
'=======================================
'目的    :エクセルデータをインポートする
'=======================================
Private Sub ImportExcel_Click()

  Dim MyDir As String
  Dim InputFilePath As String
  Dim i As Long
  Dim mySheetCnt As Long
  Dim SheetNames() As String
  Dim wb As Workbook
  
  '自分の配置されているパスを取得
  MyDir = Application.CurrentProject.Path
  
  '①----------ファイルダイアログを開いて取込むエクセルのパスを取得する----------
  '取込みファイルパスを取得
  InputFilePath = GetFilePath()
  
  'アラート表示をオフにする
  DoCmd.SetWarnings False
  
  '取得パスが空白でなければ処理開始
  If InputFilePath <> "" Then
  
    '②----------エクセルのシート名を取得----------
    'エクセルを開く
    Workbooks.Open (InputFilePath)
    Set wb = ActiveWorkbook
    
    '配列の数を再定義
    ReDim SheetNames(ActiveWorkbook.Sheets.Count)
    
    'シートを配列に取得
    For i = 1 To ActiveWorkbook.Sheets.Count
      SheetNames(i) = ActiveWorkbook.Sheets(i).Name
    Next i
    
    'エクセルを閉じる
    wb.Close False
    
    '③----------エクセルのシートを全て取り込む----------
    'シート分取込みを実行
    For i = 1 To UBound(SheetNames)
    
      'シート名がテーブル名に存在する場合、取込む 存在しない場合、スキップ
      If DCount("*", "MSysObjects", "[Name]='" & SheetNames(i) & "'") > 0 Then
        '対象テーブルを削除
        DoCmd.RunSQL "DELETE * FROM " & SheetNames(i)
        
        '各マスタテーブルへエクセルデータを取込む
        DoCmd.TransferSpreadsheet _
          acImport, _
          acSpreadsheetTypeExcel12Xml, _
          SheetNames(i), _
          InputFilePath, _
          True, _
          SheetNames(i) & "!"
       End If
    Next i

    MsgBox "取込みが完了しました。"
  Else
    MsgBox "取込をキャンセルしました。"
  End If
  
End Sub

補足

49行目のテーブルデータ削除についてです。

以下の処理で一度テーブルデータを一括削除してからテーブルに取込んでいます。

削除しないとどんどん行数が増え続けていくためです。

DoCmd.RunSQL “DELETE * FROM ” & SheetNames(i)の中身

指定したテーブル名を引数に受け取っています。

そして指定したテーブルのレコードすべてを削除するようにしています。

VBA C# オススメ参考書プログラミングの参考書でオススメの紹介をしていきます。 それぞれ私が使っている言語のみです。 VBAのオススメ参考書をExc...

エクセル取込み時に255文字で切れる

一つ問題がありました。

この処理だけでは文字数が255を超える場合、文字が切れてしまう可能性があります。

たとえ、文字型を「長いテキスト」にしたとしてもです。

長いテキストにすれば1GB分まで格納できるはずなのですが、なぜ?と思っていました。

原因は最初の8行が255文字以上でないと、自動でMAX文字数を255とAccess側で判断してしまうためだそうです。

この対応は他で行って公開できればと思います。

おすすめのプログラミングスクール めざせ就職・年収アップ! プログラミングを独学でされている人は意外と多いです。 ただ、プログラミングの独学は簡単にはいきません。 自分だけの力...
プログラミング初心者の独学方法 文系卒の経験談 私はIT系の会社に勤めて4年目ですが、最初の1年は開発をさせてもらえませんでした。 文系卒でプログラミング経験はなかったの...
プログラミングの独学とはエラーとの闘い プログラミングを勉強し始めた初心者の方はつまづくことが多いと思います。 私も最初はエラーが出るたびにいちいちビビッて、意味...

・xserver アクセスログ取得方法
https://www.xserver.ne.jp/manual/man_server_log.php
・Access2016で長いテキスト型でインポートエラー
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_access-mso_winother/access2016%E3%81%A7%E9%95%B7%E3%81%84%E3%83%86/9e9d4345-0a9a-4004-9038-1da0150cf835?page=2
・もう一度学ぶMS-Access データ型
https://www.accessdbstudy.net/entry/20141027/p1#04020
・AccessVBAでテーブルを削除する3つの方法!コピペでOK!
https://eijiman.com/access-vba-table-delete/

ABOUT ME
LooseCarrot
LooseCarrot
ブログ運営をしているLooseCarrotです。 興味のあることにトライして発信していきます! プロフィール

関連している記事