Posts by AJW


    This assumes that the workbook that contains the Macro you want to run is already open as either a visable workbook or addin.

    Application.Run "'MyFile.xls'!MyMacro"

    If the workbook is not open you will need to have it open - run your macro - have it shut.

    One way this can be done:

    Sub OpenAndRun() 'Untested
    'Filesearch is great for non specific file location
    With Application.FileSearch
    .LookIn = "C:\Temp\MyFiles\"
    .SearchSubFolders = True
    .FileName = "MacroFile.xls"
    .MatchAllWordForms = True
    .FileType = msoFileTypeAllFiles
    If .Execute() > 0 Then
    For I = 1 To .FoundFiles.Count
    Workbooks.Open FileName:=Application.FileSearch.FoundFiles(I)
    Application.Run "'MacroFile.xls'!MyMacro"
    Application.Workbooks("MacroFile.xls").Close savechanges:=False
    Next I
    End If
    End With
    End Sub

    Pleace the attached files into your C:\Temp Directory and experriment.




    This code and the attached files will do the job.

    Sub FileUpdate()
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Turn off alerts
    Application.DisplayAlerts = False
    'Use ThisWorkbbok save for current path or SaveAs with new path name
    ThisWorkbook.SaveAs ("C:\Temp\blah blah.xls")
    'Use File System Objects to copy saved file to network location and "True" to overwrite without prompt
    fso.CopyFile "C:\Temp\blah blah.xls", "P:\MARKETING\Temp\", True
    'Turn alerts back on
    Application.DisplayAlerts = True
    End Sub

    Good Hunting.


    Jack UK,

    Don't forget Andy Pope on charts he's a legend.



    I draged my code across to an example of the above.

    There are two file that need to be place into the same folder. I only know how to attach one file to a post at a time so one here and one below.

    The idea is that your users would have access to the General File to do their work.

    If a Manager or someone higher in the food chain than us Planktonites needed access to the master data that the general file linked to they could click on the Open Confidential File button > Enter the Secret password and bang the file loads and opens. When they have finished with the confidential file they can escape it or hide it with the close file, x or clicking on the Close Confidential File button. The file will automatically save and revert back to an addin so that anyone who tries to view it by loading from the explorer window will see nothing.

    Granted if they have some VBA skill they can force it to be viewable, but most general users wouldn't know this. You could also mess around with hidden and very hidden for the sheets in question so that even if they do manage to force it open by changing addin to false they then need to force the hidden settings for the sheets in question. The trick with security is to make it as difficult as possible and multilayering your security does this reasonably well.

    BTW the Secret password for the password prompt and the VBE viewing is Secret.

    Have fun


    Hi Insomniac,

    Sounds like fun ;) Just a few quick suggestions.

    Instead of having the secure sheets in the same workbook why not remove them to a seperate linked workbook?

    I have a similar requirement where only certain people are allowed to see some confidential sheets. So instead of lumping it all into one file I made a seperate one with the confidential sheets linked to the general one. The confidential one opens at the same time as the general one but it's properties are set as an addin, this means that it's not visible unless a user overrides the addin setting through the VBE. To this end I have a control (button) in the general file that when clicked prompts for a password, if the correct password is entered then it triggers code to change the confidential file from addin true to addin false.....hey presto the magic of it all !! When finished the manager just clicks a button in the confidential file and the reverse takes becomes an addin again with all the usual saves etc.

    Just some thoughts on an alternative approach, hope it gets your grey matter racing.


    Hi Toad and welcome.

    I can relate to your problem on where to get a good reference manual. Took me about 6 months to define what I needed.

    Essentially it comes down to what your into...accounting, engineering, education etc. Being engineering orientated I found manuals / books that related to engineering and Excel. Excel for Scientists and Engineers is on my wish list. But for starters it would pay to get something that is very broad and general in it's coverage. Most people who do anything with Excel have a copy of John Walkenbachs "Excel 2002 Power Programming With VBA". This is the first book that I bought and it is the only one I have needed so far. As someone pointed out earlier a good discussion board can fill the gaps and also allows you to assist other if you have the time and expertise.

    I would througherly recommend John's book to anyone starting out with VBA. In fact if you look at Daves list of books on this site I think he had a review of it.



    No too hard as long as you know how to use Visual Basic Code.

    You'll need to read between the lines as I don't have time to customise a solution for you. Let me know if your stuck and I'll see what I can do.

    Essentially we are looking at what sort of trendline it is and then extracting the equation. We then use a substitute function to replace the non recognised characters with worksheet rec ones and throw the equation into the worksheet.

    Simple huh ?


    The attached example will help.

    At work I'm using WIN 98 & XL97SR2.

    At home I'm using WIN XP & XL97SR2.

    These combinations fairly rock and do everything the above average user / developer could want.

    Even if your getting into advanced stuff there is always an alternative work around to some to the sleek geek tools in the later versions.

    Another great advantage of using 98/97 or XP/97 is forward compatability with later versions of Win and XL. Hard to allways be sure of Multi-platform compatabiity with XL2000 or XLXP ?

    Well that's my two cents worth.


    Hi XL-Dennis,

    I agree good call to get ride of the posts if the attachments are commercial.

    I was not aware of this as these we given to me by another user. I assumed they were freely distributable and of limited value unless you have the proprietry software installed, anyway sorry for the inconvenience caused.

    Thanks for your througherness, good job.


    Dave H.

    Give this guy a medal :bsmile: He's a great moderator.

    Thanks XL-Dennis,

    I think I'll have to stick with my existing method for now rather than use an imported fix. I have about 300 users on XL97 and to try updating them all against our IT departments wishes would cause me a major problem.



    Thanks Jack UK & XL-Dennis for the responses.

    I found an adding from Adobe that I assume is written in VB/VBA that makes use of Distiller or Writer. I'm not sure if I'm allowed to attach it, but I guess unless you have either of these installed the addin is useless to you.

    XL-Dennis does your code require the installation of Adobe Acrobat Distiller or Writer? I'd be interested in seeing if it can be achieved without using these tools?? I've also read that you can use ghostscript somehow to do the same and that every machine has this installed?

    Any code or leads on which would be the best approach would be appreciated.

    Thanks Guys


    Hi All,

    I currently use a third party utility to capture my Excel Print output to a *.PDF format and it works OK.

    I was wondering if anyone has tried to write some VB or VBA code to do this directly from Excel ?

    I'm guessing you would probably need to hook into Acrobat distiller or PDF Writer somehow, has anyone tried this ?




    I downloaded your calendar and think it's great but when you click on the month dropdown it only shows January as an option ??

    Have'nt had time to look atthe code but you may already be aware of the problem and fixed it. If so could you please repost the file.

    Thanks and great work !


    The attached assumes that you have the MSCal.ocx control on your machine.

    This is normally found in: C:\WINDOWS\SYSTEM\MSCAL.OCX
    And is a common control on most Windows machines from 98 on. If it's missing you could ship it with your addin as a self extracting zip.exe

    I have removed the VBA project password so that you can create a reference to the MSCAL.ocx control.

    This is done in the Visual Basic Editor window. Menu = Tools > References.

    This should bring up the References selection form which will tell you if the control is installed or missing.



    Hi XL-Dennis,

    Thanks for the response.

    I'm running Windows 98 with office 97 thus Excel 97. Don't have a lot of the fancy new stuff in later versions :(

    Do you know the path or specific *.ocx file name for the WEB office component Spreadsheet ? I'm assuming it's an OCX type control ?

    I'm currently using images in the forms I have. The idea is that when a user clicks on one of the values it will return that value to the location of choice in their worksheet. The problem is that the image is not autoupdating if the master data changes. The other problem is that I have had to overlay a squillion lables that represent the displayed values and have code attached to return the displayed value. This is an absolute pain to maintain and it also uses a heck of a lot of memory with all the images and controls.

    What I'd really like is to have a control that displays the worksheet information in a grid format and is dynamically linked to the source data. I could then use some selection event to recognise the clicked value and return it.

    Anyone with any ideas ?


    Sounds OK in theory but do you think I can figure it out ?? Not !