Excel

i have very simple question about EXCEL automation. I want to ADD worksheets in the Workbook and also want to give names to these worksheet. :blush:

After Adding these worksheets also want to use (reference) these sheets one by one.

VB Code:

ActiveWorkbook.Sheets.Add , , 20
    For iLoop = 1 to 20
        'ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
        Set wSheet = ActiveWorkbook.ActiveSheet
        wSheet.Name = sName & Format(iLoop, "00")
    Next

Problems that i am facing is that I could not get the reference of each sheet one by one ... so i can write data in that sheets.

And MOST important after running my code, when i directly open the EXCEL (2000) it display me 20 sheets, even before running this code, it was displaying only three sheets as normally dose. And i do not want to change the default settings of the EXCEL.

Thanx to all in advance.....

Well i resolved one problem :wave: ... That is Number of Sheets as Default. Actually i write the following code which change the default setting of Excel.

VB Code:

AppExcel.SheetsInNewWorkbook = 40

But i am still looking for
1. Getting reference of Each WorkSheet, so i can write data on it.
2. Change the name of WorkSheets

Thanx to all in advance....

Well i am able to solve my problem :bigyello: . Thanx to all. :blush:

Now i am getting a new type of error :mad:

Actually i am fromatting my Cells in the worksheet.... my code run fine for the first Worksheet but when same code run for the second or so on it give the error... Even in sheet data is written correctly....

VB Code:

Set wBook = AppExcel.Workbooks.Add
ActiveWorkbook.Sheets.Add , , 30
    Dim Count As Integer
    For Count = 1 to 30
 
        Set wSheet = ActiveWorkbook.Sheets(Count)
        wSheet.Name = Format(iDate, "dd-mm-yyyy")
 
        ' Column Headings
        Dim iNumberOfFields As Single
        iNumberOfFields = 6         'Fields to be Displayed
        
        Dim iHeadingStartRow, iHeadingStartCol As Single
        iHeadingStartRow = 3
        iHeadingStartCol = 2
        
        wSheet.Cells(iHeadingStartRow, iHeadingStartCol + 0).Value = "Colum 1"
        wSheet.Cells(iHeadingStartRow, iHeadingStartCol + 1).Value = "Colum 12"
        wSheet.Cells(iHeadingStartRow, iHeadingStartCol + 2).Value = "Colum 3"
        wSheet.Cells(iHeadingStartRow, iHeadingStartCol + 3).Value = "Colum 4"
        wSheet.Cells(iHeadingStartRow, iHeadingStartCol + 4).Value = "Colum 5"
        wSheet.Cells(iHeadingStartRow, iHeadingStartCol + 5).Value = "Colum 6"
            
        ' Column Data
        Dim iDataStartRow, iDataStartCol As Single
        iDataStartRow = iHeadingStartRow + 1
        iDataStartCol = iHeadingStartCol
        
        Dim iRow, iCol As Long
        For iRow = iDataStartRow To 5 + iDataStartRow - 1    'Record Count + 3
            For iCol = iDataStartCol To iNumberOfFields + iDataStartCol - 1 'Number of fields
                wSheet.Cells(iRow, iCol).Value = iRow + iCol
            Next
        Next
        
        'Format Colum Heading
        Debug.Print iHeadingStartRow & vbTab & iHeadingStartCol & vbTab & iHeadingStartCol + iNumberOfFields
 
' Error in the following line. BUT it WORK fine for the FIRST SHeet
        Range(wSheet.Cells(iHeadingStartRow, iHeadingStartCol), wSheet.Cells(iHeadingStartRow, iHeadingStartCol + iNumberOfFields)).Select
'.....
Next

Values printed in Debug window are
3 2 8
3 2 8

which means that values are correct....

Thanx to all in advance....

Sorry i forget to write the error message

message is "Select method of Range class failed"
message number is " 1004 "

Oaky... Thanx to all... i am able to resolve the problem. I missed very silly point. :D

VB Code:

wSheet.Name = Format(iDate, "dd-mm-yyyy")
' Following line is the key 
wSheet.Activate

I guess that the above is what I would call "A Monologue"!