[Solved] Error: Macro to protect all as planils (abas)

[Math]

Personally, I've created the following macro in Excel to protect all the sheets (sheets) of the Excel file. worked very well.

         But now, in OpenOffice is accusing an Error in the following line:

         ThisComponent.Sheet.Protect DrawingObjects: = True, Contents: = True, Scenarios: = True, Password: = lPass, AllowFiltering: = True

========================================================================================

follows the whole code below:

Code: Select all

REM  *****  BASIC  *****
'Função que protege todas as planilhas de um arquivo
Option VBASupport 1
Sub lsProtegerTodasAsPlanilhas()
    'Declara as variáveis necessárias
    Dim lPass As String
    Dim lQtdePlan As Integer
    Dim lPlanAtual As Integer
    
    'Sheets(7).Activate
    If Sheets(1).ProtectContents = True Then Exit Sub
    
    'Solicita a senha
    'O método InputBox é utilizado para solicitar um valor através de um formulário
    lPass = InputBox("Proteger todas as planilhas:", "Senha", ActName)

    'Inicia as variáveis
    'O método Worksheets.Count passa a quantidade de planilhas existentes no arquivo
    lQtdePlan = Worksheets.Count
    lPlanAtual = 1

    'Loop pelas planilhas
    'A função While realiza um loop de código enquanto não passar por todas as planilhas contadas
     While lPlanAtual <= lQtdePlan
        'O método Worksheets(lPlanAtual).Activate ativa a planilha conforme o índice atual 1, 2, 3...
        Worksheets(lPlanAtual).Activate
        
        Cells.Select
        Selection.Locked = True  'Bloqueia
        Selection.FormulaHidden = True  'Oculta
        Range("A1").Select

        'O método .Protect proteje a planilha passando os parâmetros para proteger
        'objetos de desenho, conteúdo, cenários e passando o password digitado    
       ThisComponent.Sheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=lPass, AllowFiltering:=True
       
         ActiveWindow.Zoom = 120
         ActiveWindow.DisplayHeadings = False

        'Muda o índice para passar para a próxima planilha
        lPlanAtual = lPlanAtual + 1
    Wend
    
    Sheets("Plan1").Select

    'O método MsgBox exibe um formulário de aviso ao usuário.
    MsgBox "Planilhas protegidas!"

End Sub

========================================================================================

 * Can you help me please fix the bug in this line!


thank you very much

[FJCC]

As you have found, OpenOffice has only a limited ability to translate VB code. It is better to just learn the API of OpenOffice and not rely on the translation. Try the version below. Note that OpenOffice indexes the sheets starting with zero.

Code: Select all

'Função que protege todas as planilhas de um arquivo

Sub lsProtegerTodasAsPlanilhas()
'Declara as variáveis necessárias
Dim lPass As String
Dim lQtdePlan As Integer
Dim lPlanAtual As Integer

Sheets = ThisComponent.Sheets
If Sheets.getByIndex(0).isProtected Then Exit Sub

'Solicita a senha
'O método InputBox é utilizado para solicitar um valor através de um formulário
lPass = InputBox("Proteger todas as planilhas:", "Senha", ActName)

'Inicia as variáveis
'O método Worksheets.Count passa a quantidade de planilhas existentes no arquivo
lQtdePlan = Sheets.Count - 1
lPlanAtual = 0

'Loop pelas planilhas
'A função While realiza um loop de código enquanto não passar por todas as planilhas contadas
While lPlanAtual <= lQtdePlan

oSheet = Sheets.getByIndex(lPlanAtual)
CellProt = oSheet.CellProtection
CellProt.IsLocked = True
CellProt.IsFormulaHidden = True
oSheet.CellProtection = CellProt

'O método .Protect proteje a planilha passando os parâmetros para proteger
oSheet.protect(lPass)

'Muda o índice para passar para a próxima planilha
lPlanAtual = lPlanAtual + 1
Wend
CurrCtrl = ThisComponent.CurrentController
CurrCtrl.HasColumnRowHeaders = False
CurrCtrl.ZoomValue = 120
oSheet = Sheets.getByName("Plan1")
CurrCtrl.setActiveSheet(oSheet)

'O método MsgBox exibe um formulário de aviso ao usuário.
MsgBox "Planilhas protegidas!"

End Sub

[Math]

Greetings FJCC,

              I'm going to do tests with this code that you built. I will return soon answer the test.

              I have one more question to ask:

              I am trying to convert an file.xls (170 MB) to file.ods

              I'm using the following command on the Linux terminal: soffice --headless --convert -to ods * xls

              http://www.ubuntubuzz.com//2016/08/libr ... o-ods.html

              But it's causing Error when I try to open the converted .ods file.

              the error says: bad allocation

              Do you know another command in Linux to convert .xls to .ods?

Note: smaller file size is converting perfectly.


thank you very much.

[FJCC]

For converting the file, I would simply open it in OpenOffice and then use the menu File -> Save As and set the File Type to ODF Spreadsheet (.ods). Such a large file may take a long time to open.