CARA MEMBUKA VBA EXCEL YG DIPROTEK FILE UNVIEWABELE

Microsoft Excel has been an essential tool for businesses and individuals who wish to organize and manipulate data. However, not everyone is adept with this software. Some people struggle with the basics, while others find themselves facing complex problems that they cannot solve on their own.

Belajar Microsoft Excel: Mengekstrak Angka dari Text Data Entri

One common difficulty that people face when working with Excel is trying to extract numbers from a text data entry. For example, if you have a column that contains data such as “100 dollars”, “50 cents”, or “70% off”, you may want to extract just the numeric value from each entry so you can perform calculations.

The good news is that there is a way to extract numbers from text data entries in Excel using formulas. The approach involves using a combination of the IFERROR, SUMPRODUCT, MID, and FIND functions.

Here are the steps:

1. Insert a new column to the right of the column containing the text data entry.
2. In the first cell of the new column, enter the following formula:

=IFERROR(SUMPRODUCT(0+MID(B1,FIND(0,1,2,3,4,5,6,7,8,9,B1),LEN(B1))),0)

• The formula will first find the position of the first numeric character in the text data entry using the FIND function. The 0,1,2,3,4,5,6,7,8,9 array specifies the numbers to find.
• The formula then uses the MID function to extract the numeric value from the text data entry based on the position found by the FIND function.
• The 0+ before the MID function converts the resulting text string to a numeric value.
• The SUMPRODUCT function adds up the numeric values found in the text data entry.
• The IFERROR function handles the case where there are no numeric values found in the text data entry by returning a zero instead of an error message.

3. Copy the formula down to all the cells in the new column.
4. The new column now contains the numeric values extracted from the text data entries in the original column.

Cara Membuka File Excel Menggunakan VBA GetOpenFileName

Opening a file in Excel using the GetOpenFileName method in Visual Basic for Applications (VBA) is very useful if you have to work with a lot of files regularly.

Using this method allows the user to select the file they would like to open instead of hard-coding the file path and name in your VBA code, which can be time-consuming and may cause errors if the file is moved or renamed.

Here is the step-by-step guide on how to use GetOpenFileName method in VBA:

1. Open a new or existing Excel file and press ALT + F11 to open the Visual Basic Editor (VBE).
2. Insert a new module by clicking “Insert” from the menu and selecting “Module”.
3. In the new module, input the following code:

Sub OpenFile()
Dim strFile As String
strFile = Application.GetOpenFilename()
If strFile <> False Then
    Workbooks.Open strFile
End If
End Sub

• The first line declares a variable “strFile” as a string data type to store the selected file path and name.
• The second line calls the GetOpenFilename method to display the “Open” dialog window and assign the selected file path and name to the “strFile” variable.
• The third and fourth lines check to see if a file has been selected and, if so, open the selected file using the Workbooks.Open method.

4. Save the module and return to the Excel workbook.
5. Select the Developer tab from the ribbon, and then click the “Visual Basic” button to open the VBE.
6. Click the “Macros” button under the Developer tab and select the “OpenFile” macro you just created.
7. Click “Run” to execute the macro. The “Open” dialog window will appear, allowing you to select the file you want to open.

Cara Membuka Excel Yang Dipassword

What if you forget the password of your Excel file that you have been working on for hours, or maybe you are handed over an Excel file that is password protected and you don’t have the password?

You might think all your hard work has gone down the drain. But don’t worry, there are several ways to unprotect an Excel sheet, depending on the version of Excel you are using and the type of protection that has been applied to the sheet.

Here are a few methods that you can try:

1. Use VBA Code: If you have a password-protected workbook, you can use a simple VBA code to unprotect it. Press Alt + F11 to open the VBA editor, click on Insert, and then click on Module to add a new module. Copy and paste the following code in the new module:

Sub PasswordBreaker()
    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 "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

• Run this VBA code by pressing F5 or clicking on Run Sub/UserForm button. If the password is a combination of uppercase and lowercase letters, then change the For loop value to 97 to 122 (Lowercase ASCII codes) and 65 to 90 (Uppercase ASCII codes).

2. Save the file in HTML format: If the workbook is protected with a password to open, you can save the file in HTML format by clicking on File and choosing Save As. In the Save As dialog box, select “Web page” from the “Save as type” dropdown list. The saved file will have the same functionality as the original Excel file, but the password protection will be removed.
3. Use an Excel add-in: There are several Excel add-ins available online that can be used to remove password protection from Excel sheets. One example is “Excel Password Recovery Master”.

Cara Membuka File MS Word melalui Kotak Input Macro VBA Excel

If you ever find yourself in a situation where you need to open a Word file from within an Excel workbook using VBA, you can use an InputBox to prompt the user for the file path and name.

Here are the steps to follow:

1. Open a new or existing Excel file and press ALT + F11 to open the Visual Basic Editor (VBE).
2. Insert a new module by clicking “Insert” from the menu and selecting “Module”.
3. In the new module, input the following code:

Sub OpenWordDoc()
    Dim objWord As Object
    Dim strPath As String
    
    strPath = InputBox("Enter the file path and name of the Word document")
    
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    
    objWord.Documents.Open strPath
    objWord.Activate
End Sub

• The first line declares two variables: “objWord” as an object data type, which will be used to represent the Word application, and “strPath” as a string data type, which will be used to store the file path and name of the Word document.
• The second line uses the InputBox function to prompt the user to enter the file path and name of the Word document.
• The third and fourth lines create a new instance of the Word application using the CreateObject function and sets it to visible so that the user can see it.
• The fifth line opens the specified Word document using the objWord.Documents.Open method.
• The last line activates the Word application so that the user can interact with it.

4. Save the module and return to the Excel workbook.
5. Select the Developer tab from the ribbon, and then click the “Visual Basic” button to open the VBE.
6. Click the “Macros” button under the Developer tab and select the “OpenWordDoc” macro you just created.
7. Click “Run” to execute the macro. The InputBox will appear, allowing you to enter the file path and name of the Word document you want to open.

FAQ:

1. How do I protect my Excel sheet from being accessed by others?

You can protect your Excel sheet by setting a password to prevent others from accessing it without your permission. To do this, click on the “Review” tab and choose “Protect Sheet” from the “Changes” group. Enter a password when prompted and choose any additional options you’d like to apply. It is important to remember your password as there are limited options to retrieve it if forgotten.

2. Can I create a pivot table with data from multiple sheets?

Yes, you can create a pivot table that collects data from multiple sheets in an Excel workbook. First, select the first sheet that contains the data you want to use in your pivot table. Then, click on the “Insert” tab, select “PivotTable”, and choose “Multiple Consolidation Ranges”. The PivotTable Wizard will appear, and you will need to follow the steps to add the ranges to the pivot table. This process works for data that is structured in a similar way across multiple sheets in the workbook.

Video:

For a step-by-step and visual guide on how to extract numbers from text data entries in Excel, check out this tutorial on YouTube: