Deleting Rows in Excel File and the Exporting results again?

[ghost2]

I know I can run a macro and specification to export results from a query to an excel file. Is there a way to purge the existing records in the sheet and then export the results? I have a pivot table that is set to refresh on open so I can't just delete the file I need to actually have the results go into the same sheet. Thanks all.

 

[PHV]

Have you tried to pull the data from within excel instead of push from access ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ghost2]

Good question but the users want to use Access where I am creating some easy to use selection forms where I am then I want to pass the parameters into the query. I could use a crosstab query instead of excel but I feel the users will have more control over the excel file if I output to excel because there is a large amount of data and sales numbers they like to play around with.

 

[jksmi]

Hi,

have you tried the get external data function in Excel (i'm guessing this is probably what PH is referring to also). Uisng this functionality within excel your users can still maintain their data in Access using the selection forms but instead of exporting the data from Access into the workbook you refresh the data from within the workbook itself. Its v. easy to use...

These first 4 steps you would only have do once in the workbook or template, the users would not have to do...
1)in excel select Data>Get External Data>New Database Query
2)from the Choose Data Source dialog slect the Databases tab (default) and scroll down to MS Access Database*.
3) Select your database and from the query wizard select your query/ table. You can change some sorting and criteria options then finish.
4) specify the top left cell of your table and your data will automatically be entered into the spreadsheet

From now on the query is built into the spreadsheet, there are loads of options you can play with - you'll notice that when you right click on the table you have some extra options (edit query, data range properties, refresh data) - refresh data does just that - gets the most up to date info from your db. If you select data range properties, under the section refresh control, you can set the table to refresh on file open.

So you can either check this option and the user doesnt have to worry about anything, each time they open the spreadsheet it will have this data in it - or you can show them how to refresh the data (or create a simple button) so that they can do it manually...

HTH, Jamie
FAQ219-2884

 

[ghost2]

Thanks alot Jamie. I will try that. Thanks.

 

[jksmi]

Hi,
if you do use this, a couple of things that have caught me out in the past are -

folder names with periods in (.) msquery doesnt like these - to get round it, before finishing the query wizard change the last option to view data or edit query in MicroSoft Query then edit the from statement in the sql, i.e.

SELECT MY_TABLE.MY_FIELD
FROM `C:\my[highlight]'[/highlight].[highlight]'[/highlight]folder\db1`.MY_TABLE MY_TABLE

to

SELECT MY_TABLE.MY_FIELD
FROM `C:\my.folder\db1`.MY_TABLE MY_TABLE

also, if someone has the server mapped to a different drive letter you'll need to edit the SQL in the same way but change the drive letter to the UNC path... i.e.

SELECT MY_TABLE.MY_FIELD
FROM `\\serverunc\myfolder\db1`.MY_TABLE MY_TABLE

HTH, Jamie
FAQ219-2884