Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Frage: How to macro-out the daily numbers??
Every morning I run a db query that returns a result set in excel. Needless
to say, there's a great deal of "massaging" that needs to be done to that 'raw' data. Once it's formatted correctly, I have to copy it to another workbook called "[ThisMonths]Daily Numbers". I then upload it to the intranet site where anyone can view the daily numbers. Probably about 90% can be recorded as a macro, but the other 10% requires a little VBA. Here's the problem: There are some records that get deleted based on their vendor name. Also, there are an even larger number of records which get moved to the "zero" sheet in the "Daily Numbers" workbook whose 'total' cell equals 0. What I'm left with is the usable data that is to go into the "Numbers" sheet of the "Daily Numbers" book. It gets tricky though, because as the month goes on, the "Numbers" sheet grows downward. But there's formulas that need to go into each days data. But the daily data is dynamic, some days I might have 20 records, other days I might have 45. No way to know. Maybe a 'count' function could add 'em up and ... AAAHH!! I know I didn't do a really good job of illustrating my daily madness... but if anyone has any ideas that might help to automate this crap I'd appreciate it. Thanks Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Frage: How to macro-out the daily numbers??
Dan,
The first thing you need to do is get an index to the last row. Assuming the key column is A, this returns the last row iLastRow = Cells(Rows.Count,"A").End(xlUp).Row -- HTH Bob Phillips "Dan" wrote in message ... Every morning I run a db query that returns a result set in excel. Needless to say, there's a great deal of "massaging" that needs to be done to that 'raw' data. Once it's formatted correctly, I have to copy it to another workbook called "[ThisMonths]Daily Numbers". I then upload it to the intranet site where anyone can view the daily numbers. Probably about 90% can be recorded as a macro, but the other 10% requires a little VBA. Here's the problem: There are some records that get deleted based on their vendor name. Also, there are an even larger number of records which get moved to the "zero" sheet in the "Daily Numbers" workbook whose 'total' cell equals 0. What I'm left with is the usable data that is to go into the "Numbers" sheet of the "Daily Numbers" book. It gets tricky though, because as the month goes on, the "Numbers" sheet grows downward. But there's formulas that need to go into each days data. But the daily data is dynamic, some days I might have 20 records, other days I might have 45. No way to know. Maybe a 'count' function could add 'em up and ... AAAHH!! I know I didn't do a really good job of illustrating my daily madness... but if anyone has any ideas that might help to automate this crap I'd appreciate it. Thanks Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Frage: How to macro-out the daily numbers??
Dan,
you've written nothing about the dbquery but could you not save the first two steps by including a where condition into the sql (something like WHERE (Vendor<'xxx') AND (Total Cell<0.0)? You can also subtotal by day, which should make the last step superfluous. Not sure if this helps, but have slight difficulties to picture the problem. You seem to be german speaking - if it helps, write me in german |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Frage: How to macro-out the daily numbers??
The query is actually embedded in this archaic FoxPro app and there's no way
for me to alter it. So, what I do every morning is hit a key which in turn launches an internal proc that finally kicks back this raw data. I then spend about 10 minutes and sort it, remove certain records, archive other records, re-sort the data, stick a header and footer in, then tweak the formulas a bit. It's really not that hard to do manually, but I'm lazy! There's so many mundane little steps that I have to go through, that its really hard for anyone to "picture" the problem. More reason to macro it out. You're right though, it would be so much easier if I could just type up my own SQL. Mein deutsch ist leider nicht zu gut. Ich habe Deutsch 4 Jahre in der Schule studiert. Meine Frau hat Deutschland 2mal besucht aber ich hab' die Chance noch nicht. Sie kann gut auf deutsch sprechen. Mindestens, besser als ich. Dan "chris" wrote in message oups.com... Dan, you've written nothing about the dbquery but could you not save the first two steps by including a where condition into the sql (something like WHERE (Vendor<'xxx') AND (Total Cell<0.0)? You can also subtotal by day, which should make the last step superfluous. Not sure if this helps, but have slight difficulties to picture the problem. You seem to be german speaking - if it helps, write me in german |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Frage: How to macro-out the daily numbers??
ok - sorry for replying late.
I can think of various solutions. if you want to use a macro (not sure if I would) identify the active range by: Sub show_active_range() Dim MyRange As Range Dim No_of_Rows As Integer Range("a1").Select 'this is were your range starts Set ShiftRange = ActiveCell.CurrentRegion No_of_Rows = ShiftRange.Rows.Count MsgBox No_of_Rows 'gives you number of rows - inactivate when not needed. 'You then need to continue writing something along the lines for each row in active range etc and filter/delete data as wanted End Sub second method - ok, that might be a bit simple, but why not using the advanced filter function with criteria sorting out the deleting for you? You could then copy visible cells only. third method (I think most elegant): make sure the data is named (will probably come through as Query_From_FoxPro) then, in excel, import from External data, new database query, Excel, and link to the current range. With MsQuery you can set sql as wanted, which leaves you with cleaned data. Next time, just make sure th name includes all data, then goto Ms Query and refresh data Hope this is halpful - if you can send me an example, feel free to do so |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I automatically reset numbers to 0 on a daily basis? | Excel Discussion (Misc queries) | |||
Newbie problem: numbers with +positive and -negative indicators | Excel Discussion (Misc queries) | |||
How do I do a formula to update numbers daily? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions |