Sunday, 24 March 2013

Membuka Proteksi Sheet EXCEL (XLSX, XLSM) Tanpa Software Khusus

Ketika Microsoft memperkenalkan Excel 2007, mereka memperkenalkan jenis file baru  seperti xlsx, xlsm, xltx, dll. Jenis file yang tersebut sering disebut sebagai Open XML. Itu karena jenis file baru pada dasarnya paket yang berisi file XML. Jika Anda mengambil file xlsx dan mengubah ekstensi ke zip, Anda akan dapat melihat semua dokumen xml yang membentuk file Excel.
File tipe Open XML merupakan file yang banyak manfaatnya. Salah satu manfaat utama adalah bahwa Anda dapat mengubah isi dan properti dari file Excel 2007 hanya dengan memanipulasi dokumen XML yang membuat itu. 
Maslahanya jika bukan anda sendiri yang membuat file tersebut, tentunya akan kesulitan dalam mengedit data tersebut. Apalagi data tersebut penting akan sangat perlu memindahkan dalam bentuk data lain (copy dan paste).

Untuk dapat mengedit file xml dari excel tersebut maka anda harus menghapus proteksi di sheet tersebut. Berikut ini adalah langkah-langkahnya:
Langkah 1 : Buat backup file tersebut jika suatu saat anda membutuhkannya lagi.

Langkah 2 : Ubah ekstensi file tersebut menjadi .zip


Langkah 3 : Ekstrak file tersebut.


Langkah 4 : Buka file yang diekstrak dan arahkan ke xml untuk lembar  target (di direktori 'xl/worksheet/sheet1)


Langkah 5 : Buka file "sheet1" dengan menggunakan xml editor (contohnya notepad++)

Langkah 6 : Cari tag 'sheetProtection' dan hapus seluruh baris yang saya beri kotak hitam.



Langkah 7 : Simpan file xml tersebut dan masukan/replace file sheet1.xml yang ada di file zip tadi.

Langkah 8 : Ubah kembali ekstensinya menjadi .xlsm.

Taraaaaaaaaaaaaaaaaaaa, file atau sheet anda sudah bisa di copy dan paste .

Note: Jika file aslinya masih xls maka disave as dulu menjadi xlsx atau xlsm agar bisa dirubah menjadi file zip.
By : kukuhsujarwo

Posted by at
Labels: , , , , , , ,

8 comments:

  1. drtommy9427 May 2016 at 19:07

    Break the password using alt-f11 and enter

    Sub PasswordBreaker()
    'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
    Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
    Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
    MsgBox "One usable password is " & Chr(i) & Chr(j) & _
    Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
    Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
    End Sub

    i think much fuckin easier then this shit!!!

    think...

    ReplyDelete
  2. Unknown9 November 2016 at 08:56

    thanks for tips

    ReplyDelete
  3. Unknown31 January 2018 at 20:32

    when I tried to edit some component of the file, appears dialog box that written "This operation was cancelled... Please contact your system administrator"

    do you have any solution?

    ReplyDelete
    Replies
    1. Unknown10 May 2018 at 13:41

      try this solutions
      in excel ALT-F11 - enter
      Sub PasswordBreaker()
      'Breaks worksheet password protection.
      Dim i As Integer, j As Integer, k As Integer
      Dim l As Integer, m As Integer, n As Integer
      Dim i1 As Integer, i2 As Integer, i3 As Integer
      Dim i4 As Integer, i5 As Integer, i6 As Integer
      On Error Resume Next
      For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
      For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
      For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
      For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
      ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
      Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
      Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
      If ActiveSheet.ProtectContents = False Then
      MsgBox "One usable password is " & Chr(i) & Chr(j) & _
      Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
      Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
      Exit Sub
      End If
      Next: Next: Next: Next: Next: Next
      Next: Next: Next: Next: Next: Next
      End Sub

      Delete
  4. Unknown10 May 2018 at 08:18

    yg dihapis kotak hitamnya sebelah mana ya?

    ReplyDelete
    Replies
    1. Unknown10 May 2018 at 13:41

      try this solutions
      in excel ALT-F11 - enter
      Sub PasswordBreaker()
      'Breaks worksheet password protection.
      Dim i As Integer, j As Integer, k As Integer
      Dim l As Integer, m As Integer, n As Integer
      Dim i1 As Integer, i2 As Integer, i3 As Integer
      Dim i4 As Integer, i5 As Integer, i6 As Integer
      On Error Resume Next
      For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
      For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
      For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
      For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
      ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
      Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
      Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
      If ActiveSheet.ProtectContents = False Then
      MsgBox "One usable password is " & Chr(i) & Chr(j) & _
      Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
      Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
      Exit Sub
      End If
      Next: Next: Next: Next: Next: Next
      Next: Next: Next: Next: Next: Next
      End Sub

      Delete
  5. LOVELYZ TRILOGY25 October 2018 at 15:41

    makasih min infonya
    multitester digital

    ReplyDelete
  6. TeguhAziz7 December 2018 at 18:38

    Gak ngerti mohon bantuanya

    ReplyDelete

Subscribe to: Post Comments (Atom)