I am using VB to query a MySQl database and populate an Excel spreadsheet. All is working well, but now I would like to start distributing the spreadsheet shroughout the company and cannot protect it.
If I protect the spreadsheet the VB cannot add the MySQL data, so I added an unprotect line at the start of the code. That works well, but now the sheet is unprotected, if I add a protect line at the end of the program it cannot finish the query. help?:-/
'Unprotect all sheets Dim WSheet As Worksheet For Each WSheet In Worksheets If WSheet.ProtectContents = True Then WSheet.Unprotect Password:="password" End If Next WSheet 'MySQL Query With ActiveSheet.QueryTables.Add(Connection:="ODBC;FileDSN=C:\MySQL.dsn;", Destination _ :=Range("B4")) .CommandText = "SELECT tech, count(tech) as count from abm_status where tech != '' and (" & shifttime & ") and machine = '" & machine & "' group by tech order by count desc limit 1" .Name = "TechniciansName" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceConnectionFile = "C:\MySQL.dsn" .Refresh BackgroundQuery:=True End With 'Protect all sheets Dim WSheet As Worksheet For Each WSheet In Worksheets If WSheet.ProtectContents = False Then WSheet.Protect Password:="password" End If Next WSheet