升级至Office 365后出现“数据源无法共享”问题求助

Hey there, let's break down why your VBA script is popping up that annoying "Select Data Source" window after upgrading to Office 365, and how to fix it.

First, let's recap the core issue: your code worked fine in Excel 2011, but now when it tries to run the QueryTables.Add method to pull data from your database (looks like SQL Server, not Access, based on the dbo prefix in your SQL), it's asking you to manually select the source instead of connecting automatically.

Here are the most likely fixes, ordered by how easy they are to test:

1. Verify Your ODBC DSN Compatibility #

Office 365 comes in both 32-bit and 64-bit flavors, and this is a super common source of connection issues when upgrading:

• First, check your Excel architecture: Go to File > Account > About Excel and note if it's 32-bit or 64-bit.
• Open the matching ODBC Data Source Manager:
  • For 32-bit Excel: Run C:\Windows\SysWOW64\odbcad32.exe
  • For 64-bit Excel: Run C:\Windows\System32\odbcad32.exe
• Look for your MyDept DSN under User DSN or System DSN. Double-click it and test the connection to make sure it works. If the DSN doesn't exist, recreate it with the correct server/database credentials for your Office 365 architecture.

2. Clean Up Your Connection String #

Your current connection string has an outdated parameter and is split into arrays, which can cause subtle parsing issues:

• The APP=Microsoft Office 2003 flag is way out of date—replace it with APP=Microsoft Office 365 or remove it entirely.
• Rewrite the connection string as a single, complete string instead of splitting it into arrays to avoid accidental typos. For example:

  Connection:= "ODBC;DSN=MyDept;Description=GT Database;UID=Administrator;APP=Microsoft Office 365;WSID=XXXX111;DATABASE=GTDB;Trusted_Connection=Yes"
  

3. Simplify Your CommandText #

Splitting your SQL query into multiple arrays can lead to missing spaces or extra line breaks that confuse the query engine. Rewrite it as a single concatenated string for clarity:

.CommandText = "SELECT DataSheetSummary.a, DataSheetSummary.b, DataSheetSummary.c, DataSheetSummary.d, " & _
               "DataSheetSummary.e, DataSheetSummary.f, DataSheetSummary.g, DataSheetSummary.h, " & _
               "DataSheetSummary.j, DataSheetSummary.k, DataSheetSummary.l, DataSheetSummary.m, " & _
               "DataSheetSummary.n, DataSheetSummary.p, DataSheetSummary.q, DataSheetSummary.r, " & _
               "DataSheetSummary.s, DataSheetSummary.t, DataSheetSummary.u, DataSheetSummary.v, DataSheetSummary.w " & _
               "FROM GT.dbo.DataSheetSummary DataSheetSummary " & _
               "WHERE (DataSheetSummary.DataSheet_Number Like '" & number & "')"

This eliminates any risk of broken line breaks or missing spaces between query parts.

4. Check Authentication Settings #

Your connection string uses both UID=Administrator and Trusted_Connection=Yes, which can conflict:

• If you're using Windows Authentication (Trusted Connection), you can remove the UID parameter entirely—Windows will handle the login.
• If you're using SQL Server Authentication, make sure your connection string includes the PWD=YourPassword parameter and set .SavePassword = True (though note that storing passwords in VBA is not the most secure practice).

Bonus: Consider Upgrading to Modern Data Access Methods #

While QueryTables still works in Office 365, Microsoft recommends using ListObjects (with ListObject.QueryTable) or Power Query (via VBA) for more reliable and flexible data connections. If you have time to refactor, this could prevent similar issues in future Office updates.

Give these steps a try—start with verifying the DSN, since that's the most common culprit after architecture changes. Let me know if any of these fix the prompt!

内容的提问来源于stack exchange，提问作者Petrichor