Delete/Overwrite Excel Spreadsheet 1

[rtd3776]

Is it possible to delete/overwrite Excel spreadsheet from Access with vba? And how could that be done?

Now when I export table from Access to Excel, it just renames it like this: Results -> Results1.

 

[PassingBy]

Assuming you want to export the results of a query:

If you create a command button on a form and attach the following command to the Click event, you will create from the chosen query an Excel file. This will export itself and overwrite any existing (named) file found in the destination folder.

DoCmd.OutputTo acQuery, "NameOfqryToExport", "MicrosoftExcel(*.xls)", "C:filename.xls", , ""

Hope this helps

 

[rtd3776]

Sorry, maybe I was not clear enough.

I tried this earlier:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Results", "C:\windows\desktop\project\fiberdb.xls", False, "Results"

This adds new sheet to this FiberDB file named as "Results1". I would like this to overwrite whole sheet OR I could delete old "Results" sheet from FiberDB and add whole new "Results" sheet in it with this docmd(if it is possible).

 

[PassingBy]

If I understand you correctly, you are trying to update 1 sheet in a workbook of multiple sheets (else you would be happy to overwrite entire file).

Unfortunately I have not had the need to do a single sheet update before, most of mine are straightforward file overwrites.

Hope someone else can help you on this.

 

[rtd3776]

Yes, that's correct.

Thanks anyway.

 

[PassingBy]

Maybe its worth looking at this from the other side...rather than exporting, create a link back from your Excel file to the db.

I found the following help notes in Excel which may be relevant:

To bring external data into Microsoft Excel, you must:

· Have access to an external data source. If the data you want access to is not on your local computer, you may need to see the administrator of the external database for a password, user permission, or other information about how to connect to the database.
· Install Microsoft Query. Microsoft Query is an optional feature for Microsoft Excel and includes the Query Wizard. To create a query in Microsoft Query, you must install it separately. For more information, click .

· Install an ODBC driver for your data source. An ODBC driver is required to retrieve data, including data in Microsoft Excel. To run a query file, you do not need to install Microsoft Query, but you must have the appropriate ODBC driver installed for your data source.

Unless you changed options when you installed Microsoft Query, Setup automatically installed three ODBC drivers that allow you to retrieve data from dBASE, Microsoft Access, and Microsoft Excel. In addition, you could have installed other ODBC drivers at that time. For a list of the available ODBC drivers that you can install, click .

If you did not previously install an ODBC driver for a data source that you want to use, you must install it separately. For more information, click .

 

[oharab]

You could try using automation. Set a reference in Access to Microsoft Excel then:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim sht as Excel.Worksheet

Set xl=createobject("Excel.Application&quot
set wb=xl.workbooks.open("C:\windows\desktop\project\fiberdb.xls&quot

for each sht in sb.worksheets
if sht.name="Results" then
wb.worksheets.delete "Results"
end if
next sht

wb.save
wb.close
xl.quit

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Results", "C:\windows\desktop\project\fiberdb.xls", False, "Results"


or something similar. I've written this freehand, so you might find a few mistakes, but the principal is sound.

Let us know how you get on.

b ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[rtd3776]

Ok, now it is working =D

All I had to change was this delete and one letter.
earlier: wb.worksheets.delete "Results"
now: wb.worksheets("Results&quot.Delete

Thank you very very much oharab, I really appreciate this.