Air_Cooled_Nut
New Member
- Joined
- Oct 8, 2004
- Messages
- 36
I have the following code successfully running, however, once it gets to the bold orange section I get the following warning message for EACH query on the sheet that is being password protected:
I tried Application.DisplayAlerts = False and set calculation to xlManual and neither stopped the message from appearing. What am I doing wrong, how can I insure that message doesn't appear? This is affecting 27 user workbooks.
Though I doubt it matters, here's the Table info for one of the tables:
Usage tab: Enable background refresh and that's it.
Definition tab--
Authentication Settings...: None.
Connection type: Database Query (even though it's hitting an Excel workbook).
Example of Command text from one query:
SELECT Dimen010203.Description, Dimen010203.`FT Program`, Dimen010203.`Project Type`, Dimen010203.`Sub-type`
FROM Dimen010203 Dimen010203
Here's my code:The cell or chart that you are trying to change is protected and therefore read-only.
To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.
Code:
Private Sub CommandButton1_Click()
'Update the dimensions on the user's sheet. User added dimensions and placement order must be retained! Oy!
Dim sFilePath As String, x As Long, sMsg As String, sUseFile As String
On Error GoTo cbErr_Handler 'Handle errors ourselves
'Open the intermediate wkbk, suck its master dimensions into an array, and then close it.
With Application
.StatusBar = "Pulling master dimensions..."
.EnableEvents = False
.ScreenUpdating = False
End With
sFilePath = ThisWorkbook.path & "\..\tools" 'Folder where the master dimension wkbk is
With ActiveWorkbook
'Unprotect the list table sheets
.Worksheets("From_Dimen_Feeder").Unprotect gPASS 'Unprotect the sheet
For x = 1 To .Connections.Count 'Loop through each data connection
Application.StatusBar = "Updating data connection: " & .Connections.Item(x).Name
With .Connections(x).ODBCConnection
'Only change where the data is coming from
If Left(.Parent.Name, 4) = "Cost" Then
sUseFile = gCOST_FEED
Else
sUseFile = gDIM_FEED
End If
.Connection = Array( _
Array("ODBC;DBQ=" & sFilePath & "\" & sUseFile & ";DefaultDir=" & sFilePath & ";Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Driv"), _
Array("erId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserC"), _
Array("ommitSync=Yes;"))
End With
.Connections(x).Refresh 'Update the table (refresh the data). If the file doesn't exist an unhandlable [by VBA] ODBC error will occur.
Next x
'Re-protect worksheets
With .ActiveSheet
[B][COLOR="#DAA520"] .Protect Password:=gPASS, UserInterfaceOnly:=True, Password:=gPASS, DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True[/COLOR][/B]
.EnableSelection = xlUnlockedCells
End With
End With
MsgBox "All data feed inputs have been updated.", vbInformation
cbErr_Resume:
With Application
.StatusBar = False
.ScreenUpdating = True
.EnableEvents = True
End With
On Error GoTo 0 'Resume normal error handling
Exit Sub
cbErr_Handler:
Call Error_Handler(Err.Number, Err.Description, "cbDimensionUpdate_Click()", "Dimensions sheet")
sMsg = "Data sheet(s) may not have been updated." & vbCrLf
sMsg = sMsg & "If you're not connected to the network then this error is expected."
MsgBox sMsg, vbExclamation, "Problem updating a data sheet"
' Stop 'for testing
' Resume Next 'for testing
GoTo cbErr_Resume
End Sub
I tried Application.DisplayAlerts = False and set calculation to xlManual and neither stopped the message from appearing. What am I doing wrong, how can I insure that message doesn't appear? This is affecting 27 user workbooks.
Though I doubt it matters, here's the Table info for one of the tables:
Usage tab: Enable background refresh and that's it.
Definition tab--
Authentication Settings...: None.
Connection type: Database Query (even though it's hitting an Excel workbook).
Example of Command text from one query:
SELECT Dimen010203.Description, Dimen010203.`FT Program`, Dimen010203.`Project Type`, Dimen010203.`Sub-type`
FROM Dimen010203 Dimen010203