excel - 刷新 Excel 中的所有 Access 数据连接取消保护和保护工作簿错误

Question

我有一些代码将参数从 excel 中的单元格提取到连接到访问数据库的数据连接中。它工作得很好，但刷新代码不起作用，除非整个工作表已经不受保护并且我不会尝试再次保护它。我也有它，当使用如下代码打开excel文件时使用代码保护工作簿（对于每个工作表）：

    Sheets("Name of WorkSheet").Protect Password:="password", Userinterfaceonly:=True

但是，对于刷新数据连接，即使 userinterfaceonly = true 也不会刷新。在这种情况下，我在运行代码之前取消保护每个工作表，然后在最后保护它们。但是，即使我在保护代码行之前有刷新代码，它也不会在保护所有工作表之前刷新 excel 文件中的数据连接（它会抛出一个错误，说它无法更新受保护的工作表？现在如果我不会尝试在最后保护工作表并保持不受保护的它会更新得很好。但是，我需要它来保护工作表。这是我的代码（我也尝试将 refreshall 命令放在消息之前框也是，但它仍然会引发一些工作表受到保护且无法刷新的错误）：

Sub RefreshQuery()

Sheets("Crosswalks").Unprotect "password"
Sheets("New Initiative Plan Form").Unprotect "password"
Sheets("Resubmit Round 1 to 2 Form").Unprotect "password"
Sheets("Total Plan Summary").Unprotect "password"
Sheets("Detail of Rollover Plan").Unprotect "password"
Sheets("Additional Info").Unprotect "password"
Sheets("Access Data").Unprotect "password"



On Error GoTo ErrMsg


valueToFilter = "Plan_Items_Qry_UniqueKey.BuyerName ="

With 
 ActiveWorkbook.Connections("For_Updating_Round1_to_Round2").OLEDBConnection
 queryPreText = .CommandText
 paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
 queryPreText = Left(queryPreText, paramPosition)
 queryPostText = .CommandText
 queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
 queryPostText = Right(queryPostText, Len(queryPostText) - 
 InStr(queryPostText, ")") + 1)
 .CommandText = queryPreText & " '" & Range("C1").Value & "'" & 
 queryPostText
  End With

 Dim queryPreText2 As String
 Dim queryPostText2 As String
 Dim valueToFilter2 As String
 Dim paramPosition2 As Integer

 valueToFilter2 = "Plan_Items_Qry_UniqueKey.PlanYear ="

 With 
 ActiveWorkbook.Connections("For_Updating_Round1_to_Round2").OLEDBConnection
 queryPreText2 = .CommandText
 paramPosition2 = InStr(queryPreText2, valueToFilter2) + Len(valueToFilter2) 
 - 1
 queryPreText2 = Left(queryPreText2, paramPosition2)
 queryPostText2 = .CommandText
 queryPostText2 = Right(queryPostText2, Len(queryPostText2) - 
 paramPosition2)
 queryPostText2 = Right(queryPostText2, Len(queryPostText2) - 
 InStr(queryPostText2, ")") + 1)
 .CommandText = queryPreText2 & Range("C2").Value & queryPostText2
 End With

 Dim queryPreText3 As String
 Dim queryPostText3 As String
 Dim valueToFilter3 As String
 Dim paramPosition3 As Integer

 valueToFilter3 = "Plan_Items_Qry_UniqueKey.Round ="

 With 
 ActiveWorkbook.Connections("For_Updating_Round1_to_Round2").OLEDBConnection
 queryPreText3 = .CommandText
 paramPosition3 = InStr(queryPreText3, valueToFilter3) + Len(valueToFilter3) 
 - 1
 queryPreText3 = Left(queryPreText3, paramPosition3)
 queryPostText3 = .CommandText
 queryPostText3 = Right(queryPostText3, Len(queryPostText3) - 
 paramPosition3)
 queryPostText3 = Right(queryPostText3, Len(queryPostText3) - 
 InStr(queryPostText3, ")") + 1)
 .CommandText = queryPreText3 & Range("C3").Value & queryPostText3

 End With




 MsgBox ("The Table below has been updated! Please Update any information 
 needed on these initiatives (DO NOT ADD NEW INITIATIVES HERE) and then 
 click Step 2 to resubmit them for Round 2 (Final Round).  If you need to 
 also add additional initiatives for Round 2 Please use the New Initiative 
 Plan Form tab to submit them."), , "Plan Resubmission of Round 1 
 Initiatives to Round 2"

 ThisWorkbook.RefreshAll

 Sheets("Crosswalks").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Access Data").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Resubmit Round 1 to 2 Form").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("New Initiative Plan Form").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Total Plan Summary").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Detail of Rollover Plan").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Additional Info").Protect Password:="password", 
 Userinterfaceonly:=True



 Exit Sub

 ErrMsg:
 MsgBox ("There was an Error Refreshing the data, please contact Finance"), 
 , "Refresh Error"

 Sheets("Crosswalks").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Access Data").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Resubmit Round 1 to 2 Form").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("New Initiative Plan Form").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Total Plan Summary").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Detail of Rollover Plan").Protect Password:="password", 
 Userinterfaceonly:=True
 Sheets("Additional Info").Protect Password:="password", 
 Userinterfaceonly:=True

 Exit Sub

 End Sub

标签： excelms-accessvba