AccessのVBAでExcelを参照設定なしで使う方法

独学でaccessを勉強してにてvbaを使いデータベースを作成しています。

帳票出力でエクセルを使うものが有りますが、エクセルのバージョンが違うと参照設定を変えなくてはなりません。

Access VBA独学の悲しいところ

office2007からAccess vbaでエクセルに出力するデータベースを作り始めました。

やがてoffice2010が社内でも導入される様になり、access2007で作ったデータベースがaccess2010でエラーなる。

最初は、なぜエラーになるのかも分からなかった、なんせ独学でグーグルが頼りなもので、参照設定を変える必要がある事はしばらく分からなかった。

しばらくしてofficeのバージョンに合わせて参照設定を変える必要があることを知り、結局office2010を購入しoffice2010の所にはaccess2010で作成して対応した。

officeのバージョンが変わる毎に新しいofficeを購入して対応してきて、それがoffice2016まで続いた。

office2019が出る前ぐらいに、やっとバージョンが変わっても同じaccess で対応出来ないかと気づきました。

気づくのがお～そ～い！

それでググったらエクセルの参照設定しないで使う方法がちゃんと有るではないか。

その方法をご紹介します。（ただし理屈はあまり分かってやってないのでご勘弁を）

方法は2種類あって

1：エクセルの参照設定をしない

2：GUIDを指定しバージョンに合わせて参照設定を変更

私が最初にググった時に1の方法しか見つかりませんでした。

ですからこの参照設定しない方法で、今まで作ってきたAccessデータベースをすべて改修しました。

2の方法は最近知りましたので今後の勉強課題としとりあえず1の方法の説明をします。

通常の参照設定

エクセルを使う場合vbaの編集画面で参照設定をしなくてはいけません。

違うバージョンのエクセルが搭載されたパソコンでAccessを立ち上げると設定した参照設定がそのパソコンに無い為エラーになってしまいます。

この為、使用するバージョンの参照設定に変える必要が有りますが、使用する人がここを変更すれば良いのですが、この事がわかる人は少ないのですぐに製作者のお呼びが掛るわけです。

エクセルバージョンの違いによる参照設定

エクセル2010 Microsoft Excel 14.0 Object Library

エクセル2013 Microsoft Excel 15.0 Object Library

エクセル2016/2019 Microsoft Excel 16.0 Object Library

参照設定を使わない方

違いは朱記部です。

参照設定を使うときのvbaでの宣言

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

Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Open(“C:\****.xlsx”)–開くファイルを指定する場合
Set xlBook = xlApp.Workbooks.Add—-新規にファイルを開く場合

Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True

参照設定を使用しない時のvbaでの宣言

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True

VBA構文

参照設定を使わないときは構文を変える必要が有ります。

例　セルＢ2を中央揃えにする時。

セルの書式設定値を定数名でなく値で指定する必要が有ります。

参照設定した場合

xlBook.Worksheets(1).Range(“B2”).HorizontalAlignment = xlCenter

参照設定しない場合

xlBook.Worksheets(1).Range(“B2”).HorizontalAlignment = -4108

この値を使用する方法もしばらく分からなく定数名で指定していたので最初はエラーが出て悩んだ事が有ります。

定数は主要なものだけで全てでは有りません。

ダウンロードファイルで開く時にメッセージが出た場合

↓pdf

xl定数access

1 ファイル 833 KB

ダウンロード

エクセルを扱うVBAを書く時

私はエクセルを使う時にやりたい処理をエクセルマクロに記録しマクロの編集で構文を拾ってAccessVBAに張り付けています。

そのままだでも良いですが余分な記述が多く有りますので必要部分だけを取り出して使用しています。

セルB2を中央揃えにするエクセルマクロ

Range(“B2”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

こんなに長いですが必要部分だけだと

Range(“B2”).HorizontalAlignment = xlCenter　と短くできます。