Access to excel

giana
12-10-2003, 09:50 AM
Hi everybody, I have this query in access that gives me all the hours that a person worked for a given time range. From this I have to create a time sheet report: the problem is that the time sheet is a template created in excel. I tried to embed it in access, but I cannot make the right links to the datas (and apparently is something really difficult, as I tried asking you guys this already, but nobody succeded). So I decided to export the result of the query to excel and go from there. Only problem is that everytime I do it, access rewrite completely on the excel file, erasing the time sheet template.
Any idea?

Timbo
12-10-2003, 11:13 AM
Open the Excel file and determine the last occupied cell (look in the "Excel FAQ 101" thread).
Then simply set a reference to the next cell and use the 'CopyFromRecordset' method of the range object...

giana
12-10-2003, 11:34 AM
Open the Excel file and determine the last occupied cell (look in the "Excel FAQ 101" thread).
Then simply set a reference to the next cell and use the 'CopyFromRecordset' method of the range object...

Since the query is always for a 2-week range, the last cell will always be H15. But how do I set that reference?
here's the code of the button:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Time Billed Query", _
"C:\Documents and Settings\Giana\Desktop\Time Sheet.xls", True, Range("H15").CopyFromRecordset

And since we are here, I have excel 2000, but I can't seem to be able to find it on the access options of transfer spreadsheet

Timbo
12-10-2003, 11:40 AM
It might be a good idea to look up the 'CopyFromRecordset' method in the Excel VB help before using it... :)

giana
12-10-2003, 11:46 AM
It might be a good idea to look up the 'CopyFromRecordset' method in the Excel VB help before using it... :)
I thought I had to use it in access and that's where I looked up. Only problem is that I just started and I'm probably not as smart as you are....

trental
12-11-2003, 10:12 AM
once you add the reference to excel in your access vba editor you can use the excel commands from access via the excel object you have created.

giana
12-11-2003, 10:35 AM
once you add the reference to excel in your access vba editor you can use the excel commands from access via the excel object you have created.


I'm sorry, I've no idea of you're saying. Only thing I know is that i've been trying for almost 2 weeks now to solve this problem and onr way or another I get an error back. I really don't know how to do it and what I would need would be an example or something.

trental
12-12-2003, 02:07 AM
if you post the db and the timesheet template i can spend a little time writing a routine for you that will show you how i would go about making the transfer for you. it sounds like you are trying to export the data from access into the template xls excel file - but access is overwriting the file - and you want it to place the new data in the middle of the existing file...?

if this is the case you could output to a new excel file, open both in excel, and use the automation to copy (inside excel) from one workbook/spreadsheet to the other.

i can show you how to do that but i cannot understand the other stuff you posted on the earlier thread about importing the sheet into access etc.

i suppose in this instance you could have a table which looks like your timesheet design header and footer.

link in a blank spreadsheet, then use append queries to insert the header, data and footer.

?

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum