Activate a macro within a folder

Hello,

I have about 20 workbooks each containing a macro called "ClearBalances" 
within a folder called "Schedules"  The macro from each workbook clears the 
quarter ending balances activated by a command button.  What I want to do is 
to use the "ClearBalances" macro and clear out the balances without having 
to open the workbook individually.  Is there a way to activate the 
"ClearBlances" command button by using a seprate Excel sheet and press a 
command button that will activate the "ClearBalance" macro.without having to 
open all the worksbooks individually. Any suggestions or visit a website 
will be appreciated.  Thank you in advance. 

0
8/6/2007 5:29:43 AM
excel 39879 articles. 2 followers. Follow

2 Replies
292 Views

Similar Articles

[PageSpeed] 24

I think you'll have to open the workbooks--but you can do that work in a macro.

Maybe something like this untested, but compiled code:

Option Explicit
Sub testme01()
   
    Dim myNames() As String
    Dim fCtr As Long
    Dim myFile As String
    Dim myPath As String
    Dim TempWkbk As Workbook
    
    'change the folder here
    myPath = "C:\my documents\excel\test\Schedules"
    If myPath = "" Then Exit Sub
    If Right(myPath, 1) <> "\" Then
        myPath = myPath & "\"
    End If
    
    myFile = ""
    On Error Resume Next
    myFile = Dir(myPath & "*.xls")
    On Error GoTo 0
    If myFile = "" Then
        MsgBox "no files found"
        Exit Sub
    End If
    
    'get the list of files
    fCtr = 0
    Do While myFile <> ""
        fCtr = fCtr + 1
        ReDim Preserve myNames(1 To fCtr)
        myNames(fCtr) = myFile
        myFile = Dir()
    Loop

    If fCtr > 0 Then
        For fCtr = LBound(myNames) To UBound(myNames)
            Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
            Application.Run "'" & TempWkbk.Name & "'!ClearBalances"
            TempWkbk.Save
            TempWkbk.Close savechanges:=False
        Next fCtr
    End If
  
End Sub

CAM wrote:
> 
> Hello,
> 
> I have about 20 workbooks each containing a macro called "ClearBalances"
> within a folder called "Schedules"  The macro from each workbook clears the
> quarter ending balances activated by a command button.  What I want to do is
> to use the "ClearBalances" macro and clear out the balances without having
> to open the workbook individually.  Is there a way to activate the
> "ClearBlances" command button by using a seprate Excel sheet and press a
> command button that will activate the "ClearBalance" macro.without having to
> open all the worksbooks individually. Any suggestions or visit a website
> will be appreciated.  Thank you in advance.

-- 

Dave Peterson
0
petersod (12005)
8/6/2007 11:29:36 AM
Thanks Dave,
I will give it a try.

Cheers

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:46B70620.7E201746@verizonXSPAM.net...
>I think you'll have to open the workbooks--but you can do that work in a 
>macro.
>
> Maybe something like this untested, but compiled code:
>
> Option Explicit
> Sub testme01()
>
>    Dim myNames() As String
>    Dim fCtr As Long
>    Dim myFile As String
>    Dim myPath As String
>    Dim TempWkbk As Workbook
>
>    'change the folder here
>    myPath = "C:\my documents\excel\test\Schedules"
>    If myPath = "" Then Exit Sub
>    If Right(myPath, 1) <> "\" Then
>        myPath = myPath & "\"
>    End If
>
>    myFile = ""
>    On Error Resume Next
>    myFile = Dir(myPath & "*.xls")
>    On Error GoTo 0
>    If myFile = "" Then
>        MsgBox "no files found"
>        Exit Sub
>    End If
>
>    'get the list of files
>    fCtr = 0
>    Do While myFile <> ""
>        fCtr = fCtr + 1
>        ReDim Preserve myNames(1 To fCtr)
>        myNames(fCtr) = myFile
>        myFile = Dir()
>    Loop
>
>    If fCtr > 0 Then
>        For fCtr = LBound(myNames) To UBound(myNames)
>            Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
>            Application.Run "'" & TempWkbk.Name & "'!ClearBalances"
>            TempWkbk.Save
>            TempWkbk.Close savechanges:=False
>        Next fCtr
>    End If
>
> End Sub
>
> CAM wrote:
>>
>> Hello,
>>
>> I have about 20 workbooks each containing a macro called "ClearBalances"
>> within a folder called "Schedules"  The macro from each workbook clears 
>> the
>> quarter ending balances activated by a command button.  What I want to do 
>> is
>> to use the "ClearBalances" macro and clear out the balances without 
>> having
>> to open the workbook individually.  Is there a way to activate the
>> "ClearBlances" command button by using a seprate Excel sheet and press a
>> command button that will activate the "ClearBalance" macro.without having 
>> to
>> open all the worksbooks individually. Any suggestions or visit a website
>> will be appreciated.  Thank you in advance.
>
> -- 
>
> Dave Peterson 


