if (excel sheet exists) ? create

[Monoxide3009]

Hey All,

TL;DR - 1) how do you test if a sheet name exists, 2) how do you create a sheet - I'm new, the more details/descriptions you provide the better.

I am trying to figure out (what I assume to be) a simple function but I am drawing a blank. I am working with 2 workbooks at a time, and I need a function for if the master workbook does not contain a sheet with the sheet name from the sub workbook, it should create it.

I already have the sheet name from the sub workbook as a variable %SheetName%, I just need to confirm if the master workbook has that sheet.

Below is my subroutine - im new, so bare with me =)

First section of the subroutine is where I need the test for the sheet. The rest is checking if the row is blank; if so, write the data variables (which also could be done a lot more elegantly, but it works). Please let me know if you think you can help with this function.

Code: Select all

mENTER: ; process into excel master file

{ ; If sheet does not exist, create it

}

ROWm++

Loop,
{
LineT := wrkbk1.Sheets(SheetName).Range("A"ROWm).Text

if (LineT = "")
{
break
}
else
{
ROWm++
}
}

wrkbk1.Sheets(SheetName).Range("A"ROWm).Characters.Text := ACT
wrkbk1.Sheets(SheetName).Range("B"ROWm).Characters.Text := MAWB
wrkbk1.Sheets(SheetName).Range("C"ROWm).Characters.Text := HAWB

ROWm = ; reset row test

Return

[FanaticGuru]

Hey All,

TL;DR - 1) how do you test if a sheet name exists, 2) how do you create a sheet - I'm new, the more details/descriptions you provide the better.

Code: Select all

xlApp := ComObjActive("Excel.Application")
SheetName := "FanaticGuru"
try
	xlSheet := xlApp.Sheets(SheetName)
catch
{
	xlSheet := xlApp.Sheets.Add
	xlSheet.Name := SheetName
}
xlSheet.Range("B2").Value := "Your Welcome!"

FG

[Monoxide3009]

FG

Thanks. I have seen something "similar" to this, but I wasnt able to decipher it. This looks clear and to the point, I think I should be good.

Any suggestions on finding the next blank cell when dealing with multiple sheets? What I have 'works' but after a few dozen rows, it slows down drastically having to check each variable when it compounds on itself starting from 1 each time.


EDIT: Works perfectly. Thank you.

[FanaticGuru]

Any suggestions on finding the next blank cell when dealing with multiple sheets? What I have 'works' but after a few dozen rows, it slows down drastically having to check each variable when it compounds on itself starting from 1 each time.

I assume you are talking about find the first blank cell below all data in a column. This demonstrates that.

Code: Select all

	xlApp := ComObjActive("Excel.Application")
	Column := 2 ; second column or B
	xlCell_EmptyBelowData := xlApp.Columns(Column).Find("*",,,,,2).Offset(1,0)	; get cell just below last cell with anything in it
	if  !xlCell_EmptyBelowData.Address	; no cell found with anything in it, column is blank
		xlCell_EmptyBelowData := xlApp.Cells(1,Column)	; default to first cell
	xlCell_EmptyBelowData.Value := "Here"

FG

[Monoxide3009]

Took me about 2 hours to really figure out what was going on here; once I played with it a while, I got it.

Works like a charm. Cut a LOT of inefficiency out of my script.

Thanks, FG, you Fatastic Guru.