Can a macro open another worksheet?

Hello,

   I've got a macro that I need to run on about 200 spreadsheets.  Is there 
a way to get it to select and open all 200 spreadsheets?

Thanks,

Art. 


0
artmacneil (88)
11/19/2005 9:04:21 PM
excel 39879 articles. 2 followers. Follow

4 Replies
425 Views

Similar Articles

[PageSpeed] 39

Are they all in the same folder?

In fact, do you need to open all the workbooks in a folder?

Or all the workbooks in multiple known folders?

If you have files in one folder--but you want to choose (click on the first and
ctrl-click on subsequent in the file|open dialog), you can do something like:

Option Explicit
Sub testme()

    Dim myFileNames As Variant
    Dim iCtr As Long
    Dim wkbk As Workbook
    
    myFileNames = Application.GetOpenFilename _
                     ("Excel Files,*.xls", MultiSelect:=True)
    
    If IsArray(myFileNames) = False Then
        Exit Sub 'user hit cancel
    End If
    
    For iCtr = LBound(myFileNames) To UBound(myFileNames)
        Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
        'do something with that workbook
        wkbk.close savechanges:=false 'or true???
    Next iCtr
End Sub


Art MacNeil wrote:
> 
> Hello,
> 
>    I've got a macro that I need to run on about 200 spreadsheets.  Is there
> a way to get it to select and open all 200 spreadsheets?
> 
> Thanks,
> 
> Art.

-- 

Dave Peterson
0
petersod (12005)
11/19/2005 9:16:36 PM
Hello Dave, Thanks for the quick response.

> Are they all in the same folder?

  The spreadsheets are in individual folders. However, I can very quickly 
copy them to a single folder for the purposes of this exercise.


> In fact, do you need to open all the workbooks in a folder?
  Not sure if I have to or not.  If I can read the data I want without 
opening all 200 spreadsheets, that I'd like to do that.

> Or all the workbooks in multiple known folders?
   Yes.  In fact the data I want is the same tab name in all 200 
spreadsheets.

I tired your code below and it works perfectly.  Thanks.  I couldn't get it 
to work with my existing Macro though.

Thanks again,

Art.


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:437F9634.71A22D1B@verizonXSPAM.net...
> Are they all in the same folder?
>
> In fact, do you need to open all the workbooks in a folder?
>
> Or all the workbooks in multiple known folders?
>
> If you have files in one folder--but you want to choose (click on the 
> first and
> ctrl-click on subsequent in the file|open dialog), you can do something 
> like:
>
> Option Explicit
> Sub testme()
>
>    Dim myFileNames As Variant
>    Dim iCtr As Long
>    Dim wkbk As Workbook
>
>    myFileNames = Application.GetOpenFilename _
>                     ("Excel Files,*.xls", MultiSelect:=True)
>
>    If IsArray(myFileNames) = False Then
>        Exit Sub 'user hit cancel
>    End If
>
>    For iCtr = LBound(myFileNames) To UBound(myFileNames)
>        Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
>        'do something with that workbook
>        wkbk.close savechanges:=false 'or true???
>    Next iCtr
> End Sub
>
>


> Art MacNeil wrote:
>>
>> Hello,
>>
>>    I've got a macro that I need to run on about 200 spreadsheets.  Is 
>> there
>> a way to get it to select and open all 200 spreadsheets?
>>
>> Thanks,
>>
>> Art.
>
> -- 
>
> Dave Peterson 


0
artmacneil (88)
11/19/2005 9:48:18 PM
Hi Art (and Dave P.),
More power to Dave,  when you said worksheets in the subject
and spreadsheets in the body, I certainly thought you meant
worksheets all in the same workbook -- guess it was that word Open
that Dave picked up on.   Spreadsheet is a rather ambiguous term
when used with Excel because it does not differentiate between a
workbook, and a worksheet.

"Art MacNeil" <artmacneil@shaw.ca> wrote ...
> Hello Dave, Thanks for the quick response.
>
> I tired your code below and it works perfectly.  Thanks.  I couldn't get it
> to work with my existing Macro though.
>
> Thanks again,


