Create New Worksheet if not already there 1

[DrSimon]

Working from Access, I'd like to be able to open a spreadsheet (know how to do that) and then insert a new worksheet if one of that name doesn't already exist. The logic being something like:
If not exists xx.Sheets("Feb06") then Insert xx.Sheets("Feb06")
Any advice would be very useful.

 

[bboffin]

DoCmd.TransferSpreadSheet creates/recreates a worksheet named after the query or table that you are exporting but doesn't remove any existing worksheets from the workbook (.xls file) so it will probably do what you want without the need to open Excel. All you need to do is create a query with the required name.



Bob Boffin

 

[DrSimon]

Good point Bob, but what I wanted to do was check the existing spreadsheet first just in case and also write to specific cells rather than 'dump' a table to Excel.

Simon Rouse

 

[JerryKlmns]

If you call for that sheet name then there is an error. In such a case add a new worksheet at the end and name it!

Dim objWrkbook As Object
Dim objSheet As Object
Dim yoursheetname As String

yoursheetname = "dd"
Set objWrkbook = ActiveWorkbook 'you know this
On Error Resume Next 
Set objSheet = objWrkbook.Sheets(yoursheetname)
If Err <> 0 Then
    objWrkbook.Worksheets.Add.Move after:=objWrkbook.Worksheets(Worksheets.Count)
    objWrkbook.Worksheets(Worksheets.Count).Name = yoursheetname
End If
On Error GoTo 0

 

[DrSimon]

Thanks JerryKlmns, just what I wanted.

Simon Rouse

 

[MajP]

I use this with a lot of different collections

Public Function fncUniqueName(theNewName As String, theCollection As Object) As Boolean
Dim Item As Object
fncUniqueName = True
For Each Item In theCollection
If theNewName = Item.Name Then
fncUniqueName = False
Exit Function
End If
Next Item
End Function