Adding a named worksheet into an existing workbook

[hatman88]

Hello, I'm trying to write some code that makes a new sheet with a given name in an existing workbook. However, if the sheet already exists, I want the macro to use that for what it has to do. Any help? Thanks

 

[SkipVought]

First see if there's a sheet with the name. Use your excel application object and workbook object...

dim ws as excel.worksheet, bFound as boolean
bfound = fasle
for each ws in xl.wb.worksheets
   if ws.name = YourNameVar then bfound = true
next

if bfound then
   set ws = xl.wb.worksheets(YourNameVar)
else
  set ws = xl.wb.worksheets.add
  ws.name = YourNameVar 
end if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hatman88]

Thanks a lot, however i tried that and it didn't seem to work. Apparently i cannot make a boolean, that isn't too much of a problem, however i cannot even make an excel.worksheet object, nor can i use the for each you provided. I tried manipulating the code a little bit, but i'm still confused, could you please clarify?

 

[hatman88]

Nevermind, i figured out how it could be done.

I exploited what seems to be a glitch. If you set the active sheet to a sheet that does not exist, an if statement testing to see if any cell has any string in it will always yield true apparently. Since that's the case, then one can just make a new sheet.

For example:

...
set aSheet = wbExcel.sheets("newSheet")

if aSheet.cells(99,99) = "anything" then
   wbExcel.sheets.add.name = "newSheet"
   set aSheet = wbExcel.sheets("newSheet")
end if
...

Don't know why, but it works fine with me.

 

[IknowMe]

modifying skips suggestion from VBA to EB (which it appears your using based on the collection loop and bool error)

for thissheet = 1 to wbExcel.worksheets.count
    if wbExcel.worksheets(thissheet).name = YourNameVar then
        bfound = -1
        exit for            
    end if
next

if bfound then '
    set aSheet = wbExcel.worksheets(thissheet)
else    
   wbExcel.worksheets.add.name = YourNameVar
   set aSheet = wbExcel.worksheets(YourNameVar)
end if

EXTRA! Basic has no true Boolean variables. EXTRA! Basic considers 0 to be FALSE and any other numeric value to be TRUE. Only numeric values can be used as Booleans. Comparison operator expressions always return 0 for FALSE and -1 for TRUE.

The help file is not very helpful, play with the following code to see what I mean.

sub main
    dim bFakeBool as integer
    'uncomment one of the two following lines to test
    'bFakeBool = -1 '"anything <= -1 evaluates true" 
    'bFakeBool = 0  '"anything > -1 evaluates false"
    
    if bFakeBool then
        msgbox "true"
    else 
        msgbox "false"
    end if
    
    if bFakeBool <= -1 then
        msgbox "true"
    else 
        msgbox "false"
    end if
end sub

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[SkipVought]

So sorry. I think in VBA.

EB - no Boolean, no For Each...Next

My bad!

I almost never code in Attachmate Basic, due to its severe limitations. Most of my data starts and ends in Excel, so from Excel VBA, I launch.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]