Adjusting Code To Delete Worksheets

[DMT Dave]

Hi Guy's, ranman kindly gave me this option to add data from Access to excel and generates worksheets A to Z and adds the data.

This works great, the question I have is if I added a new contact that starts with C for example:

Can i adapt this code to delete worksheet C then replace after update once postcode is added ?

I am guessing where Currentdb.QueryDefs.Append qdf, is it a matter of running similar code before hand and changing .Append to .Delete ?

assuming this will delete all worksheets from A to Z ? it wouldn't matter if we done that and replace all again, in fact guy's, it maybe wise to delete all works sheets that start A,B,C to Z and replace because if another user adds a record starting T and I have added starting with C then more than one sheet requires update ?



How can I adapt this to delete the sheets A-Z then run the following code to replace them ?

Dim sSql As String, srcPath As String, srcFile As StringDim vChr
Dim i As Integer
Dim qdf As QueryDef
Const kQRY = "qsNames2Export"
Dim vFile, vLtr




On Error Resume Next


srcPath = "T:\DMT Ltd\XL Files\"
srcFile = "New Items.xlsx"


vFile = srcPath & srcFile




 sSql = "select * from tblDealers where left(ucase([Name]),1)<='A'"
Set qdf = New QueryDef
qdf.Sql = sSql
qdf.Name = kQRY
CurrentDb.QueryDefs.Append qdf




For i = 65 To 90
     vLtr = Chr(i)
     If i = 65 Then   'A or less
        sSql = "select tblDealers.Name, tblDealers.Town, tblDealers.Postcode from tblDealers where left(ucase([Name]),1)<='" & vLtr & "' ORDER BY tblDealers.Name, tblDealers.Name;"
     Else
        sSql = "select tblDealers.Name, tblDealers.Town, tblDealers.Postcode from tblDealers where left(ucase([Name]),1)='" & vLtr & "' ORDER BY tblDealers.Name, tblDealers.Name;"
     End If
     
     Set qdf = CurrentDb.QueryDefs(kQRY)
     qdf.Sql = sSql
     qdf.Close
     
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, kQRY, vFile, True, vLtr
Next
Set qdf = Nothing

[June7]

No, changing .Append to .Delete will not delete worksheets.

If there are no other worksheets, maybe easier to delete workbook and create a new one.

If Dir(vFile) <> "" Then Kill vFile

[DMT Dave]

Hu June7, yes there are 3 sheets Sheet1, Sheet2, Sheet3 then afterwards it's A-Z

the code that works does generate the sheets after any sheet that already exists in that workbook

[June7]

And there is data on Sheets 1, 2, 3?

[Gicu]

If you don't want to recreate a new file each time as suggested by June7 then you need to use automation to delete the sheets. Here is an example of how to do just that:
https://powerspreadsheets.com/excel-vba-delete-sheet/

Cheers,

[DMT Dave]

H Guy's thank you very much will follow up once I can make adjustments

Kindest