Export data to Excel

[jonnyuk3]

Hi,

I have a macro that exports data to an excel spreadsheet.

When i run the macro i receive a prompt saying 'The file filename already exists. Do you want to overwrite?'

I dont want to overwrite the entire file but what i would like to do is overwrite the data held in sheet one of the excel file. Is this possible?

I am using the OutputTo action for the macro export.

jonnyuk3

 

[chergh]

No, what you want is not possible with Access macros

 

[jonnyuk3]

Thanks, I thought that may be the answer.

Would it be possible using VBA?

jonnyuk3

 

[chergh]

Yes it's entirely possible with vba.

 

[jonnyuk3]

Any chance of giving me a clue as to how i could do this then?

jonnyuk3

 

[chergh]

'Excel variables
dim xlapp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

'access recordset for data to be copied
dim rs as recordset

'string variable for the location of your spreadsheet
dim path as string

'create excel application object
set xlapp = new excel.application

'path to location of workbook you want to open
path = "c:\YourWorkbook.xls"

'open the workbook
set wb = xlapp.workbooks.open(path)

'delete the worksheet that contains the data you want to overwrite
wb.worksheets("SheetName").delete

'create a new worksheet
set ws = wb.worksheets.add

'rename the new worksheet with same name as deleted sheet
ws.name = "SheetName"

'create the recordset from query or table
set rs = currentdb.openrecordset("QueryOrTableName")

'copy the data from the recordset into the worksheet from cell A1
ws.range("A1").copyfromrecordset rs

'clean up
set ws =nothing
set wb = nothing
set xlapp = nothing
set rs = nothing

 

[NJBuckeye]

Cells

Can you link fields to specific cells, so that when you export the data, it goes into the cells?

Would having a template work? Could I export the data into a template?

We've started a new training program, and part of it involves giving a leadership assessment. The goal is to havean on-going summary (averages actually) for everyone who has ever taken the test here, so people can see how they fare against co-workers.

We also want to be able to compare the classes against each other, and people in specific job catagories. Age, race, education, and job classification are also other areas for comparison.

I can do all of this in Access, BUT we'd like graphs. I did a pivot chart, but I can only show one graph at a time... (For example, I can't have a bar graph with one set of bars showing the class average and a second set, from a different query, showing an individual score... or at least I don't think so. Graphing in Access appears to be very cumbrsome.

If I can export it to Excel, I can have a much nicer looking graph to show the summaries, and a graph that shows an individual class.

Also, I can give printouts with the actual numbers... to do that in Access, I need to alter the layout to vertical, and I can't figure out how to do it... I think it is impossible. There are too many catagories to fit across a page, but I can list them going down the page. In Excel, I could have a colum with average of scores from everyone who has taken it, the class average, and a specific individual. I don't know of any way to do that in Access.

Thanks... hope my question and explanation are clear and not too bogged down in detail.