Can a macro open another worksheet?

[Art MacNeil]

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]

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

[Art MacNeil]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[David McRitchie]

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" <[email protected]> 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,

[Dave Peterson]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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