Excel to Database

[Jennifer]

I have a project that has me stumped as to where to even go. My boss makes
excel sheets monthly displaying customers and costs for each resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums in
looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of the
costs but getting the SUM worksheet to look like the oringal worksheet kept
screwing up Access and eventually I got a file too big error (or something to
that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.

 

[Daniel.C]

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

 

[Jennifer]

How would I do it with Excel?

The point of doing a database is to automate the monthly process of summing
the numbers. The sheet contains about 40 columns and 50 rows. I tried
adding between worksheets and I would have to do each individual cell (I
can't drag and auto change the formula) because it creates a "circular
reference". Plus I also need to watch out for new customers and new
resources.

Jennifer

 

[Daniel.C]

Have a look at the following file :
http://www.filedropper.com/jennifer
I put a command button on the "Year" sheet. Customers are added, sorted
and totals are computed.
Daniel

 

[Jennifer]

This is great, Daniel! Thank you.

1 question though:

1. Customers aren't actually given numbers and they are already in a
particular order. Is there a way to not sort them but still add new
customers?

I tried simply deleting the order...Ascending but I think the .sort also has
something to do with it but I'm not sure what to delete.

Thanks again,
Jennifer

 

[Daniel.C]

Just delete the "sort" line. The order will be customers from the "Jan"
sheet, then new customers from "Feb" and so on. The list is recreated
each time by the macro :

Sub test()
Dim Coll As New Collection, Sh As Worksheet, c As Range
Dim Ctr As Integer, myRow As Integer, Resource As Integer
Sheets("Year").Range("A2:IV10000").ClearContents
On Error Resume Next
For Each Sh In Worksheets
If Sh.Name <> "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
Coll.Add CStr(c), CStr(c)
Next c
End If
Next Sh
On Error GoTo 0
With Sheets("Year")
Ctr = 1
For Each Item In Coll
Ctr = Ctr + 1
.Cells(Ctr, 1) = Item
Next Item
Set Coll = Nothing

For Each Sh In Worksheets
If Sh.Name <> "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
myRow = Application.Match(c, .[A:A], 0)
For i = 2 To Sh.[IV1].End(xlToLeft).Column
.Cells(myRow, i) = .Cells(myRow, i) + _
Sh.Cells(c.Row, i)
Next i
Next c
End If
Next Sh
End With
End Sub

Regards.
Daniel

 

[Jennifer]

On every sheet the customers are in the same order (with the exception of
additions or deletions) so is there a way to use that order? I'm trying to
recreate the exact same sheet just with sums instead of monthly values.

Just delete the "sort" line. The order will be customers from the "Jan"
sheet, then new customers from "Feb" and so on. The list is recreated
each time by the macro :

Sub test()
Dim Coll As New Collection, Sh As Worksheet, c As Range
Dim Ctr As Integer, myRow As Integer, Resource As Integer
Sheets("Year").Range("A2:IV10000").ClearContents
On Error Resume Next
For Each Sh In Worksheets
If Sh.Name <> "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
Coll.Add CStr(c), CStr(c)
Next c
End If
Next Sh
On Error GoTo 0
With Sheets("Year")
Ctr = 1
For Each Item In Coll
Ctr = Ctr + 1
.Cells(Ctr, 1) = Item
Next Item
Set Coll = Nothing

For Each Sh In Worksheets
If Sh.Name <> "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
myRow = Application.Match(c, .[A:A], 0)
For i = 2 To Sh.[IV1].End(xlToLeft).Column
.Cells(myRow, i) = .Cells(myRow, i) + _
Sh.Cells(c.Row, i)
Next i
Next c
End If
Next Sh
End With
End Sub

Regards.
Daniel

This is great, Daniel! Thank you.

1 question though:

1. Customers aren't actually given numbers and they are already in a
particular order. Is there a way to not sort them but still add new
customers?

I tried simply deleting the order...Ascending but I think the .sort also has
something to do with it but I'm not sure what to delete.

Thanks again,
Jennifer

 

[Daniel.C]

With the last macro, the order, on "Year" sheet will be :
1, 2, 3, 12, 4, 5
Is there something wrong with it ? What should it be ?
Daniel

On every sheet the customers are in the same order (with the exception of
additions or deletions) so is there a way to use that order? I'm trying to
recreate the exact same sheet just with sums instead of monthly values.

Just delete the "sort" line. The order will be customers from the "Jan"
sheet, then new customers from "Feb" and so on. The list is recreated
each time by the macro :

Sub test()
Dim Coll As New Collection, Sh As Worksheet, c As Range
Dim Ctr As Integer, myRow As Integer, Resource As Integer
Sheets("Year").Range("A2:IV10000").ClearContents
On Error Resume Next
For Each Sh In Worksheets
If Sh.Name <> "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
Coll.Add CStr(c), CStr(c)
Next c
End If
Next Sh
On Error GoTo 0
With Sheets("Year")
Ctr = 1
For Each Item In Coll
Ctr = Ctr + 1
.Cells(Ctr, 1) = Item
Next Item
Set Coll = Nothing

For Each Sh In Worksheets
If Sh.Name <> "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
myRow = Application.Match(c, .[A:A], 0)
For i = 2 To Sh.[IV1].End(xlToLeft).Column
.Cells(myRow, i) = .Cells(myRow, i) + _
Sh.Cells(c.Row, i)
Next i
Next c
End If
Next Sh
End With
End Sub

Regards.
Daniel

This is great, Daniel! Thank you.

1 question though:

1. Customers aren't actually given numbers and they are already in a
particular order. Is there a way to not sort them but still add new
customers?

I tried simply deleting the order...Ascending but I think the .sort also
has something to do with it but I'm not sure what to delete.

Thanks again,
Jennifer


:

Have a look at the following file :
http://www.filedropper.com/jennifer
I put a command button on the "Year" sheet. Customers are added, sorted
and totals are computed.
Daniel

How would I do it with Excel?

The point of doing a database is to automate the monthly process of
summing the numbers. The sheet contains about 40 columns and 50 rows.
I tried adding between worksheets and I would have to do each individual
cell (I can't drag and auto change the formula) because it creates a
"circular reference". Plus I also need to watch out for new customers
and new resources.

Jennifer

:

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

I have a project that has me stumped as to where to even go. My boss
makes excel sheets monthly displaying customers and costs for each
resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a
given year and sum the respective costs so that she can put it in a
different worksheet to create a sort of true-up. The worksheet she
puts the sums in looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all
of the costs but getting the SUM worksheet to look like the oringal
worksheet kept screwing up Access and eventually I got a file too big
error (or something to that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.