Delete Excel Sheet And Then Add New One

[carlnewboult]

Hiya,

Just wondering if anyone knows how i go about deleting an excel sheet and then add a new one with specific headers ?

I have spent 3 hours looking round this site with no luck either I am blind or you cannot do what I am asking.

Cheers

 

[Bat17]

here is the basic code to delete and add, you should be able to find code to let you add the headers, if not I will look again in the morning.

Peter

Sub DelXlSheet()
Dim xlApp As Excel.Application
Dim wk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application

Set wk = xlApp.Workbooks.Open("C:\MyFile.xls")
wk.Sheets("Sheet2").Delete
xlApp.Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = "newSheet"

'Save with current name
wk.Save
wk.Close
xlApp.UserControl = False
xlApp.Quit
Set xlApp = Nothing
End Sub

 

[boblarson]

One of the ways that I get Excel Code, is I go into Excel, click on Tools / Macro / Record New Macro and then do whatever it is that I want to automate and then go copy the code from the module it createes. It sometimes needs a little tweaking, but for the most part, that's how I've managed to do all the various things I need to do in Excel via code.

 

[carlnewboult]

Thanks for the code Bat17 that worked a treat don't suppose anyone knows how to put the headers on do they and also why the excel document will not open until access has been closed

Cheers

 

[bat1799]

I have had some time to play with the code again Try this

Sub DelXlSheet()
Dim xlApp As Excel.Application
Dim wk As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set wk = xlApp.Workbooks.Open("C:\MyFile.xls")
xlApp.DisplayAlerts = False
    wk.Sheets("Sheet2").Delete
xlApp.DisplayAlerts = True
xlApp.Worksheets.Add.Move After:=xlApp.Worksheets(xlApp.Worksheets.Count)
xlApp.Sheets(xlApp.Worksheets.Count).Name = "newSheet"
wk.Sheets("newSheet").Range("A1") = "First Header"
wk.Sheets("newSheet").Range("B1") = "Second Header"
wk.Sheets("newSheet").Range("C1") = "Third Header"
With wk.Sheets("newSheet").Range("A1:C1")
    .Font.Bold = True
    .Font.ColorIndex = 3
    .Interior.ColorIndex = 37
    .Interior.Pattern = xlSolid
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    

    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideVertical).ColorIndex = xlAutomatic
    
    

End With
wk.Sheets("newSheet").Columns("A:C").EntireColumn.AutoFit
        

'Save with current name
wk.Save
xlApp.Visible = True
'wk.Close
xlApp.UserControl = True
'xlApp.Quit
Set wk = Nothing
Set xlApp = Nothing
End Sub

HTH

Peter

 

[carlnewboult]

Thanks a lot for all your help you have been a star.

I read your post that you have had to change your username and i think i have found a way around it by changing my password.

Worked for me.