SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 4 of 4
  1. #1
    Member
    Join Date
    Jan 2008
    Posts
    284
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    How to add a new sheet every time i run the script

    Below code is creating a sheet for the first time and then from second time it is overwriting the existing sheet.Instead i need to add a new sheet to the same work book if exists and name it.


    mth = Month(now)
    dy= day(now)
    hr= hour(now)
    mt=minute(now)


    Set suitExcelobj= CreateObject("Excel.Application")
    Set myFSO = CreateObject("Scripting.filesystemobject")
    If myFSO.FileExists("C:\Testdata.xls") Then
    msgbox "file found"
    Set suitExcelWorkbook=suitExcelobj.Workbooks.Open("C:\ Testdata.xls")
    Else
    msgbox "file not found"
    Set suitExcelWorkbook=suitExcelobj.Workbooks.Add()
    suitExcelWorkbook.saveas("C:\Testdata.xls")
    End If

    Set suitExcelWorkSheet=suitExcelWorkbook.Worksheets(1)
    suitExcelWorkSheet.name=mth&dy&hr&mt

    suitExcelWorkbook.close()
    set suitExcelWorkbook=nothing
    Set suitExcelobj=nothing

  2. #2
    Super Member QAGanesh's Avatar
    Join Date
    Sep 2010
    Posts
    1,898
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to add a new sheet every time i run the script

    U can check the sheet count and then use the same.

    use sheetCnt = suitExcelWorkbook.Sheets.Count to retrieve the sheetcount and then use

    Set suitExcelWorkSheet=suitExcelWorkbook.Worksheets(sh eetCnt)
    Thanks,
    Ganesh Iyer
    Winds of Change

  3. #3
    Member
    Join Date
    Jan 2008
    Posts
    284
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to add a new sheet every time i run the script

    Thanks for the response.

    sheetCnt = suitExcelWorkbook.Sheets.Count to retrieve the sheetcount and then use

    Set suitExcelWorkSheet=suitExcelWorkbook.Worksheets(sh eetCnt)

    with the above logic,

    It creates a new sheet for the first time as 891154(monthdayhrmt format) but the results are writing to sheet 1 as it is active.I need to make sheet 891154 as active and write my results there.

    And also when i rerun the script it overwrites 891154 to 891156 which is not correct in my case.it should create a new sheet with name 891156 and write results to that sheet

  4. #4
    Super Member QAGanesh's Avatar
    Join Date
    Sep 2010
    Posts
    1,898
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to add a new sheet every time i run the script

    What if using this

    Set suitExcelWorkSheet=suitExcelWorkbook.Sheets.Item(s heetCnt)
    Thanks,
    Ganesh Iyer
    Winds of Change

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 07:27 AM.

Copyright BetaSoft Inc.