0
11/19/2005 10:08:35 PM
If you had a list of the complete path (drive\folder\filename.xls), the
worksheet name that holds the cell that you want retrieved and the address of
the cell(s) you want, you could build a formula (or have a macro build bunch of
formulas that get that value.

The formula would look something like:
='C:\My Documents\excel\[book2.xls]sheet99'!$x$22




Art MacNeil wrote:
> 
> Hello Dave, Thanks for the quick response.
> 
> > Are they all in the same folder?
> 
>   The spreadsheets are in individual folders. However, I can very quickly
> copy them to a single folder for the purposes of this exercise.
> 
> > In fact, do you need to open all the workbooks in a folder?
>   Not sure if I have to or not.  If I can read the data I want without
> opening all 200 spreadsheets, that I'd like to do that.
> 
> > Or all the workbooks in multiple known folders?
>    Yes.  In fact the data I want is the same tab name in all 200
> spreadsheets.
> 
> I tired your code below and it works perfectly.  Thanks.  I couldn't get it
> to work with my existing Macro though.
> 
> Thanks again,
> 
> Art.
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:437F9634.71A22D1B@verizonXSPAM.net...
> > Are they all in the same folder?
> >
> > In fact, do you need to open all the workbooks in a folder?
> >
> > Or all the workbooks in multiple known folders?
> >
> > If you have files in one folder--but you want to choose (click on the
> > first and
> > ctrl-click on subsequent in the file|open dialog), you can do something
> > like:
> >
> > Option Explicit
> > Sub testme()
> >
> >    Dim myFileNames As Variant
> >    Dim iCtr As Long
> >    Dim wkbk As Workbook
> >
> >    myFileNames = Application.GetOpenFilename _
> >                     ("Excel Files,*.xls", MultiSelect:=True)
> >
> >    If IsArray(myFileNames) = False Then
> >        Exit Sub 'user hit cancel
> >    End If
> >
> >    For iCtr = LBound(myFileNames) To UBound(myFileNames)
> >        Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
> >        'do something with that workbook
> >        wkbk.close savechanges:=false 'or true???
> >    Next iCtr
> > End Sub
> >
> >
> 
> > Art MacNeil wrote:
> >>
> >> Hello,
> >>
> >>    I've got a macro that I need to run on about 200 spreadsheets.  Is
> >> there
> >> a way to get it to select and open all 200 spreadsheets?
> >>
> >> Thanks,
> >>
> >> Art.
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
11/19/2005 10:21:52 PM
Reply:

Similar Artilces:

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

Trial Media to Open License.
Well, i just found out that open media was not ordered for a job we are doing now. So i have a question. I have read on a few different sites that you can download SBS2008 PREM. Trial and enter your open license key later to convert to a running server. I called MS and they could not confirm or deny that it works. They can't find anything that says it will not work, but in the same time they can not find anything that it will work. Any First Hand experience doing this? If it does work, any gotchas? NOTE: I did order the media, it just will not be here in time. AND th...

Macros at POS
How far can you take the macro feature? If for example i want to discount an item by a set amount or by a percentage for customers who have a voucher, could i set up a macro which would find and discount an item in a transaction? or is this asking too much? Yes, that's too much for a macro. You can do it with a COM add-in ,or have it done or look into an existing discount or promotion add-in. Macros can only emulate keystrokes or mouse clicks - there is no way to include IF..THEN logic. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------...

database takes too long to open
Hi, I have a customer that has a problem with his exchange server, the outlook users takes longer than usual to start opening their mailboxes, and when I open the mailbox store, it also takes longer than usual (about a minute or 2). I recently found out that the ExchangeAL was not finding a DC, becuase it was incorrecly configured on the RUS, I fixed that, I also made an offline defragmentation of the database and checked its integrity using isinteg, and everything is fine. I don´t know what maybe wrong with it, but it´s making the users upset that the outlooks takes so long t...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Can't send e-mails from Outlook 2003 after SP1
After installing service pack SP1 for Office 2003, I can't send e-mails anymore. They are stuck in the outbox folder. The error message states that the connection to the server was interrupted. I did not change any settings and checking them confirms that everything is as it should be. Did anyone else experience the same issue and how did you resolve it? Thanks for your help Matthias See if you can manually move those messages from your Outbox and then check all your settings again. "Mattliusa" <anonymous@discussions.microsoft.com> wrote in message news:cb7401c48a5...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Can Line Chart Overlaps be Highlighted?
Can the overlap portion(s) of two lines in a chart be shown in a different color, without having to manually draw a shape? I would like to show the areas where Line A is above Line B as red, and the areas where it crosses below in blue - is it possible to do this without manually drawing in shapes? Hi, To some extent it depends how many times the lines cross. If it's just the once then you can use area charts to provide the shading. http://www.andypope.info/ngs/ng21.htm http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=590 This may help if the used one line as a base line...

AFX_MANAGE_STATE() macro disturbs the startup position of the ATL dialog
Hi All, I have a MFC dialog based application and a COM component developed using ATL with MFC support. The COM component exposes an interface ISum that has a method called Add(). The Add() method displays an ATL dialog that accepts numbers from the user on which addition is to be performed and displays the result as well. The contents of the Add() method are as given below: STDMETHODIMP CSum::Add() { CAcceptInput dlg; dlg.DoModal(); return S_OK; } On click of one of its buttons, the MFC dialog application, instantiates the COM component and invokes the ISu...

Excel 2000/Change Default Opening Location?
I'm using Excel 2000. When I open Excel, and the click on the Open button to open worksheet, it goes to a default location I'd like to change. How can I change that default location? I have searched under Tools > Options, but find no means there. Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preferences. Tools|Options|General Tab change the entry in the Default File Location box CWLee wrote: > > I'm using Excel 2000. When I open...

Users asked to enter Username / Password /Domain when opening outl
Just this morning, our users are being asked for their username / domain and password. When we eneter it in, the box reappearas after we eneter it again we get and error that the information store could not be opened. We are running Exchanage 5.5 with Windows 2000. Any help would be great. Thanks in advance =?Utf-8?B?SmVmZg==?= <Jeff@discussions.microsoft.com> wrote in news:DD28037B-B6C8-4575-B0CC-F80C14991716@microsoft.com: > Just this morning, our users are being asked for their username / > domain and password. When we eneter it in, the box reappearas after we > eneter...

Open 2003 files with 2007
Hello all, I just upgraded my Office 2003 with Office 2007 on an EP Pro machine. The only snaffoo I find(so far..) is with Excel 2007. When I double-click on an Excel 2003 file, Excel 2007 starts up just fine, BUT my file is not displayed. At that point if I click the Restore button up at top-right, the file appears when Excel is restored up or down, then all is well. Anyone have any idea whats up with that and how to fix it??? Thanks John Lots of people have complained that when they doubleclick on a file in windows explorer, that the workbook doesn't seem to show up in xl20...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

run Microsoft Publisher 2002 macro in command line
This might look simple but I cannot figure it out. How can you run a Microsoft Publisher 2002 macro in command line? (like in Word, you can do winword file.doc /mNAMEOFTHEMACRO ) In a command line I am not sure. Try posting at microsoft.public.publisher.programming Brian Kvalheim Microsoft Publisher MVP http://www.kvalheim.org >-----Original Message----- >This might look simple but I cannot figure it out. > >How can you run a Microsoft Publisher 2002 macro in command line? >(like in Word, you can do winword file.doc /mNAMEOFTHEMACRO ) >. > ...

Radio Button on opening the form
Hello: I have know how to open the form using command button, but now I want to use Radio Option button to open the form consists of: 1. Show all supplier invoices 2. Show only outstanding invoices 3. Show only paid invoices I have created 3 types of Form using 3 types of query, and now I want to open it by using radio button with the button OK and cancel to open the form. Is there any website providing the sample of Radio button to opent the form. I want to studdy how to write the VBA for that. Thanks in advance. Frank You sure you want the form to open when the radio button is sel...

How to transfer Custom Views to another machine
Outlook 2003 How do you transfer Custom Views to another machine (no exchange server)? Thank you for your help. They are contained within your pst-file so if you transfer that you'll transfer the views too. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more ----- "John" <no.email@no.email.com> wrote in message news:usDhjJ7hHHA.4132@TK2MSFTNGP05.phx.gbl... > Outlook 2003 > > How do you transfer Custom Views to another machine (no exchange server)? >...

how do I open excel workbook If i forget my workbook password
I made programm with a lot of formuals so I've put password on the workbook, because I did not wanted to earase formulas accidently,but now I can't open it because I forget the password. Thanks for help Hi search Google for 'Excel password remover' -- Regards Frank Kabel Frankfurt, Germany "av8rart" <av8rart@discussions.microsoft.com> schrieb im Newsbeitrag news:D2443918-FEF4-41E6-92C4-9B19A293778A@microsoft.com... > I made programm with a lot of formuals so I've put password on the workbook, >...

Can't sign in to 2004?
Now, another problem. I can no longer sign in with my hotmail and password if "use Money's online features that require a passport" even though I am signed on to MSN with that passport. I tried resetting the hotmail password for both MSN and Money 2004 but still can't get it to work. In microsoft.public.money, MrEKJ wrote: >Now, another problem. I can no longer sign in with my hotmail and password >if "use Money's online features that require a passport" even though I am >signed on to MSN with that passport. I tried resetting the hotmail password &...

keeping page, insert etc dropdowns open & having page below it
how can i keep the drop downs open & have the page below it. It was that way & I must have hit the wrong button or something.microsoft word 2007 "billy" <billy@discussions.microsoft.com> wrote in message news:91198FA1-A43C-4A00-9141-FB019528AC5B@microsoft.com... > how can i keep the drop downs open & have the page below it. It was that > way > & I must have hit the wrong button or something.microsoft word 2007 Ask in a discussion group for Word. Here's how to find it. http://www.microsoft.com/communities/newsgroups/en-us/ In the...

How do I open or cancel "previous appointment"?
Using 2007 version, small vertical bar running down lhs side of calendar (just to right of times) for selected date, inside bar is written "previous appointment" , cannot open or delete, cannot insert new appointment in that time frame...anybody else hit this wall? Cannot be an appointment since never used this %$#(&^ programme before. Tnx r2p2 "r2p2" <r2p2@discussions.microsoft.com> wrote in message news:E0E9D6ED-09A3-4235-8B14-652FF43F430A@microsoft.com... > Using 2007 version, small vertical bar running down lhs side of calendar > (just...

AutoFilter on Protected Worksheet
I need to protect a worksheet to prevent users from editting certai columns. It's a log, so I would like to allow them to use AutoFilter t sort the log. So when I protect the worksheet, I check the box tha says, "Allow all users of this worksheet to: Use AutoFilters". Seems pretty straightforward, but when I then go to Data > Filter th AutoFilter selection is grayed out. What am I missing? -- Message posted from http://www.ExcelForum.com That setting will allow users to apply filters to a table where the AutoFilter is already turned on. However, they won't be able to...

Access violation in CDaoQueryDef::Open or CSimpleStringT::GetLength(). VC++ 6.0 app compiled in Visual C++ .NET
Hi, I have a problem using DAO in Visual C++ .net Standard edition. We have an old project created in Visual C++ 6.0 Professional. The projects compiles, links ok (with a lot of warnings telling the DAO-classes are deprecated). The compiled program executes ok until I am about to access the database. Some accesses goes ok, but some are not. It is always the same databse accesses that the program crashes on. The database that I'm using is Access 97. I also have tried converting it to 2000 and 2002. In this function the crash occurs in daocore.cpp: void CDaoQueryDef::Open(LPCTSTR lpszName...