Add external data to formatted worksheet 1

[PaulBricker]

I wasn't sure exactly how to describe this problem in the subject line but it's fairly straight forward. I have an attendance database in MS Access. We take attendance daily and have always printed a report that goes to 'central office'. Now central office want this done electronically. I need to export the information in my database to a spreadsheet. I can do this with the TransferSpreadsheet Method in VBA. But my problem is with the formatting of the excel sheet. They want some information in the first 2 rows that stay the same for each attendance sheet. Basically the title and some info about the week. The first 2 rows look like this

"Attendance Sheet for ABC School"
Week Of: 8/24/09

The date is inserted using the formula Now()-Weekday(Now())+2 and the cell is formatted for the date only to get rid of the time value.
After that, I have a row of field names and after that there is usually about 10 rows of faculty/staff absent for the day.
The TransferSpreadsheet Method doesn't allow me to start my data in a specific cell so I can't have the first 2 rows in the spreadsheet set up this way. I'm looking for alternatives.
1. I know I can put &[Date] in a Header and get todays date, but can't find a way to put an expression in the Header. Is this possible?
2. Thought about merging the two sheets or displying the data from sheet1 on sheet2 (=Sheet1!A3) but I'm unsure if I can email a specific sheet from a workbook as an attachment.

Any suggestions are welcome. I very versed in VBA in Access so coding isn't an issue.

Thanks

Paul

 

[SkipVought]

Hi,

I do stuff like this nearly every day. Its a piece of cake.

Data > Import External Data > New Database Query... Select the MS Access driver, drill down to your database, add the table you want to query from, fields, criteria etc. and then File > Return Data to Microsoft Excel

This is a ONE TIME setup, to add your QueryTable to the sheet.

To get new data, select in the resultset on the sheet and Data > Refresh.

Skip,

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

 

[PaulBricker]

Way too easy. I should spend more time in Excel.

Thanks again.

Paul

 

[SkipVought]

Sure! Take off your shoes and kick back. Have a drink on me, as you excel. ;-)

Skip,

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

 

[PaulBricker]

Consider it done.


Paul

 

[SkipVought]

hey, we MAJOR in easy!

E^z MAJOR

Bus Conducting

Get you Fill of Harmonics!



Skip,

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

 

[PaulBricker]

P.S. Congrats on the TMOTW. Well deserved.

Paul

 

[xlhelp]

P.S. Congrats on the TMOTW. Well deserved.[

TMOTW? - Twitter Mom of the Week?


.....oh, you mean Tip Master of the week....yes, Congrats Mr Vought

A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

The leading syllable in twitter, give me pause and the mom part: well that's above my pay grade.

Thanks, nonetheless, y'all.

Skip,

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

 

[PaulBricker]

Back to serious. Thought I'd start here before I try in the Access forums. I need to email this spreadsheet. No problem with the code for that. I can even refresh using the code. The problem is I can't find a way to refresh the data while the database is open. It says the db is open exclusively by...yada, yada, yada.
That means I need to open the db, add data, close db, open excel, refresh data, close excel, open db, click button to email. I know I could probably close excel in the same code I send the email but it's still more than I want to do (actually I don't have to do this, the admin assistant does and we can't be p***ing them off now can we?).
Any thoughts on how to get around the locking. Nothing in the database is open except a form with 1 button on it.

Thanks for your time.

Paul

 

[SkipVought]

I thot you were in Excel? Why are you eMailing from Access?

I can have my Access db open AND access & refersh data in it, so I cannot replicate that part of your problem.

Skip,

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

 

[PaulBricker]

Most of the work actually gets done in Access. It's an attendance db (using Access). We used to just print the attendance report and mail it to 'central office'. Now they want it electronically in Excel. I can export it using TransferSpreadsheet, but it looses the formating. Your suggestion allowed me to retain that formatting and still refresh the data. But as I said, we have to email the spreadsheet. I had hoped to enter the daily attendance and then click a button that refreshed the data in the spreadsheet with that days info and then email it from there. I've got it all except the locking issue.
Should I be trying to build the spreadsheet using a recordset? I suppose I could do that but it seemed like a lot of work when all I need is a refresh on the data.

Thanks

Paul

 

[PaulBricker]

In case this is still as clear as mud.
All the info is entered in an Access database daily. Then we have to export that data to an Excel spreadsheet and email the spreadsheet as an attachment. I'm trying to do all of this from the database using a form with a button. The problem I'm having is I can't refresh the data in the spreadsheet if the database with info in it, is open. I don't exactly know why, but I've tried this a few different ways (just using the refresh button in excel, changing the settings in the worksheet to refresh on open and just using the refresh method directly in VBA), and it always fails if the db is open when refreshing the data.

Thanks for you suggestions and time. I appreciate it.

Paul