Unlock protect sheet xlsx

1 rename xlsx to zip
> ren protect.xlsx protect.zip
2 In zip goto xl  --> worksheets
3 unzip sheetx.xml tofix it (outside zip)
4 open sheetx.xml and search "protect"
5 remove

example


6 put all remove protect back to zip
7 rename zip to xlsx

Finish

Cr.
http://www.excelsupersite.com

http://www.excelsupersite.com/how-to-remove-an-excel-spreadsheet-password-in-6-easy-steps/

Create Jar include dependencies and run

'    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.matco</groupId>

    <artifactId>pdfcutter</artifactId>

    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <dependencies>

        <dependency>

            <groupId>com.itextpdf</groupId>

            <artifactId>itextpdf</artifactId>

            <version>5.5.13.2</version>

        </dependency>

    </dependencies>

    <properties>

        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

        <maven.compiler.source>8</maven.compiler.source>

        <maven.compiler.target>8</maven.compiler.target>

    </properties>

    <build>

  <plugins>

    <plugin>

      <artifactId>maven-assembly-plugin</artifactId>

      <configuration>

        <archive>

          <manifest>

            <mainClass>com.matco.pdfcutter.Pdfcutter</mainClass>

          </manifest>

        </archive>

        <descriptorRefs>

          <descriptorRef>jar-with-dependencies</descriptorRef>

        </descriptorRefs>

      </configuration>

    </plugin>

  </plugins>

</build>

</project>

'     
'       
'          maven-assembly-plugin
'         
'           
'             
'                com.matco.pdfcutter.Pdfcutter
'             
'           
'           
'              jar-with-dependencies
'           
'         
'       
'     
'   
after build
@folder project
example c:/..../Pdfcutter/
cmd mvn clean compile assembly:single

Mvn must download and install


'   
'        UTF-8
'        1.8
'        1.8
'   

for MVN compile to collect version

VIM command

vi

i to write mode

Esc to exit mode

:wq to quit & save
:q! to quit & not save

x to delete

Function แก้ไข date เพิ่ม0 และกำหนดรูปแบบ // Function select file and exec and insert to worksheet

Function Ndate(x, y, R)
A = Mid(x, 1, 2)
B = Mid(x, 4, 2)
R = R
newdate = DateAdd("d", 1, A & "-" & MonthName(B) & "-" & y)
A = Day(newdate)
If Len(A) = 1 Then
    A = "0" & A
End If
B = Month(newdate)
If Len(B) = 1 Then
    B = "0" & B
End If
    Ndate = A & "/" & B
End Function

Sub FileOpenDialogBox()

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "PDF Files", "*.pdf", 1
        'Show the dialog box
        .Show
       
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
   
    fullpath = """X:\Delivery Section\Test PDS_01\Print KB\pdftotext.exe""" + fullpath + " /to " + """X:\Delivery Section\Test PDS_01\Print KB\1.txt"""
    'fullpath = "X:\Delivery Section\Test PDS_01\Print KB\pdftotext.exe"
    'MsgBox fullpath
Shell fullpath, vbNormalFocus
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet

    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("PDF") '<~~ Sheet where you want to import

    Set wbO = Workbooks.Open("X:\Delivery Section\Test PDS_01\Print KB\1.txt")

    wbO.Sheets(1).Cells.Copy wsI.Cells



    wbO.Close SaveChanges:=False
End Sub

INDIRECT FUNCTION ใช้ในการเชื่อมชื่อชีทโดยอ้างอิงเซลได้

INDIRECT("'"&sheet&"'!"&name)

example

=countif(indirect(D$1&"!$A:$A"),$A2)

=countif(MUSCATEL!$A:$A,$A2)

REFERENCE SHEET NAME FROM CELL.VALUE