Overwriting Worksheet

[bee]

I'm using MS Access 2000 to write this program. I'm trying to create a worksheet with the name "Within CMB2" from an existing workbook. The thing is, the program only runs when there is no existing file. I want the program to overwrite the worksheet "Within CMB2" if it already exists. Normally, the program will start without the "Within CMB2" worksheet. It will start by adding it. However, I want the program to be able to overwrite it with the same name if it already exists.

The error message that it displays is Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

Thank you so much for looking into my code.

Private Sub generatebtn_Click()
Dim oXL As Excel.Application, oBook As Excel.Workbook, oSheet As Excel.Worksheet, oSheet2 As Excel.Worksheet, oRange2 As Excel.Range, vValue As Variant
Set oXL = New Excel.Application
Set oBook = oXL.Workbooks.Open(strInputFileName)
Set oSheet = oBook.Worksheets("Source Pivot")
Worksheets.Add().Name = "Within CMB2"
Set oSheet2 = oBook.Worksheets("Within CMB2")
Set oSheet2 = ActiveSheet
Worksheets("Within CMB2").Range("A1:A1") = "DETB_UPLOAD_DETAIL"
oBook.Close
End Sub

[JimmyTheHand]

Try this code. (I didn't test it.)
Beside the necessary additions, I commented out a few lines that seemed useless to me. (Maybe this is only part of a larger code, and they have their use, actually.)

Private Sub generatebtn_Click()
Dim oXL As Excel.Application, oBook As Excel.Workbook
'Dim oSheet As Excel.Worksheet, oSheet2 As Excel.Worksheet, oRange2 As Excel.Range, vValue As Variant
Set oXL = New Excel.Application
Set oBook = oXL.Workbooks.Open(strInputFileName)
'Set oSheet = oBook.Worksheets("Source Pivot")
oXL.DisplayAlerts = False
If WorksheetExist("Within CMB2", oBook) Then oBook.Sheets("Within CMB2").Delete
oXL.DisplayAlerts = True
oBook.Worksheets.Add().Name = "Within CMB2"
'Set oSheet2 = oBook.Worksheets("Within CMB2")
'Set oSheet2 = ActiveSheet
Worksheets("Within CMB2").Range("A1:A1") = "DETB_UPLOAD_DETAIL"
oBook.Close
End Sub

Private Function WorksheetExist(Name As String, WB As Excel.Workbook) As Boolean
Dim WS As Excel.Worksheet
On Error GoTo EH
Set WS = WB.Sheets(Name)
WorksheetExist = True
EH:
End Function
HTH

Jimmy

PS: You don't need to start a new thread if something happened to be left out of the 1st post. There's an Edit option below the post, so you are allowed to modify your post in the first day. Sending another post to the same thread is also an option.

[bee]

I tried your code. It asked me if I wanted to save the changes that I made. I clicked yes and tried saving the excel file with the same name so that I can overwrite. It would tell me that the excel file already exists and asks me if I want to overwrite, I click tes but it doesn't allow me to save it with the same name. Attached is the zip with the file inside it. Thank you very much for looking into it.

I didn't notice that I've created two posts. There's something wrong with my browser not reloading. I tried deleting the other one but it did not allow me. I already asked a moderator to delete the other post yesterday. :)

[JimmyTheHand]

I tested the program/form you posted. I chose a workbook, then clicked on button 'Generate'. It worked a little, then asked me if I want to save the changes. I clicked 'Yes', and the program saved the changes fine. I checked the workbook via Excel user interface, and saw the new worksheet called 'Within CMB2'.
It seems I can't reproduce the error you get. :dunno

I'm not sure but maybe it's the result of multiple trials. When you open a workbook with this code, and don't close it e.g. because of a runtime error, the file remains open, and the excel process keeps sitting in memory. If you repeat this several times, you will have several excel processes running in the background, and maybe as many open instances of the same file. I can't divine what can happen under such circumstances. I suggest you try the same code whan you are sure there are no unattended Excel processes in memory. For example, right after computer restart.

Jimmy

[bee]

I tested the program/form you posted. I chose a workbook, then clicked on button 'Generate'. It worked a little, then asked me if I want to save the changes. I clicked 'Yes', and the program saved the changes fine. I checked the workbook via Excel user interface, and saw the new worksheet called 'Within CMB2'.
It seems I can't reproduce the error you get. :dunno

I'm not sure but maybe it's the result of multiple trials. When you open a workbook with this code, and don't close it e.g. because of a runtime error, the file remains open, and the excel process keeps sitting in memory. If you repeat this several times, you will have several excel processes running in the background, and maybe as many open instances of the same file. I can't divine what can happen under such circumstances. I suggest you try the same code whan you are sure there are no unattended Excel processes in memory. For example, right after computer restart.

Jimmy

Oh yes, thansk for informing me. There were so many Excel programs in my Task manager. :) Hmm, is there a way that the message box would not have to ask me if I want to save the changes? Can it automatically overwrite?

[JimmyTheHand]

oBook.Close SaveChanges:=True

[bee]

oBook.Close SaveChanges:=True

Thanks Jimmy, it worked! :)