Name a new Sheet but delete if it exists first

[CLR]

Hi All...........
I have some really good code here that I obtained from you fine folks, and
it works great. It opens a new worksheet and retrieves data from our
Fourth-Shift program, just like it's supposed to. Only problem is now, it
creates the new sheet with generic names that I cannot predict, such as
Sheet8 or Sheet10, depending on how many other sheets I have open. What I
would like, if someone be so kind, is to hard-code the Sheet name into the
macro, AND, if the workbook was saved with that named sheet still open, to
first delete it before opening a new blank one...........I've tried
inventing the code myself, but am just unable.........help please.


Sub GetBOM_FromFourthShift()
Dim inputstr
inputstr = InputBox("Please enter BOM Number")

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=FS - MSS-Fourth Shift;ServerName=FS.1583;ServerDSN=FS -
MSS-Fourth Shift;UID=ddd;PWD=kkkkk;ArrayFetchOn=1;ArrayBufferSize=8" _
), Array(";DBQ=M:\Mfgsys\System;CODEPAGE=1252;")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ITEM_MULTILEVELBILL.END_ITEM, ITEM_MULTILEVELBILL.PARENT,
ITEM_MULTILEVELBILL.PARNT_DESC, ITEM_MULTILEVELBILL.REV,
ITEM_MULTILEVELBILL.MB, ITEM_MULTILEVELBILL.DRWG,
ITEM_MULTILEVELBILL.COMPONEN" _
, _
"T, ITEM_MULTILEVELBILL.COMP_DESC, ITEM_MULTILEVELBILL.COMP_MB,
ITEM_MULTILEVELBILL.IN_REV, ITEM_MULTILEVELBILL.OUT_REV" & Chr(13) & "" &
Chr(10) & "FROM ITEM_MULTILEVELBILL ITEM_MULTILEVELBILL" & Chr(13) & "" &
Chr(10) & "WHERE (ITEM_MULTILEVELBILL.END_ITE" _
, "M='" & inputstr & "')" & Chr(13) & "" & Chr(10) & "ORDER BY
ITEM_MULTILEVELBILL.COMPONENT")
.Name = "Query from FS - MSS-Fourth Shift_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Many thanks in advance..........

Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

One way:

Const sFileName As String = "Query"

Application.DisplayAlerts = False
On Error Resume Next
Worksheets(sFileName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
ActiveWorkbook.Worksheets.Add.Name = sFileName

 

[CLR]

Thanks JE.........

That looks great.........I'll give it a try tomorrow when I get back on the
network.........
It looks so easy when you do it........<g>

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

Yeah, but you can have those query strings...

 

[CLR]

I hear ya........I "recorded" it and it luckily fell into place..........<G>

Thanks again for the help, this will smooth it out.

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi JE............

Just wanted to let you know two things.............first, I sent a thank
you post about 6:30 this morning but it never seemed to have gotten here,
(although it showed up on other servers), and second, I tried your solution
today at work and it worked fine, fine, super-fine.

Thanks so much again,
Chuck, CABGx3

 

[JE McGimpsey]

Glad it worked for you.