Creating a calendar report in excel using MSAccess query data

Originally Posted by 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

Originally Posted by puppydogbuddy

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

Originally Posted by 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!

Originally Posted by 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

Originally Posted by 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)>