Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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 "Daniel.C" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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" wrote: 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 "Daniel.C" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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" wrote: 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 "Daniel.C" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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. "Daniel.C" wrote: 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" wrote: 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 "Daniel.C" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel to Database
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. "Daniel.C" wrote: 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" wrote: 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 "Daniel.C" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Convert Excel database to dBASE database? | Excel Discussion (Misc queries) | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
How to make reference to database and if true copy from database? | Excel Discussion (Misc queries) | |||
Moved database, how do I change link to the database in excel | Excel Discussion (Misc queries) |