Formatting and Summing Excel Rows (1 Viewer)

[jfgambit]

Hi All:

I am in need of any samples someone may have to insert rows into Excel and then sum data based on the individuals ID numbers. An example of the export is attached with both the way the export is delivered to excel and what it needs to look like after the update. Any help would be greatly appreciated, thanks!

 

[namliam]

1) What attached example?
2) Why dont you try the excel forum on this board?
3) Posting your question once is usually enough

 

[jfgambit]

1) Well that's strange...the attachment shows on the Form Title page but not here...let's try again.

2) Becuase it is being exported from a VB Module from Access 2000

3) I did? Well there is a first for everything.

 

[namliam]

What does the code in access do? Export a query ? Export a report? Or does it make an Excel export by opening excel?

Each way has its own approach, and different solutions. But what you want to do isnt that hard actually.....

 

[jfgambit]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Comm Payroll Schd Product Level", stPath & Left(SName, 1) & Mid(SName, InStr(SName, " ") + 1) & Format(StDt, "MM") & Format(StDt, "YYYY") & ".xls"

It's a simple DoCmd.TransferSpreadsheet command, which as I am sure you are aware, just lists everything together in a single sheet. Once transfered I would like the module to open the Excel form and format in the example I included: inserting a row after each Salesperson and summing the total sales for that individual and finally, if possible highlighting that row.

Thanks for the response namliam

 

[namliam]

If you want to actually open up the file... Try searching the forum on "Excel.application" as that is what you need to start... There are plenty of examples out there and its a bit involved so If you cannot figure it out, please tell and I / we can help you further. But I wont go into it now....
From there anything is possible that you can do in excel....

If you just want the total lines in without any formatting you can do that with a union query....
You create a new query bases on this one where you group by Salesperson and sum the amounts...
Then do a query where you do
Select * from thisquery
union
Select * from GroupByQuery
Order by salesperson

Then if you want you can also leave the name blank if you try hard enough...

General comment:
This is your query right? "Comm Payroll Schd Product Level"
1) Dont use spaces
2) Use prefixes... qry for Queries, frm for forms, tbl for tables etc...

Greetz from amsterdam