Automatic chart for each day

[SeaweedOPM]

My question is that I need to know how to have one sheet grab data from multiple sheets. I can do this very simply if it were only for a few days. The thing is that each day there is a seperate file created containing the information that i need. I created some vba code that takes the date that we started the day by day process, and then runs through every day's folder from then until present. I can also have it copy all of the table data and accumulate it in one sheet. This would take hours upon hours for just a few days worth of records though. I was thinking if there was an actuall formula that a pivot chart uses in one cell. If there is i could take the formula and just have a date variable that increases by a day and copy the new pivot formula to a cell 10 spots away or whatever. Please help or show me a referance. Thx.

 

[SkipVought]

zboyles,

This is almost a carbon copy of an erlier post that I answered.

You have made a common user error by storing data with a common structure in separate sheets. This is a HUGE MISTAKE!

To recover from this error, you ought to consolidate your data into a single table, assuming that the sum total of rows do not exceed 65536 rows per sheet in Excel.

Once you do this, reporting, charting, data analysis will be MUCH, MUCH easier than it is with your current structure.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SeaweedOPM]

Hmmm... well originally i had to use excel. Basically I have a really complex access form that upon a sale, users hit a button and it exports the sale to an excel file. What I wanted to get away from was an accumulated export that, after time, would take awhile to export. Is there an easyer way i can export each sale to a seperate database with the same structure that will hold only sales. Then I believe that I can have a pivot chart automatically grab sales and sort by sales person / date and whatever else i want! ok anyways what would i need to change out of my current export:



Set ExRst = dbs.OpenRecordset("SalesQuery")

If ExRst.EOF Then
MsgBox "There are no queries to export", , "No Objects"
GoTo ExitHere
End If

Do Until ExRst.EOF 'This exports all the queries to the same Excel File
DoCmd.OutputTo acOutputQuery, "SalesQuery", acFormatXLS, FileName, False
ExRst.MoveNext
Loop
ExitHere:
ExRst.Close
Set ExRst = Nothing
Set exApp = Nothing
End



i think that it is the "acformatXLS" i can change. Remember though that I need a full database with all sales.

 

[SkipVought]

Why doesn't each sale just get recorded in a sales table in your database?

That's where you would need to go to report sales by day, month, quarter, year, location, region, store, whatever.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}