0

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
2
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by Oxiegen
0

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?

0

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.

0

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

0

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

0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.