Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Run time error - Adding Workshet
I am using following procedure for adding worksheets in workbook, first time following function it works perfect and all the tab created alphabatically with the different name(abc, mno, stv, xys), but second time it give me following error
Method 'Move' of Object '_worksheet' failed; If I comment following line, it wont give me error, it run perfectly except workbook tab name add in reverse chronolofical order like (xys, stv, mno, abc) ExcelSht.Move befo=Worksheets(Worksheets.Count) Lte me know whats wong I am doing? Or is there any way that I can sort worksheet alphabatically. *-------------------------------------------------------- Dim ExcelApp As New Excel.Application Dim ExcelSht As New Excel.Worksheet Dim ExcelWkb As New Excel.Workbook Dim rsManagersDis As New ADODB.Recordset Set ExcelApp = CreateObject("Excel.Application") MyExcel = True ExcelApp.Visible = True Set ExcelWkb = ExcelApp.Workbooks.Add rsManagersDist.ActiveConnection = CurrentProject.Connection rsManagersDist.CursorLocation = adUseClient rsManagersDist.Open "Select distinct [Manager Name] rom tmpPMExcel order by [Manager Name] ) " Do Until rsManagersDist.EOF j2 = rsManagersDist("expr1") Set ExcelSht = ExcelWkb.Worksheets.Add ExcelWkb.Worksheets("Sheet1").Visible = xlSheetVeryHidden ExcelSht.Visible = xlSheetVisible ExcelSht.Move befo=Worksheets(Worksheets.Count) ExcelSht.name = j2 ExcelSht.PageSetup.PrintGridlines = True ExcelSht.PageSetup.Orientation = xlPortrait rsManagersDist.MoveNext Loop *-------------------------------------------------------- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 02/29/2004 |
#2
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Run time error - Adding Workshet
Hi
for sorting worksheets you may have a look at http://www.cpearson.com/excel/sortws.htm -- Regards Frank Kabel Frankfurt, Germany Ashish Kanoongo wrote: I am using following procedure for adding worksheets in workbook, first time following function it works perfect and all the tab created alphabatically with the different name(abc, mno, stv, xys), but second time it give me following error Method 'Move' of Object '_worksheet' failed; If I comment following line, it wont give me error, it run perfectly except workbook tab name add in reverse chronolofical order like (xys, stv, mno, abc) ExcelSht.Move befo=Worksheets(Worksheets.Count) Lte me know whats wong I am doing? Or is there any way that I can sort worksheet alphabatically. *-------------------------------------------------------- Dim ExcelApp As New Excel.Application Dim ExcelSht As New Excel.Worksheet Dim ExcelWkb As New Excel.Workbook Dim rsManagersDis As New ADODB.Recordset Set ExcelApp = CreateObject("Excel.Application") MyExcel = True ExcelApp.Visible = True Set ExcelWkb = ExcelApp.Workbooks.Add rsManagersDist.ActiveConnection = CurrentProject.Connection rsManagersDist.CursorLocation = adUseClient rsManagersDist.Open "Select distinct [Manager Name] rom tmpPMExcel order by [Manager Name] ) " Do Until rsManagersDist.EOF j2 = rsManagersDist("expr1") Set ExcelSht = ExcelWkb.Worksheets.Add ExcelWkb.Worksheets("Sheet1").Visible = xlSheetVeryHidden ExcelSht.Visible = xlSheetVisible ExcelSht.Move befo=Worksheets(Worksheets.Count) ExcelSht.name = j2 ExcelSht.PageSetup.PrintGridlines = True ExcelSht.PageSetup.Orientation = xlPortrait rsManagersDist.MoveNext Loop *-------------------------------------------------------- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 02/29/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I expand/stretch one cell in an Excel workshet. | Excel Discussion (Misc queries) | |||
excel workshet can not open in desktop | Excel Discussion (Misc queries) | |||
Automatic extend row in workshet. | New Users to Excel | |||
Automatic extend row in workshet. | New Users to Excel | |||
Run time error '-2147467259 (80004005) adding icon | Excel Discussion (Misc queries) |