Posts by MisterT

Re: Open PDF Files, Save With New Security Settings

Thanks Guys - I have had no luck at all and reluctantly agree with you.

Much appreciated,
Mike

Hi,

I am trying to use a shell command to dig into a specified directory (and ideally all sub-directories) and open every pdf file and re-save with security settings changed (adding a password to prevent printing).

I can't seem to get the shell command to run from where it is:

Option Explicit
 
Sub OpenPDFFilesAndSave()
     'opens every pdf file in a directory
    Dim FSO As Object, Fld As Object, Fil As Object
    Dim MainFolderName As String, i As Integer
    Dim RetVal As Variant
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    MainFolderName = "C:\Test"
    Set Fld = FSO.GetFolder(MainFolderName)
    For Each Fil In Fld.Files
        i = i + 1
        RetVal = Shell("C:\Program Files\Adobe\Acrobat 7.0 
         _&\Acrobat\Acrobat.exe MainFolderName & " \ " & Fil.Name")
         
    Next
'have to add save the file, and change the print settings but not figured that out yet


End Sub

Any help would be much appreciated!
Thanks in advance,
Mike

I came across this code to allow the user to rename illegal names:

Sub Rename_BadNames()
If ActiveWorkbook.Names.Count > 0 Then
With Application
.ReferenceStyle = xlR1C1
.ReferenceStyle = xlA1
End With
End If
End Sub

in this thread: http://ozgrid.com/forum/showth…21&highlight=delete+names

Can anybody add the required loop to rename them all legally?
Something like zzz1, zzz2, zzz3, etc.

I keep inheriting workbooks with 1,000's of Lotus 123 names that begin with illefgal characters, and want to delete all illegal external names and #Ref! names in one go...

Any help would be much appreciated,
Mike

Re: VBA Project "Reference To" another (protected) project

That's great Derk. Thanks.

Rob Bopvey's Code Cleaner is exactly what I'm after. Does anybody have a zipped copy or a link to a zipped copy as the firewall blocks .exe files?

Thanks in advance,

Mike

Re: Macro for Viewing select cells

Although someone here will no doubt come up with a clever solution, I find saving custom views (View > Custom Views) is the easiest solution. You can then record a macro that shows a particular view.

Something like:

Sub ShowView()


' ShowView Macro


    ActiveWorkbook.CustomViews("<insert your viewname here>").Show
End Sub

Hope that helps,

Mike

Hi,

My personal.xls file has a reference to a protected add-in showing in the VBE which causes an error on opening excel.
Any ideas how can I remove the reference?

On a similar note: Does anyone have the saved link for that great "Code Cleaner" macro I remember using a while back?

Thanks in advance,

Mike

Re: Gridlines & Print Footer

Below are my solutions - there may be more efficient code for the first, but it works..

Sub GridlinesAllSheets()


'Groups all sheets in workbook and turns off gridlines
Dim wsSheet As Worksheet


    For Each wsSheet In ActiveWorkbook.Worksheets
        If wsSheet.Visible Then
        wsSheet.Select Replace:=False
    End If
    Next wsSheet
ActiveWindow.DisplayGridlines = False


End Sub

The following can be stored in personal.xls and fired up every time you want to, rather than saved as a default.

Sub FooterStetup()
With ActiveSheet.PageSetup
            .CenterFooter = "Page &P of &N"
End With
End Sub

You could combine 1 and 2 if you like.

Hope that helps

Re: Creating Live Links from Indirect() Formulae?

Derk,

That is just brilliant! I create outputs from 100-odd sheet workbooks, and navigating to the sheet to build/link the output page is painful. Thankfully, the sheets are standard so indirects work.
The trouble is that if a number loooks wrong, I can't easily navigate to that sheet and cell to check it. (Ctrl - { is a godsend).

This works superbly!

Thanks very much.

Re: Insert Sheet name formula with VBA

Quote from Richie(UK)

I have just tried your example add-in, called the routine via the shortcut keys and .... it still works!

Nope, still doesn't work for me. I am going to uninstall all other addins and then give it a go. I can only assume there is some kind of conflict...

Quote from Richie(UK)

Is there anything else we should know? Does the example add-in work for you (uninstall the problem add-in and try the example)?

The example addin I created above doesn't work for me, either.

Thanks for your time!

Re: Insert Sheet name formula with VBA

Attached is the simple addin. All I have done is assign a shortcut (Ctrl-Shift E) and save it as an xla.

I get "Argument not Optional" every time.

Can you confirm this?

Re: Insert Sheet name formula with VBA

Hmm?

Well I have it pasted into a standard module and have a simple dropdown menu to fire it up. Always the error.

Will try creating it as a single AddIn, and see if it works.

Thanks anyway,

Mike

Re: Insert Sheet name formula with VBA

Thanks Roy, but I tried:

Sub Sheetname()
'
' Inserts Sheet name formula into active cell
    ThisWorkbook.ActiveCell.Formula = _
        "=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,255)"
    ActiveCell.Select
End Sub

But to no avail...

I use the following line to insert the formula for a sheet name:

Sub Sheetname()
' Inserts Sheet name formula into active cell
   ActiveCell.Formula = _
        "=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,255)"
   ActiveCell.Select
End Sub

This works perfectly in a workbook, but when put into an AddIn, it gives an "Argument not Optional" error. I have looked in Help but can't find what argument I am missing, and why it only matters in an AddIn?

Any ideas?

BTW: I realise it is easier to insert sheetname from the sheet property, but I specifically want the formula

Re: Making a cell content the same as a worksheet name ...

I tried to use VBA to insert this formula(and not just the value), and it works as a macro in a workbook, but not inside and Add-in?
Error message is "Argument is not Optional"

Any ideas?

Sub InsertSheetname()
ActiveCell.Formula = _
        "=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,255)"
    ActiveCell.Select
End Sub

Re: Editing multiple formulas in a sheet

Check that there are not external links:
Edit > Links
If there is a reference to an old (possibly deleted) version of the file, click "change source" and reference the latest (active) file.
Now find and replace should work.

Note, select range before running find and replace if there are a lot of links on a page, and it helps to be speciifc - i.e. search and replace "CT!" for, say, "NY!" including the ! but not the "".
This will stop you replacing text, and other occurences of CT in the workbook.

Hope that helps,
Mike

Re: Pasted text defaulting to date format

Musicman,

There are (at least) two solutions:
1. Use the following formula to build out your desired output:
=MID(C1,1,2)&"-"&MID(C1,4,5) Where the 09=09 is in cell C1.

2. Find and replace "=" with "" in excel (not word) (without the quotation marks. Then format with custom 00-00

Both of those work for me.

Mike

Re: Pasted text defaulting to date format

What does the 09=09 refer to - ie what is the desired output?

Hi,

I find Indirect() to be really useful in quickly creating output tables of large workbooks, but it is really annoying that you can't then navigate back to the cell it is displaying (say, using Ctrl { ).

I'm just wondering if anybody has ever written something which would evaluate indirect functions on a sheet and rebuild the formula so that it creates a proper "live" link?

I suppose a simpler approach would be to build a hyperlink based on the formula, whick links to the cell.

Long shot, but would be keen to see if anybody has already tackled this..

Cheers,
Mike

Re: A macro to insert a module?

I have come up with the following modification, which works, but it is not a great solution as it requires macro security level to be altered by users, which defeats the purpose a bit.

Sub CopyOneModule()
'Courtesy of [url]http://www.cpearson.com/excel/vbe.htm[/url]
Dim FName As String
Dim currBook As Workbook
Set currBook = ActiveWorkbook
With Workbooks("XL_Macros_v1.2b.xls")
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Mod_PrintSelected").Export FName
End With
currBook.VBProject.VBComponents.Import FName
End Sub

I think it may end up easier to instruct users to copy the code, insert module, paste code...

Disappointing.

Re: A macro to insert a module?

Thanks - that is great stuff.