Another Macro Problem! (dave where are you!)

  • Thread starter Thread starter John Kitchens
  • Start date Start date
J

John Kitchens

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
 
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
 
Back
Top