Pick specific rows from Access table to transfer to Excel worksheet

[UnsolvedCoding]

I am having a problem on how to pick out specific date in an Access table that gets transfered to an Excel worksheet.

The master table in Access is complied from several tables and has a field called Season. The information in the master table needs to be transfered to Excel by each change in season. Since new seasons are added regularly, the table needs to be exported to Excel by season without the user needing to create new code, reports or queries.

Right now I can get it to export to the various worksheets as needed but can't figure out how to export for a specific season without writting a crap load of queries and needing to add a query each time a new season is added.

I have a query written to identify seasons as they go into the master table, but need something to capture each season for a transfer to Excel.

Any ideas?

 

[SkipVought]

Hi,

NO VBA REQUIRED, as I understand your requirement.

In Excel simply Data > Get External Data > From other sources > From Microsoft Query... select Access Files* and drill down to your Access database.

Format your query with the season desired. If the season will be a user selection then make it a Parameter that the user can change ON THE SHEET.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

I need to transfer all the seasons from Access over to Excel and break out each season into its own worksheet in Excel.

 

[PHV]

I can get it to export to the various worksheets
And how do you do that ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

So you have a querytable on each worksheet w/ SQL criteria for one season. No big deal!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

PHV -

In answer to your question, I couldn't do it straight in a macro from Access, I had to convert the macro to VBA and then tweak it a bit. Here's the generic code.

Dim Last_Month
Last_Month = MonthName(Month(Date))

Dim Months_Year
Months_Year = Year(Date)

Dim Name1
Dim Name2
Dim Name3

Name1 = "All Seasons"
Name2 = "Totals " & Last_Month & " " & Months_Year
Name3 = "Totals Season to Date"

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Seasons
DoCmd.TransferSpreadsheet acExport, 10, "Master_Table_Sorted_ASC_By_Season", "K:\Totals\User\History\New Imports\Totals_" & Last_Month & "_" & Months_Year & ".xlsx", False, Name1
DoCmd.Echo True, "All Seasons Transfered"

' Totals - (May 2012)
DoCmd.TransferSpreadsheet acExport, 10, "Master_MTD_Totals", "K:\Totals\User\History\New Imports\Totals_" & Last_Month & "_" & Months_Year & ".xlsx", False, Name2
DoCmd.Echo True, "MTD Totals Transfered"

' CCA Totals - Season to Date
DoCmd.TransferSpreadsheet acExport, 10, "Master_YTD_Totals", "K:\Totals\User\History\New Imports\Totals_" & Last_Month & "_" & Months_Year & ".xlsx", False, Name3
DoCmd.Echo True, "YTD Totals Transfered"

DoCmd.Echo True, "Macro Completed"
DoCmd.Hourglass False
DoCmd.SetWarnings True
Beep
MsgBox "Done", vbInformation, "Macro Status"




Skip -
Writting a query for a single season isn't a problem, I totally agree with you that doing a single season export to Excel isn't an issue. The problem for me lies with the constant adding of seasons. So far there are around a dozen seasons and several more get added each year. What I am trying to do is find a way so this doesn't come back to me every 6 or 8 weeks for a new query.

The Mater Table holds all the complied data sorted by ascending season. Ideally, it would be much easier to find away to groupby season and export the season as a group into Excel. Right now I can export to Excel as a unit or right over a dozen queries to export season independantly, but to do that would mean creating new queries as an ongoing process.

 

[SkipVought]

What? You need a new sheet in the workbook for any new season, right? So you COPY a SHEET containing the query (I'd make it a PARAMETER query with the parameter being the SEASON!). Hmmmmmmmm. Might take 10, 12 seconds. Sounds VERY labor intensive to me??????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Of course, you could go whole hog VBA like...
[tt]
1. Query the Access db returning a DISTINCT list of seasons
2. Using the list of seaons, a) reference the correct sheet, or insert and name a new sheet for the season b) query that season for that referenced sheet.
[/tt]
That would be an automated button approch.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}