openrowsetで既存Excelファイルへexport

vb.netでSQLserverテーブルをクエリして既存excelファイルに出力

accessのVBAからなら
docmd.transferspreadsheet acexport,acspreadsheettypeexcel9,”クエリ”,”ファイルパス”
でしたがvb.netでは難しいです

SQLserverにはopenrowsetというクエリでexportできるようなのでvb.netからExecutenonqueryで操作

SQLserverで準備
SQLserverは64bitでaccess32bitがすでにインストされているのでACEの32bitが既存
SQLserverのリンクサーバに64bitのACEのプロバイダ登録が必要なので
https://knowledge.autodesk.com/ja/support/autocad/learn-explore/caas/sfdcarticles/sfdcarticles/JPN/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.htmlを参照にACEの64bit版をちょっと強引にインスト

SQLserverのリンクサーバーにACE登録

SQLserverでmasterデータベースで以下クエリ発行
exec sp_configure ‘show advanced options’,1;
reconfigure;
exec sp_configure ‘ad hoc distributed queries’,1;
reconfigure;
exec sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ ,N’AllowInProcess’,1;
reconfigure;
exec sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ ,N’DynamicParameters’,1;
reconfigure;

exportするクエリは
insert into openrowset(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;DATABASE=ファイルパス;imex=0;hdr=1;’,
‘SELECT カラム FROM [excelのワークシート名$]’)
select カラム from sqlserverテーブル where 条件カラム=パラメータ

接続文字がとっても厳格、ここまで試行錯誤数日
vbaでは列名含めて新しいワークシートに出力できたが
openrowsetはワークシートと列名は作成しておく

クエリが問題なければvb.netからExecutenonqueryで出力

最初VB.NETのコードでゴリゴリとやっていたけど難しくて断念
openrowsetもかなり難しいし環境が違えば同様にはいかないだろうし後日再検討しよう

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です