VBA / Excel problem (1 Viewer)

[ray147]

Hi,

I have a routine in Access VBA that opens an Excel spreadsheet and creates a pivot table on a sheet named 'Pivot' and then gives the user the control in Excel. The user can then save the spreadsheet.

However, I'm getting a problem that if the user tries to run the routine again, an error is raised since the sheet 'Pivot' already exists...is there a way to check if a particular sheet name exists, and if it does, delete it ?

Feedback appreciated...thanks!

 

[Jibbadiah]

Ray,

You could add the following module to your code and call it just before you run your export to excel code.

You would call it just by adding Checkexist(Filename) as a single line command.

Public Function CheckExist(FileName)
If Len(Dir(FileName)) > 0 Then
Kill FileName
End If
End Function

If it is the worksheet itself that is the problem, then you could make the naming convention relate to time so that there is a degree of uniqueness.

J.

 

[Ian Mac]

What is code you have currently, it's easier to adapt what you have than second guess what you may need to fit into your code.

That said, you can loop through your sheets, controlled from Excel you would use something like.

Dim sht As Worksheet

For Each sht In Worksheets 
If sht.Name = "Pivot" Then
Msgbox "Already Exists!"
Exit Sub
End If
Next sht

However, you may just want to check to see if the Workbook is there, saving it off before running the real code you code see it exists already, that way you save running a chunk of code you may not need to.