0
8/7/2007 12:35:53 AM
Reply:

Similar Artilces:

macro to continue running if error
Hi, I have the following macro that is working fine. It is looking at a specified worksheet array and executing the macro "Filter" on each worksheets. However, in case one of the worksheet is not existing, I would like the macro to pass the non existing worksheet and continue running on the next available worksheet. I tried adding the following statement: "On Error Resume Next" at the beginning but it is passing everything. Any help would be appreciated. Thanks Sub UpdateAll() For Each sh In Worksheets(Array("1", "2", "3", ...

For Follow Up folder emails not showing
Hi - for whatever reason, my 'For Follow-up' folder is showing the numeric number of emails I've flagged to follow-up. However, when I click on the folder, no emails are shown. Has anyone encounted this problem before? Please help. What view do you have set on the folder? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Daniel W asked: | Hi - for what...

Outlook 2000 Folder Issue
Forgive my earlier post, I haven't gotten the hang of Google Groups. In Outlook 2000 my folders no longer expand to show new/unread mail. I seem unable to find the option or control to set it so the folders expand and go bold whenever they have new or unread mail in them. I have a fair number of folders and sub-folders so going looking for new mail is a pain. Can anyone help? Thanks, Dave What mode of OL2000 are you using? Check the second line here for your mode: Help menu > About I seem to vaguely remember that one mode showed the auto-expanding folders, the other didn't. ...

Archive/Download Public Folder
We have a public folder that contains all the messages that went through the server. At the moment we are manualy copying the content once in a while with outlook and move them to outlook folder. Then export to pst file. But when it comes to the hunderds thousands of emails it is desparately slow. Is there other comon way to download/export messages/posts from public folder to pst file. Thank you, Vladimir There is nothing that says you must drag them to a mailbox first. Just make sure that you have the pst file mounted and drag and drop the contents from the PF directly to the PST f...

Password protect a document or folder
How can I set up a password to limit access to a document or folder in Excel (or Word, etc.) I am running Windows XP Home. It was easy to do in Win98, but can't seem to figure it out in XP. Bill Any Excel or Word file can be given "password to open" attributes using(XL 2002) File>Save As>Tools>General Options. Folders are more complex and require Windows XP settings. The folder needs to be under your local profile, and you need to be using NTFS to prevent access to others. Read: HOW TO: Set, View, Change, or Remove File and Folder Permissions [Q308418] http://sup...

public folders vs newsgroups
any good links out there describing the pro's and cons of each? what features does one have that the other hasn't? Are they comparable? On Thu, 28 Oct 2004 11:34:36 -0400, "wehrgasm" <no1@hotmail.com> wrote: >any good links out there describing the pro's and cons of each? what >features does one have that the other hasn't? > ...

Archive folders are empty
Hi there, I tried archiving one folder in my Inbox. It got saved into a .pst file. When I click File | Open the pst file, I see a folder called "Archive Folder" created in by folder tree. Assuming this is the archived folder, I open it but find it totally empty. I have saved about 2 months of emails into the archive, so by right it really should not be empty. Any advise anybody please? How big is the archive.pst file? Do you have more than one? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus na...

Macro running problem
When I run a macro it stops at the following point : ActiveCell.FormulaR1C1 = _ "=ISNA(MATCH(ControlCentre!RC3,Purchases!R24C65:R2000C65,0))L"""" INDEX(Purchases!R24C[17]:R2000C[17],MATCH(ControlCentre!RC3,Purchases!R24C65 :R2000C65,0))=""UpdateCC"" INDEX(Purchases!R24C42:R2000C42,MATCH(ControlCentre!RC3,Purchases!R24C65:R20 00C65,0))?"""" " Here is the leading and ending portion of the macro: Range("H77").Select ActiveCell.FormulaR1C1 = _ "=ISNA(MATCH(ControlCentre!RC3,Purcha...

Need a macro to copy cells
I have numbers spread thruout col A, with many blank cells in between the data. As time goes on I will add numbers in col B. In most cases there could be a number in col B where there is none in col A(adjacent cell) I would like to have a macro copy the numbers from col B to col A. If there already is a number in col A I would like the number in col a to be added to the number in col B Thanks Need to confirm a couple of thing. "If there already is a number in col A I would like the number in col a to be added to the number in col B". Do you mean if 5 exists in col A and you in...

Macro to open text files and copy their contents.
Hi all, Is there a way for a Macro to open a text file, then copy it's contents to a spreadsheet and name the tab so it matches the name of the text file? Then repeat this for 200+ text files in the same folder? I thought I saw a solution here a while ago but I couldn't find it. Thanks, Art Here is my attempt. Note: The help file for the FileSystemObject says that "readall" wastes memory resources on large files. There must be sufficient blank sheets in the workbook. The text added to the worksheet includes some of the line feed characters. (using Dana DeL...

Enter key functions differently after different macro behavior
I posted this last week in the general forum and didn't hear anything. Since, I believe, it has something to do with the macro, i thought I might have better luck here in the programming forum. This is more of an annoyace than a true issue but i would appreciate any help. I'm running Excel 2007. I'm using a spreadsheet to keep scores for quiz matches. Across on row 6 I have the question numbers. Immediately below that is a spot for me to enter the point value. Both of these cells span two columns (i.e. column 6 and 7 for one question, 8 and 9 for the next, et...

Opening other users folders
I have an exchange 2000 server that I am working on. A user went through outlook and chose the file menu, then open, and then other users folder. They then selected the head cheese of the company and they could see all of their e-mail messages. I just came in after a guy, who was fired, and I figure he may have done something to the security. The mailboxes all have the everyone group listed with full permissions. I didn't want to touch it without consulting someone else first. Thanks in advance, Kevin That's not good. Someone has fiddled with permissions because that is not ...

Outlook 2007 activities lost
None of my activities are showing up on my contacts. Had worked before. How do I get them to show back up? What did you do to lose them? Outlook doesn't do it on it's own, so there's more to your story. You did something to disconnect your Contacts from the MAPI addressing service. Moving, exporting or importing your data are the most common ways to cause this problem. -- Russ Valentine "Edwin" <Edwin@discussions.microsoft.com> wrote in message news:3BA5F18E-1519-4C41-8E07-D65652D41777@microsoft.com... > None of my activities are showing up on my...

Active Directory Connector #3
If I go to Active Directroy Sites and Services --Default-first-Site --Servers --Mail exchagne server I have a item there called Exchange Settings in it I have Active Directory Connector Is this suposded to me there? I did have Exchange 5.5 that since has been removed. It's from the Active Directory connector. I haven't seen anything that says it would cause problems to delete it, but likewise it shouldn't cause any problems if you leave it there. If you do decide to delete it, then make sure that you have a backup of Active Directory (System State on your DC's) so ...

Include a value from one cell within text of another cell.
In my spreadsheet, users are prompted to enter a value into cell B4. Multiple peices of information are then returned based on what they entered, But certain numbers are not valid (for instance 25 through 150). If an invalid value is entered, I would like cell B5 to return something like: " X is not a valid entry. Please enter the correct pair number." I would like the cell to actually reference B4 so the number they typed will appear where the "X" is. The only way I know how to do this would be to make B5 equal another cell (lets say Z1). Z1 would then con...

Mail Enabled Public Folder Hidden Email address
Hi, I am running SBS 2003 w Exchange. We create public folders on the fly for different projects, and then we want to copy emails to them. By default however, the address is Hidden from Exchange Address Lists, and we can't email to the folder, until I go into ESM and change the properties, and clear that checkbox. Is there a way to change the default so the address is not hidden, and we can email to the folder by default? Thanks Willy On Wed, 14 Feb 2007 13:08:20 -0800, WillyinPhoenix <WillyinPhoenix@discussions.microsoft.com> wrote: >Hi, I am running SBS 2003 w Exchan...

Run a macro on a fixed date cycle
Hi Is there a way to run a macro - "Update_Me" - on a fixed date, say the first of every month? Thanks in advance Steve Steve, There's no way to do waht you want without opening the workbook on that day. If we assum you open the workbook everyday, the ideal way to achieve ehat you want is to create ans 'Auto_exec' macro that performs a check on the date, and then either exits doing nothing, or runs your update macro, depending on the result of the date check. Depending how volatile your source data is you may need to add some validation and safety checks to en...

macro to include email signature
I have attached the following code to a button on the quick access bar in Outlook. It does all I want except that "body text" erases my default signature. Is there some additional code I could include that would complete the email with my default signature? The default signature uses various fonts sizes and colours. Thanks for any help. Sub inspector() Dim myOlApp As New Outlook.Application Dim myItem As Object Dim myAttachments As Outlook.Attachments Set myItem = myOlApp.ActiveInspector.currentitem Set myAttachments = myItem.Attachments myItem.Subject = "Status...

full version activation
hello there, sorry if this is a silly question, but i am trying to install the full version of fp 2003 on my new pc, because my old pc crashed. i purchased fp about 5 years ago, but now it is saying that i have installed it on too many computers, which might be technically true, but i have always used it on only 1 pc at a time. is there anyway i can activate fp? i tried contacting microsoft by phone using the number provided in fp, but after going through the automated activation process, it says there is nobody available to assist me. anyways, was just wondering if i will ...

Find within a file as a function
Cell J22 generally contains the text "Project Name:" within a template. But people mess with their files and this is not always the case. Als it was not previously defined as Project Name (and since I can' expect everyone to download a new template, can't be). What I woul like to do is be able to have a seperate file search through th template to locate this cell and enter the information directly below. I'm also not allowed to use a macro here. If however a macro is th only way, I'll take any assistance you can provide. Thank you -- MACRE ---------------------...

Where are folders stored ? Sent ? Recieved?
Hard drive crashed and I have rebuilt. Old drive may have outlook folders intact; sent, recieved etc. Where does outlook store files in variouse versions. it would be a 2000 vintage of Outlook Express. Mark B wrote: > Hard drive crashed and I have rebuilt. > > Old drive may have outlook folders intact; sent, recieved > etc. > > Where does outlook store files in variouse versions. it > would be a 2000 vintage of Outlook Express. Wrong newsgroup! This newsgroup is for Outlook which is a PIM (personal information manager) with e-mail and a component of Microsoft Offic...

Remove macro warning
Hi - I have a spreadsheet that I take some raw data that I run a macro on to clean the data then (once the macro is complete) I send it on to managers in my department. I don't want to send the file with the macro held within it, so I remove it from the copy I send out. The thing is, even with the macro deleted, when people open the file, they still get a message offering them the choice to enable macros, even though there is no macro. Can anyone tell me how I go about removing the reference to the macro so that this message no longer appears? Thanks Neil Hi, Neil Perry Delete ...

ObjectName in Activities
Hi! I am using the example in the MS CRM SDK help file to create new activities. It works like a charm, with one exception (of course). The field ObjectName does not get written. This leads to the Regarding box to be open when the Activity is opened. It works in the sence that my Activity is in fact linked to, in this case, an order which would indicate that I am doing the <activityparties> stuff correctly. The attribute ObjectName on the Activity is read-only so I can't set it either. Does anyone know what I am doing wrong? Gratefully !Robert The code: string strXml = &qu...

how do I tansfer my incredimail folders and emails to outlook?
I chose to move from incredimail to outlook and want my mail transfered as well. Does anyone knw how? In news:61EAC482-5694-4241-8068-EAD7C07099BB@microsoft.com, P Block <P Block@discussions.microsoft.com> typed: > I chose to move from incredimail to outlook and want my mail > transfered as well. Does anyone knw how? Check Incredimail support to see what formats you can transfer/export to - or google. "P Block" <P Block@discussions.microsoft.com> wrote in message news:61EAC482-5694-4241-8068-EAD7C07099BB@microsoft.com... >I chose to move from incredi...

Macros do not appear in Macro dialog box
I create a macro by choosing Tools > Macro > Macros, entering a name, and clicking Create. In the editor I write the code, then return to Publisher and save the file. But when I choose Tools > Macro > Macros again, the created macro does not appear. Any suggestions on what to do? Thanks gwasson <gwasson@discussions.microsoft.com> was very recently heard to utter: > I create a macro by choosing Tools > Macro > Macros, entering a name, > and clicking Create. In the editor I write the code, then return to > Publisher and save the file. But when I choose Tools...