Creating a calendar report in excel using MSAccess query data

[cori25]

I have data that I want to import from access into excel, I need to be able to pull in the data so that it can be automated to appear in calendar format. The dates are in the db, so I want it to be pulled into excel and distributed to the correct date on the calendar. I have had no luck yet......any help would be greatly appreciated

Thanks
Cori

[puppydogbuddy]

I have data that I want to import from access into excel, I need to be able to pull in the data so that it can be automated to appear in calendar format. The dates are in the db, so I want it to be pulled into excel and distributed to the correct date on the calendar. I have had no luck yet......any help would be greatly appreciated

Thanks
Cori

Cori,

Try and follow these steps:
1. Construct a basic Access query that retrieves the data that you want to export to excel.
2. Go to the query object in the Access DB window, select new (for a new query), then select the crosstab query wizard, keeping in mind that you are using the wizard to construct a preliminary crosstab query, which you will be able to modify and refine as needed.
.
3. Respond to the Wizard's prompts as follows:
Data Source: your basic query from #1 above
Column Header: Your dateField
Value Column: the column that has the values for each date in the column header.
Row Header: all other columns from your source query

4. Refine your crosstab via the query grid until you have it diisplaying the data the way you want.

5. Place the following code behind a button on the form that you use to enter the data into the table. This is an example using the TransferSpreadsheet method to export the data to Excel. The general syntax is:
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

An example to illustrate the transfer spreadsheet method.

Private Sub YourButton_Click()
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, YourCrosstabQueryname & Format(DateAdd("m", -0, Date), "MMM") & Format(Date, " yyyy"), "C:\Temp\FileName.xls", True

[cori25]

Thanks for the information. Although, I am not using a form to get the data, I have linked to an excel spreadsheet which pulls in the information. My goal is to automate this, so a form would not be necessary. Also, if I am not mistaken, this will just create an output similar to a pivot table report. I wanted an actual calendar with the data not just the raw data dumped into excel. Any other ideas?

Thanks!

Cori,

Try and follow these steps:
1. Construct a basic Access query that retrieves the data that you want to export to excel.
2. Go to the query object in the Access DB window, select new (for a new query), then select the crosstab query wizard, keeping in mind that you are using the wizard to construct a preliminary crosstab query, which you will be able to modify and refine as needed.
.
3. Respond to the Wizard's prompts as follows:
Data Source: your basic query from #1 above
Column Header: Your dateField
Value Column: the column that has the values for each date in the column header.
Row Header: all other columns from your source query

4. Refine your crosstab via the query grid until you have it diisplaying the data the way you want.

5. Place the following code behind a button on the form that you use to enter the data into the table. This is an example using the TransferSpreadsheet method to export the data to Excel. The general syntax is:
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

An example to illustrate the transfer spreadsheet method.

Private Sub YourButton_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, YourCrosstabQueryname & Format(DateAdd("m", -0, Date), "MMM") & Format(Date, " yyyy"), "C:\Temp\FileName.xls", True

[FishVal]

Thanks for the information. Although, I am not using a form to get the data, I have linked to an excel spreadsheet which pulls in the information. My goal is to automate this, so a form would not be necessary. Also, if I am not mistaken, this will just create an output similar to a pivot table report. I wanted an actual calendar with the data not just the raw data dumped into excel. Any other ideas?

Thanks!

Could you give an example of calendar layout you want to get?
Additionally posting metadata of relevant dataset(s) would be nice.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[NeoPa]

Registering interest.

[MMcCarthy]

I have data that I want to import from access into excel, I need to be able to pull in the data so that it can be automated to appear in calendar format. The dates are in the db, so I want it to be pulled into excel and distributed to the correct date on the calendar. I have had no luck yet......any help would be greatly appreciated

Thanks
Cori

I think you will need to look at using Excel Automation for this.

[missinglinq]

The more I read this thread the more confused I am! First you say "I have data that I want to import from access into excel." Then you say "I am not using a form to get the data, I have linked to an excel spreadsheet which pulls in the information."

Exactly where does this data come from; how is it inputted? It sounds like it originates in Excel. If this is true, why are you running it thru Access and then, seemingly, back into Excel? Is the calendar you want to fill in supposed to be in Access or Excel?

Linq ;0)>

[cori25]

This particular project is in regards to Overtime. I have there exceptions pulling from an other source into excel, which is linked to access. The output of this data is a mess so I clean it up in excel and link to access. From there I have other data in access that runs and needs the exceptions data before it is sent out to employees(in calendar format) so that they know what is available. It is necessary to have this data in access because there are quite a few more steps before the process is complete which needs to be done in access.

After the email is sent out with the OT Availabilty the employees interested in the shifts will go to a form I created in access and input what they want. Next I have to figure out how to allocate the OT if more then 1 person wants the shift...

Do you understand why I need the calendar format now? I know I can do it in a pivot table, but I feel this will be too confusing for the enduser to comprehend.

The more I read this thread the more confused I am! First you say "I have data that I want to import from access into excel." Then you say "I am not using a form to get the data, I have linked to an excel spreadsheet which pulls in the information."

Exactly where does this data come from; how is it inputted? It sounds like it originates in Excel. If this is true, why are you running it thru Access and then, seemingly, back into Excel? Is the calendar you want to fill in supposed to be in Access or Excel?

Linq ;0)>