Newbie Frage: How to macro-out the daily numbers??

[Dan]

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

 

[Bob Phillips]

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

 

[chris]

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

 

[Dan]

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]

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