Prev: DTPicker controls missing from userform
Next: launch instance of excel without loading personal.xls
From: JerseyInDC on 20 May 2010 15:42
I need to create a summary report that uses various formulas (count, sum,
datedif etc) that pull data from over 900 workbooks located in a single
folder. All of the workbooks are formatted exactly the same with the same
variables. I want this summary report to have each row represent one
workbook with the columns being each new calculation that I run. I know
nothing of macros and have been thrown into this. Perhaps giving an example
with SUM formula may be a good place to start.
From: Barb Reinhardt on 20 May 2010 16:03
This should get you started.

Option Explicit

Sub CreateSummaryWorkbook()

Dim myFolder As String
Dim myFile As String
Dim myWB As Excel.Workbook
Dim aWB As Excel.Workbook
Dim aWS As Excel.Worksheet

myFolder = "C:/" 'Change to your folder name

Set aWB = Workbooks.Add
Set aWS = aWB.Worksheets(1)
aWS.Name = "Summary"


If Right(myFolder, 1) <> "/" Then
myFolder = myFolder & "/"
End If

myFile = Dir(myFolder & "*.x*")

If myFile = "" Then Exit Sub

Do
Set myWB = Workbooks.Open(myFolder & myFile, UpdateLinks:=False,
ReadOnly:=True)

'Do all of your summary work here

'I'll leave this for someone else to help with. :)

'Close workbook
myWB.Close savechanges:=False

myFile = Dir

Loop While myFile <> ""

End Sub

--
HTH,

Barb Reinhardt



"JerseyInDC" wrote:

> I need to create a summary report that uses various formulas (count, sum,
> datedif etc) that pull data from over 900 workbooks located in a single
> folder. All of the workbooks are formatted exactly the same with the same
> variables. I want this summary report to have each row represent one
> workbook with the columns being each new calculation that I run. I know
> nothing of macros and have been thrown into this. Perhaps giving an example
> with SUM formula may be a good place to start.
From: Barb Reinhardt on 20 May 2010 16:04
Minor tweaks. Try this (after changing the folder name) and see what you've
got. It doesn't do any summaries yet, but does give a list of the workbooks
in the folder.

Option Explicit

Sub CreateSummaryWorkbook()

Dim myFolder As String
Dim myFile As String
Dim myWB As Excel.Workbook
Dim aWB As Excel.Workbook
Dim aWS As Excel.Worksheet
Dim lRow As Long

myFolder = "C:/" 'Change to your folder name

Set aWB = Workbooks.Add
Set aWS = aWB.Worksheets(1)
aWS.Name = "Summary"
aWS.Cells(1, 1).Value = "Workbook Name"

If Right(myFolder, 1) <> "/" Then
myFolder = myFolder & "/"
End If

myFile = Dir(myFolder & "*.x*")

If myFile = "" Then Exit Sub
lRow = 1
Do
Set myWB = Workbooks.Open(myFolder & myFile, UpdateLinks:=False,
ReadOnly:=True)
lRow = lRow + 1
'Do all of your summary work here
aWS.Cells(lRow, 1) = myWB.Name

'Close workbook
myWB.Close savechanges:=False

myFile = Dir

Loop While myFile <> ""

End Sub

--
HTH,

Barb Reinhardt



"JerseyInDC" wrote:

> I need to create a summary report that uses various formulas (count, sum,
> datedif etc) that pull data from over 900 workbooks located in a single
> folder. All of the workbooks are formatted exactly the same with the same
> variables. I want this summary report to have each row represent one
> workbook with the columns being each new calculation that I run. I know
> nothing of macros and have been thrown into this. Perhaps giving an example
> with SUM formula may be a good place to start.
 | 
Pages: 1
Prev: DTPicker controls missing from userform
Next: launch instance of excel without loading personal.xls