Hey Peeps,

I have a problem that i know can be easily solved if i
used MSAccess, but my manager won't spend any money.

I wish to import times from several time sheets into an
overall office timesheet. Each time sheet will have a
list of job numbers (e.g. X3456) with hours spent on each
listed next to it. I need to create a list of all the
different job numbers in the office timesheet with the
total number of hours spent on each by everyone in the
office.

I thought that a good way of doing it would be to get
everyone to save there timsheet in a certain folder.
Excel can then be told to import the info from certain
cells in each file in the folder. This will mean that as
more files are added excel will update??

I can program, but not very well in excel and this is past
me. Please can you help me :-)

If you need more info please don't hesitate to ask me, I
hope it makes sense?



Thanks in advance

Adam

Adam,

If all your worksheets are in C:\Excel\Time Sheets, the sub below will copy the values in cells A1 and B1 (from the default
worksheet) of all the files into a new workbook. You can modify the code to get as many values as you need - or post back for help
in modifying it, if this is too simplistic for your needs.

HTH,
Bernie
Excel MVP


Sub ConsolidateSameDataFromAllFiles()
Dim myBook As Workbook
Dim i As Integer

Set myBook = Workbooks.Add

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Time Sheets\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
myBook.Worksheets(1).Range("A65536").End(xlUp)(2). Value = _
ActiveWorkbook.Name
myBook.Worksheets(1).Range("B65536").End(xlUp)(2). Value = _
ActiveWorkbook.Worksheets(1).Range("A1").Value
myBook.Worksheets(1).Range("C65536").End(xlUp)(2). Value = _
ActiveWorkbook.Worksheets(1).Range("B1").Value
ActiveWorkbook.Close False
Next i
Else: MsgBox "There were no files found."
End If

End With

End Sub




"Adam Thickett" wrote in message ...
Hey Peeps,

I have a problem that i know can be easily solved if i
used MSAccess, but my manager won't spend any money.

I wish to import times from several time sheets into an
overall office timesheet. Each time sheet will have a
list of job numbers (e.g. X3456) with hours spent on each
listed next to it. I need to create a list of all the
different job numbers in the office timesheet with the
total number of hours spent on each by everyone in the
office.

I thought that a good way of doing it would be to get
everyone to save there timsheet in a certain folder.
Excel can then be told to import the info from certain
cells in each file in the folder. This will mean that as
more files are added excel will update??

I can program, but not very well in excel and this is past
me. Please can you help me :-)

If you need more info please don't hesitate to ask me, I
hope it makes sense?



Thanks in advance

Adam

Cheers Bernie,

That works great,

I need 2 modifications to make it perfect, again i've
tried and failed (i really must brush up on my VB)

1) The data must be fetched into the workbook the script
is run from (currently it opens a new workbook)

2) I need it to add the times for each job number and list
the totals for each job?

Hope you can help

Regards

Adam
-----Original Message-----
Adam,

If all your worksheets are in C:\Excel\Time Sheets, the
sub below will copy the values in cells A1 and B1 (from
the default
worksheet) of all the files into a new workbook. You can
modify the code to get as many values as you need - or
post back for help
in modifying it, if this is too simplistic for your needs.

HTH,
Bernie
Excel MVP


Sub ConsolidateSameDataFromAllFiles()
Dim myBook As Workbook
Dim i As Integer

Set myBook = Workbooks.Add

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Time Sheets\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
myBook.Worksheets(1).Range("A65536").End(xlUp)
(2).Value = _
ActiveWorkbook.Name
myBook.Worksheets(1).Range("B65536").End(xlUp)
(2).Value = _
ActiveWorkbook.Worksheets(1).Range
("A1").Value
myBook.Worksheets(1).Range("C65536").End(xlUp)
(2).Value = _
ActiveWorkbook.Worksheets(1).Range
("B1").Value
ActiveWorkbook.Close False
Next i
Else: MsgBox "There were no files found."
End If

End With

End Sub




"Adam Thickett" wrote in
message ...
Hey Peeps,

I have a problem that i know can be easily solved if i
used MSAccess, but my manager won't spend any money.

I wish to import times from several time sheets into an
overall office timesheet. Each time sheet will have a
list of job numbers (e.g. X3456) with hours spent on
each
listed next to it. I need to create a list of all the
different job numbers in the office timesheet with the
total number of hours spent on each by everyone in the
office.

I thought that a good way of doing it would be to get
everyone to save there timsheet in a certain folder.
Excel can then be told to import the info from certain
cells in each file in the folder. This will mean that
as
more files are added excel will update??

I can program, but not very well in excel and this is
past
me. Please can you help me :-)

If you need more info please don't hesitate to ask me, I
hope it makes sense?



Thanks in advance

Adam


.

Adam,

Part 1)

Delete these two lines:

Dim myBook As Workbook
Set myBook = Workbooks.Add

And change the other instances of myBook to ThisWorkbook

You may also want to change the Worksheets(1) to a specific worksheet or to add a new worksheet for the data to be copied to. Post
back if that is what you actually want.

Part 2)

Once the table is created, type in headings above the data. Then select a single cell in the table and select Data | Pivot table,
and once you're at the point where Excel shows the blank pivot table (differs between XL97 and XL2000 and up) drag the button for
job into the row area, and the hours into the data area, and you'll get your summary.

HTH,
Bernie
Excel MVP


"Adam Thickett" wrote in message ...
Cheers Bernie,

That works great,

I need 2 modifications to make it perfect, again i've
tried and failed (i really must brush up on my VB)

1) The data must be fetched into the workbook the script
is run from (currently it opens a new workbook)

2) I need it to add the times for each job number and list
the totals for each job?

Hope you can help

Regards

Adam
-----Original Message-----
Adam,

If all your worksheets are in C:\Excel\Time Sheets, the
sub below will copy the values in cells A1 and B1 (from
the default
worksheet) of all the files into a new workbook. You can
modify the code to get as many values as you need - or
post back for help
in modifying it, if this is too simplistic for your needs.

HTH,
Bernie
Excel MVP


Sub ConsolidateSameDataFromAllFiles()
Dim myBook As Workbook
Dim i As Integer

Set myBook = Workbooks.Add

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Time Sheets\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
myBook.Worksheets(1).Range("A65536").End(xlUp)
(2).Value = _
ActiveWorkbook.Name
myBook.Worksheets(1).Range("B65536").End(xlUp)
(2).Value = _
ActiveWorkbook.Worksheets(1).Range
("A1").Value
myBook.Worksheets(1).Range("C65536").End(xlUp)
(2).Value = _
ActiveWorkbook.Worksheets(1).Range
("B1").Value
ActiveWorkbook.Close False
Next i
Else: MsgBox "There were no files found."
End If

End With

End Sub




"Adam Thickett" wrote in
message ...
Hey Peeps,

I have a problem that i know can be easily solved if i
used MSAccess, but my manager won't spend any money.

I wish to import times from several time sheets into an
overall office timesheet. Each time sheet will have a
list of job numbers (e.g. X3456) with hours spent on
each
listed next to it. I need to create a list of all the
different job numbers in the office timesheet with the
total number of hours spent on each by everyone in the
office.

I thought that a good way of doing it would be to get
everyone to save there timsheet in a certain folder.
Excel can then be told to import the info from certain
cells in each file in the folder. This will mean that
as
more files are added excel will update??

I can program, but not very well in excel and this is
past
me. Please can you help me :-)

If you need more info please don't hesitate to ask me, I
hope it makes sense?



Thanks in advance

Adam


.