VBAを共有ネットワークで複数人で開いて実行したい。

得意先・商品などのデータリストからの、検索プログラムをExcelのVBAで作りました。
これを社内(共有)ネットワークに置いて、誰でも検索できるようにしたいのですが、
一人が開いていると以降の人は読み取り専用となってしまい、検索実行が出来なくなります。

ブックの共有ではどうしてもVBAに制限がかかり、エラーとなってしまうため、
読み取り専用に設定しておいてから、検索時に解除→VBA実行→再び設定をすれば、
と思い試行錯誤しましたが、どうしてもエラーが出てしまいます。

該当のソースコード

Sub Search()

Dim adoCON          As New ADODB.Connection
Dim adoRS           As New ADODB.Recordset
Dim strSQL          As String
Dim odbdDB          As Variant
Dim strConnector    As String
 
'データベースのパスを取得（ExcelブックをDBとする）
odbdDB = ActiveWorkbook.Path & "\データ.xlsm"
 
'データベースに接続する
Set adoCON = New ADODB.Connection

With adoCON
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties") = "Excel 12.0"
    .Open odbdDB
End With

'カーソルをクライアント側に設定
adoRS.CursorLocation = adUseClient

strConnector = ""
 
'シート「検索データ」をテーブルとしてSQLを発行
strSQL = "SELECT * FROM [検索データ$] "

＊＊検索条件を指定して別シートに出力するVBAが入ります＊＊

'レコードセットを開く
adoRS.Open strSQL, adoCON, adOpenDynamic

'検索結果シートを一旦クリア
Worksheets("検索結果").Rows("2:" & Rows.Count).ClearContents
    
'検索結果をシートに貼り付ける
Worksheets("検索結果").Range("A2").CopyFromRecordset adoRS
Worksheets("検索結果").Select  
'クローズ処理
adoRS.Close
Set adoRS = Nothing
adoCON.Close
Set adoCON = Nothing

End Sub

ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite
などを入れてみてはいるのですが、
Open odbdDB　の所でエラーが出てしまうようです。

これ以外でも複数人からのアクセス＆検索が出来るようにするには
どうしたらいいでしょうか。

yoorwm

2020/08/11 08:14

ExcelかAccessかと思うのですが、そこの辺りを追記しておいてください。（どのみち、しようとしている事は無理かと思いますが。）

TSb

2020/08/11 23:04

すみません、Excelとなっております。やはり難しいのですね…ありがとうございます。

tosi

2020/08/12 05:21 編集

Excelは基本スタンドアロンです。同じExcelファイルを複数人が使うと、最初の人は更新権限を持ちますが、後の人はReadOnlyとなり「Open odbdDB」でエラーがでるのでしょう。 Excel(VBA)ファイルを各自複写してから、動かせば動作的にはOKな状態でしょうか。もしも、この形で動く様でしたら「Excelファイル複写→複写先ファイル起動→終了時複写先ファイル削除」プログラムを作成して、こちらを皆さんから使って貰う手があります。プログラムはVB・DOS他何でも構いません。但し、ファイル削除部分はプログラム的に難しいので、ファイル名にルールを設けて、別プログラムとしてバッチで定期削除を実行しても良いかと思います。

TSb

2020/08/12 06:04

各自複製して、自分のパソコン（CやDドライブ)に置いておく場合は、問題なく使用できますので、おっしゃる通りだと思います。（但し、同じ（共有）ファイルに複製してしまうと、ファイル名が変わるのでエラーとなります。）やはり別のプログラムを作らないとなのですね… 検索データは月に一回、更新しているのですが「自分のパソコンにコピペ(ショートカットではない)して置いておけば、勝手にリンクして更新してくれるんでしょ？」と言われるくらいパソコンに疎い人ばかりなので、できれば小難しくしたくはなかったのです。最終手段として、この意味が分かっている人だけに使用してもらう…という事も視野に入れます。また私もプログラミングは始めたばかりで、難しい事はこれからなので、根気よく頑張ってみます。このような難しい質問にご回答くださり、本当にありがとうございます。

tosi

2020/08/14 08:23

>同じ（共有）ファイルに複製してしまうと、ファイル名が変わるのでエラーとなります･･･この部分はVBAのコーディング方法で解決できると思います。エラー部分は、odbdDB = ActiveWorkbook.Path & "\データ.xlsm" でしょうか？データ.xlsmはこのVBAがあるExcelファイル自分自身を指しているのでしょうか？そうであれば、 odbdDB = ActiveWorkbook.FullName と言う記述方法もあります。

TSb

2020/08/17 02:21

素敵なアドバイスありがとうございます。 Excelファイル自身を指していましたので、 odbdDB = ActiveWorkbook.FullName　に変えてみると、コピーして（名前が変わっても）問題なく使用できました。こんな方法があるとは！コピーが簡単になったので、人数分コピーし（たら番号が自動で振られるので）、一人一人に予め指定した番号のファイルを使用してもらう、というのがこちらで管理も出来ますし、一番いいかもしれません。大変助かりました、ありがとうございます！

行動規範の内容に同意します

回答1件

ベストアンサー

要件次第ですが、共有フォルダーに配置しているマクロ付きExcelファイル(xlsm形式?)自体への
更新が不要なのであれば、xltm形式（マクロ有効テンプレート）で保存しておけば開いた時点で
新しいBookになるので解決しませんか？
ActiveWorkbookは未保存になるためActiveWorkbook.Pathなど修正する必要はありますが。。。

投稿2020/08/11 14:36

編集2020/08/11 14:41

Y.H.

総合スコア7914

TSb

2020/08/11 23:17

迅速なご回答、誠にありがとうございます。 xltm形式（マクロ有効テンプレート）で保存し、ActiveWorkbook.Pathもxltmにして、そちらを起動しましたが、「データベースまたはオブジェクトは読み取り専用なので更新できません」と出て、同じくOpen odbdDBでエラーが出てしまいました。やはり実装は難しそうですね…でも勉強になりました。ありがとうございます。

Y.H.

2020/08/11 23:37

明示的にReadOnlyを指定するとどうでしょうか？ adoCON.Mode = 1 ' (adModeRead ) した後にadoCON.Open

TSb

2020/08/12 02:06

ありがとうございます。 With adoCONの前に adoCON.Mode = 1 ' (adModeRead ) adoCON.Open を設定してみましたが、今度はadoCON.Openで「データソース名および指定された既定のドライバーが見つかりません」とでてエラーとなりました… まだ勉強を始めたばかりで、頭から煙が出そうです…

行動規範の内容に同意します