Menu
Excel has a function to generate random numbers: RAND , and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN. However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill. This is OK if you want a small list, but what if you have to generate say a list of 1000 random numbers? The macro shown below will loop through to generate any number of entries you specify. It will also give you an option to specify the number of digits you want in the generated number.
Sub GenNumbers Dim i As Integer, Row As Integer, Col As Integer Dim NoRows As Integer, NoDigits As Integer Dim MinNum As Double, MaxNum As Double Dim NumFormula As String Col = 1 ‘ Write random numbers on column A ‘Get the number of rows and number of digits entered by the user NoRows = ActiveSheet.Range(“B1”).Value NoDigits = ActiveSheet.Range(“B2”).Value ‘Calculate minimum and maximum numbers to be generated MinNum = WorksheetFunction.Power(10, (NoDigits – 1)) MaxNum = WorksheetFunction.Power(10, NoDigits) – 1 ‘Construct the formula to be entered in the cells. NumFormula = “=randbetween(” & MinNum & “,” & MaxNum & “)” ‘ The loop to generate the numbers i = 0 For Row = 3 To NoRows + 2 i = i + 1 ActiveSheet.Cells(Row, Col).Value = i ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula Next Row End Sub How to use the macro 1. Switch to Visual Basic Editor and Insert a new module.
Copy the code into the new module. Close Visual Basic Editor and return to your worksheet. In cell B1 of your worksheet enter the number entries you want. In cell B2 of your worksheet enter the number digits you want. Go to Tools - Macro - Macros, you will see the list of available macros.
Select GenRandomNum and run it. You will get an output like this: Applies to: Excel 2003.
. Introduction Microsoft Office VBA macro: Excel or Word to save a document as password protected / encrypted PDF.
Let’s talk Office 2010. Looks like not too complex task. Start a macro recorder, go to: File / Save as. Select “Save as Type” and pick “PDF”. You will see an “Options” button appear.
Click that and check the box “Encrypt the document with a password”. Now, you will be asked to type in a password. Stop macro recording. Copy this code into whatever your VBA macro will be. But you’re not finished yet, the recorded code has no trace of password related activity.
Let’s go to MSDN:. Document.SaveAs2 Method (Word) SaveAs2(FileName, FileFormat, LockComments, Password, AddToRecentFiles, WritePassword, ReadOnlyRecommended, EmbedTrueTypeFonts, SaveNativePictureFormat, SaveFormsData, SaveAsAOCELetter, Encoding, InsertLineBreaks, AllowSubstitutions, LineEnding, AddBiDiMarks, CompatibilityMode) Saving as a word encrypted from VBA such as: ActiveDocument.SaveAs2 fileName:= 'myDoc', Password:='mySecret', WritePassword:= 'mySecret2', AddToRecentFiles:=False Will produce a password protected document (word). Now let’s move on to PDF. You can use: ActiveDocument.SaveAs2 fileName:= 'myDoc', AddToRecentFiles:=False, Password:='mySecret', WritePassword= 'mySecret2', FileFormat:=WdSaveFormat.wdFormatPDF Or a similar format: ActiveDocument.ExportAsFixedFormat. In both cases, you will get your PDF.
Alas, unprotected. I have spent time trying to play with some parameters so you can spare yours. Unfortunately, MSDN documentation remains silent on this topic. As for PDF, the parameter Password, as mentioned above, is ignored. Oddly, Word and Excel can do it, but Microsoft did not deliver this functionality to the document object model. At the very minimum, MSDN should address this deficiency, saving the developer’s time. A simple Internet search will refer you to third party tools (you need install one on a user computer) and remains the only the choice thus far.
![]()
For PDF tools, you can get either paid PRO versions or free. Some of PDF tools can be engaged with VBA/VBS via COM/Interop, and some only via command line executable that you can run from VBA. Beside encrypting/password protecting a PDF document, there are different permissions can be set as a document’s security options. Selecting third party tool/libraries can be somewhat time consuming due to licensing (and if you’re doing work for a corporation you may also require IT/Security approval, which can take time – and I’m guessing you don’t want to waste time coding for tools which can be rejected months later), performance, memory footprint – some are even full PDF editors which include GUI components. Some of the free tools do not even provide a full set of PDF security options or are limited to only 40 – bit encryption, unless you pay for the pro version. Rummaging through all the above to address the simple task of filling in a gap in the Word/Excel object model functionality to save an encrypted PDF and set different security options from VBA, I found that the simplest and quickest solution to make a custom COM wrapper around one of the well-known.NET open source libraries.
As an in house component, you shouldn’t have to worry about getting IT approval. If you not familiar with making COM visible.NET DLL, here is an attached Visual Studio 2013.NET v 4.0 project, written in C# and wrapping a popular library ITEXTSHARP.dll which is a.NET PDF library ported from Java. This example is fully functioning.
Feel free to modify / extend in order to tailor to your needs. For simplicity, ITEXTSHARP.dll is imbedded into single output resulting library ProtectPDF.dll with a single method: GoPDF.ProtectPdfStandard(string passwordUser, string passwordOwner, string namepathPDFIn, string namepathPDFOUT ) This method converts an unprotected PDF into password protected / 128 – bit encrypted one, with security setting only to Allow Printing. You can modify the code to customize security settings as they are just bitwise OR options. If you are not familiar with what needs to be done next, here are the remaining steps:. Your Windows computer should have.NET 4.0 installed, which is a standard now. You need to place a projects output library ProtectPDF.dll anywhere on your local hard drive (probably your application folder or a common one if you will be sharing this component) MyFolder. Copy.NET Utility RegAsm.exe (which comes with.NET framework) into MyFolder.
![]()
If you are comfortable with all DOS prompts commands, you can run that utility from the original location, but I always tend to copy that utility along with the component I want to register. Sometimes, you want to move folder, which means you would have to unregister the component using the same utility, move folder, then register again. Or maybe you want to install the component on a different computer so you won’t have to look for RegAsm.exe again as it is VERSION SPECIFIC! In other words, if your component is built on.NET 4.0, then you need to use the appropriate version of RegAsm.exe.NET 5.0 yet has to be seen.
Anyway, a typical location for RegAsm.exe for.NET 4.0 is usually in a Windows folder similar to: C: Windows Microsoft.NET Framework64 v4.0.30319 or: C: Windows Microsoft.NET Framework v4.0.30319 if you are on 32-bit CPU. Register the library on computer (this may require admin rights), by running the following CMD (PROMPT) command: cd myFolder myFolderRegAsm.exe ProtectPDF.dll /tlb: ProtectPDF.tlb /codebase.
The above will register the ProtectPDF.dll component on the system and also generate/register the type library ProtectPDF.tlb which you will be referring to from your Excel/Word VBA by adding ProtectPDF.tlb to References. If you need to re-register your component (i.e., you want to move the folder or have a new version of the component), unregister the component using the following: RegAsm.exe ProtectPDF.dll /tlb: ProtectPDF.tlb /codebase /unregister The rest is as usual: Dim error As String Dim protectObj as ProtectPDF.GoPdf Set protectObj = New ProtectPDF.GoPdf error = protectObj. ProtectPdfStandard( ' usrSecret', ' ownerSecret', ' namepathPDFIn', ' namepathPDFOUT' ) Hopefully, the steps above will save you time when you need to create a protected PDF from MS Office VBA code. Rcully 12-Jun-15 16:39 12-Jun-15 16:39 Thank you for your post and the work you've done.
This is exactly what I have been researching and discovered. I'm trying to use the DLL on a Windows 8 system, accessing the it through Access 2013 VBA. I ran into a few issues and haven't been able to sort out what is going on. When I registered the DLL the first time I discovered a syntax error in the registration code snipit on the post; the post puts a space between /tlb: and the ProtectPDF.tlb name.
That space needs to be deleted. After that regasm gave a message that was a little confusing. It said: RegASM: warning RA0000: Registering anunsigned assembly with /codebase can cause your assembly to interfere with other applications that may be installed on the same computer. The /codebase switch is intended to be used only with signed assemblies. Please give your assembly a strong name and re-register it. Microsoft.NET Framework Assembly Registration Utility version 4.0.0 for Microsoft.NET Framework version 4.0.0 Copyright (C) Microsoft Corporation. All rights reserved.
Types registered successfully Assembly exported to 'C: myFolder ProtectPDF.tlb', and the type library was registered successfully I tried to locate the DLL after registration in Tools-References but didn't see it. So I Browsed to MyFolder and selected ProtectPDF.tlb which entered a reference to 'ProtectPDF-VBA/VBS COM wrapper for ITextSharp'. B association pro keygen. At that point the code snipit compiled successfully.
However upon running the code it fails at the line Set protectObj = New ProtectPDF.GoPdf The error is Run-time error '429': ActiveX component can't create object. Any ideas on how to solve this? I have installed this today on a 64bit Windows 7 PC. Initially I has the same 'Cannot create component' problems, and the library did not appear in references. I unregistered it as described, then re-registered using the 32bit regasm. After that, it all worked correctly.
Hope that helps anyone in the future. Last Visit: 31-Dec-99 19:00 Last Update: 6-Jan-18 23:48 1 General News Suggestion Question Bug Answer Joke Praise Rant Admin Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
Macro To Break Password Excel
Function RandomString(Length As Integer) 'PURPOSE: Create a Randomized String of Characters 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault Dim CharacterBank As Variant Dim x As Long Dim str As String 'Test Length Input If Length. How Do I Modify This To Fit My Specific Needs? Chances are this post did not give you the exact answer you were looking for.
We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! I highly recommend that you check out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction! Chris Founder, TheSpreadsheetGuru.com.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |