I'm new so please be patient. I have searched for similar threads and not found a solution to my problem.
I have a protected worksheet containing an SQL query.
I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.
Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below
- My macro won't unprotect the sheet before it calls the SQL.
- it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify query
- it won't run the query / return the data as the sheet is still protected
I have had to remove sheet protection in order to allow the sql to execute and return data.
If protection is removed, the query does execute and return data (changes in the "parameter" cell contents do cause the query to be correctly modified)
I could comment out the debug info but the query still doesn't return data if the sheet is protected.
I apprecaite I have mixed code at present... e.g. still using both
[COLOR="Blue"]Activesheet[/COLOR] and [COLOR="blue"]worksheets(ndx)[/COLOR] where [COLOR="blue"]ndx[/COLOR] has been defined as [COLOR="blue"]ActiveSheet.Name[/COLOR] I apologise if my post is difficult to follow - especially the code. [FONT="Courier New"][SIZE="2"][COLOR="Blue"] Sub AgedStockParameters() nmb = ActiveWorkbook.Name ndx = ActiveSheet.Name Workbooks(nmb).Activate Worksheets(ndx).Activate [/COLOR] '[COLOR="DarkRed"] This is the bit that seems to fail ' I've tried it using Workbooks(nmb).Worksheets(ndx).Uprotect ' and just Worksheets(ndx).Uprotect ' and just ActiveSheet.Unprotect ' ' and I've tried it both with and without passwords - all to no avail[/COLOR] [COLOR="Blue"] ' commented out to avoid protection and ensure query runs & returns data 'ActiveWorkbook.ActiveSheet.Unprotect Password:="agedstock" ActiveSheet.Range("A1").Select WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection ActiveCell.Value = WkshtQryCon Range("A2").Select WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText ActiveCell.Value = WkshtQryFld Range("A3").Select WkshtQryPrm = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Parameters.Count ActiveCell.Value = WkshtQryPrm sql0 = "SELECT InvMultBin.Warehouse, InvMultBin.StockCode, (InvMaster.Description + ' ' + InvMaster.LongDesc) ," sql1 = "InvMaster.AlternateKey1, InvMultBin.Bin, InvMultBin.QtyOnHand1, InvMultBin.QtyOnHand2, InvMultBin.QtyOnHand3," sql2 = "InvMultBin.SoQtyToShip, InvMultBin.QtyDispatched " sql3 = "FROM SysproCompanyH.dbo.InvMultBin InvMultBin, SysproCompanyH.dbo.InvMaster InvMaster " sql4 = "WHERE (InvMaster.StockCode = InvMultBin.StockCode) AND (InvMultBin.Warehouse=" sql5 = ") AND (InvMultBin.QtyOnHand1>$0) " sql6 = "AND (InvMultBin.StockCode IN (SELECT InvFifo.StockCode " sql7 = "FROM SysproCompanyH.dbo.InvFifoLifo InvFifo " sql8 = "WHERE (DATEDIFF(day,InvFifo.LastReceiptDate,GETDATE())" sql4a = "'BI'" sql8a = "between 365 and 1095)))" Range("B4").Select If ActiveCell.Value <> "" And Range("B6").Value And Not (Range("C6").Value) And Range("B8").Value = 2 _ Then sql4a = "'" + ActiveCell.Value + "'" Range("B5").Select casecheck = ActiveCell.Value If ActiveCell.Value <> "" And Range("B7").Value And Not (Range("C7").Value) And Range("C8").Value = 1 _ Then Select Case casecheck Case 1 To 30 sql8a = "between 1 and 30)))" Case 31 To 60 sql8a = "between 31 and 60)))" Case 61 To 90 sql8a = "between 61 and 90)))" Case 91 To 120 sql8a = "between 91 and 120)))" Case 121 To 150 sql8a = "between 121 and 150)))" Case 151 To 180 sql8a = "between 151 and 180)))" Case 181 To 270 sql8a = "between 181 and 270)))" Case 271 To 365 sql8a = "between 271 and 364)))" Case Else sql8a = "between 365 and 1095)))" End Select End If sqlconcat = sql0 + sql1 + sql2 + sql3 + sql4 + sql4a + sql5 + sql6 + sql7 + sql8 + sql8a Set qt = Workbooks(nmb).Worksheets(ndx).QueryTables(1) qt.Sql = sqlconcat qt.Refresh Range("B3").Select WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText ActiveCell.Value = WkshtQryFld ' commented out to avoid protection and ensure query runs & returns data 'ActiveSheet.Protect Password:="agedstock", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub[/COLOR][/SIZE][/FONT]