Why do all the other files open, too? Are you selecting them--if yes, stop it!
I think I'd tell the user to open only DS*.xls files. When you're prompted with
the file|open dialog, the user can type DS*.xls and just see those DS* files.
Then select what they want.
You could have your code ignore all the workbooks that don't start with DS:
Option Explicit
Sub GetSheets()
Dim i As Long
Dim varr As Variant
Dim wkbk As Workbook
Dim ws As Worksheet
Application.DisplayAlerts = False
varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _
MultiSelect:=True)
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
If LCase(varr(i)) Like "*\ds######.xls" Then
'do the work
Set wkbk = Workbooks.Open(varr(i))
Set ws = wkbk.Sheets("Summary")
'do more stuff
'(I'm not sure how you consolidate the data here)
wkbk.Close SaveChanges:=False
Else
'do nothing
End If
Next
End If
Application.DisplayAlerts = True
End Sub
I'm not quite sure what you're doing, but if you're copying each sheet at the
bottom of a "master consolidated" sheet, you could copy|paste special|Values
followed by a copy|paste special|formats.
Kind of like:
Option Explicit
Sub GetSheets()
Dim i As Long
Dim varr As Variant
Dim wkbk As Workbook
Dim ws As Worksheet
Dim DestCell As Range
Dim ConsWks As Worksheet
'add a new workbook for consolidating???
Set ConsWks = Workbooks.Add(1).Worksheets(1)
DestCell = ConsWks.Range("a1")
Application.DisplayAlerts = False
varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _
MultiSelect:=True)
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
If LCase(varr(i)) Like "*\ds######.xls" Then
'do the work
Set wkbk = Workbooks.Open(varr(i))
Set ws = wkbk.Sheets("summary")
With ws
.Range("a1", .UsedRange).Copy
End With
DestCell.PasteSpecial Paste:=xlPasteValues
DestCell.PasteSpecial Paste:=xlPasteFormats
With ConsWks
Set DestCell = .Range(.Cells.Count, "A").End(xlUp).Offset(1, 0)
End With
wkbk.Close SaveChanges:=False
Else
'do nothing
End If
Next
End If
Application.DisplayAlerts = True
End Sub
This does use Column A to determine the next available row (in the consolidated
worksheet).
Just a note--if you're getting all the DS*.xls files from the same folder, you
can make it so that the user doesn't need to interact:
Option Explicit
Sub testme02()
Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
'get the list of files
fCtr = 0
Do While myFile <> ""
If LCase(myFile) Like "ds######.xls" Then
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
End If
myFile = Dir()
Loop
If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
'this is where all the real work would go
MsgBox myPath & myFiles(fCtr)
Next fCtr
End If
End Sub
John Kitchens wrote:
>
> Okay,
>
> You guys have helped me in the past, and I need you again.
> I am using Excel 2000.
>
> I have a worksheet that I saved as a template. The user opens the template
> and clicks a button that executes the macro.
>
> When the button is pushed the open box appears and you select the files that
> you need and hit open and then it opens the files one after anpther.
>
> When it finishes opening the files (up to 250) I click on the first page of
> my workbook "Summary". These is where is all the information is summarized
> from all of the 250 or so files that are now open in the workbook.
>
> I save this new file as DS092204 for daily summary and the date. Same thing
> tommorrow DS092304.
>
> NOW MY QUESTION
>
> I would like to use this same form and modify the macro so that instead of
> opening all 250 individual files I could just open the DS files and create a
> weekly summary. I can use this macro now to open the DS files, but all of
> the files (up to 250) that were used to create the DS open with it. That is
> too much and it takes to long. I also get the message about linked data
> etc. that I have to say ok to for it to continue with its calculation.
>
> I am hoping to modify this macro so that it will only open sheet one or in
> my case "Summary" of the DS files and then compute based on that
> information. I am not sure if this can be done or not since the Summary
> sheet of the DS files are calculated by having the 250 files open.
>
> This is the macro that creates the daily summary:
>
> Sub GetSheets()
> Dim i As Long
> Dim varr As Variant
> Dim wkbk As Workbook
> Dim ws As Worksheet
> Application.DisplayAlerts = False
> varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _
> MultiSelect:=True)
> If IsArray(varr) Then
> For i = LBound(varr) To UBound(varr)
> Set wkbk = Workbooks.Open(varr(i))
> Set ws = wkbk.Sheets("Sheet1")
> ws.Unprotect
> ws.Name = Left(wkbk.Name, Len(wkbk.Name) - 4)
> ws.Cells.Style = "Normal"
> ws.DrawingObjects.Delete
> ws.Copy After:=ThisWorkbook.Worksheets("Start")
>
> wkbk.Close SaveChanges:=False
> Next
> End If
> Application.DisplayAlerts = True
> End Sub
>
> Sub UnhideSheets()
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Sheets
> ws.Visible = xlSheetVisible
> Next ws
> End Sub
>
> I am sorry this post is so long. Can someone please help?
>
> John Kitchens
--
Dave Peterson
(E-Mail Removed)
Similar Threads
