Mysql query to protected spreadsheet

[muppet]

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

[Oxiegen]

What happens if you reset the protection after you update the Excel sheet?

By looking at your code it seems like you fírst unlock the sheet, read from the MySql database and then lock the sheet again, without updating the sheet.
When do you do your update?

[muppet]

I am not forcing an update, I was not aware sure that I could.
It just seemed that the update happened after the code had finished running. I tried putting a sleep time in the code, hoping that the update would happen while it was asleep, but it does not happen until after the code has finished.

[Oxiegen]

Ah! Now I see what you're doing.
What would happen if you save the excel document before doing the protection thing?

[muppet]

Save before I run my code, or force a file save with the code?

[Oxiegen]

Force a save just before you apply the protection.
I'm just providing suggestions, not solutions, because I don't know how you access the excel file.

'' --- Save the file here --- ''

'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