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]

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.

 

[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.

 

[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