Testing the existence of a table, and if nothing, creating the table

[idlewyld89]

Alright, so I've got a bit of an odd outcome from this bit of code... It works, but only under specific circumstances.

If I run the code on a fresh file (where the table "FleetStatusDetail" doesn't exist and HAS NEVER EXISTED) I get a Runtime Error 438. HOWEVER, if I comment out the IF statement (thereby forcing table creation without testing) and then run the exact same code with the IF statement back in, it works without error. The IF statement recognizes the existence of the table and ignores the line of code to create it again.

I should also say that, if I delete the sheet and copy back in the raw data (not formatted as a table) the code will also identify the non-existence of the table and create it as intended.

It seems that it's ONLY if I run the code on a completely fresh file that it generates this error... and I'm stumped!!!

Private Sub convert_DataToTable()
' References:
' Dependents:


' ~~> Converts the active sheet's dataset to a table (if it is not already formatted as a table)


If ActiveSheet.ListObject("FleetStatusDetail") Is Nothing Then


    ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("A1").CurrentRegion, LinkSource:=False, xlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleLight1").Name = "FleetStatusDetail"


End If


End Sub

Pertinent info:
- "FleetStatusDetail" is the name of the table which I am testing the existence of, and if nonexistent, creating

 

[AlphaFrog]

There's a syntax error. Missing an "s"

If ActiveSheet.ListObject[B]s[/B]("FleetStatusDetail") Is Nothing Then

Then use this to test if the table exists.

Dim lo As ListObject
On Error Resume Next
    Set lo = ActiveSheet.ListObjects("FleetStatusDetail")
On Error GoTo 0
If lo Is Nothing Then

    ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("A1").CurrentRegion, LinkSource:=False, xlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleLight1").Name = "FleetStatusDetail"

End If

Or just this...

If ActiveSheet.ListObjects.Count = 0 Then

 

[idlewyld89]

Haha, that's embarrassing... that was a product of having transferred the code incorrectly when testing on a new file. I am curious as to why, as mentioned above, the code works after the table has been created at least once before, but not until then...

I get a Runntime Error 9 - Script out of range.

Again... if I comment out the if statement, force excel to create the table the first time, it will recognize the if statement after that. Then, if I copy in the raw data (as opposed to using a fresh file) it correctly recognizes the lack of the table and creates it.

Thanks for the reply, will definitely put it to good use!