Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: new sheets in vba

    I have created an Excel spreadsheet in a procedure in Access. Here it is:

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    (Theres more to it but you get the point)

    I need to add sheets to it. It currently is created with the defaulted 3 sheets per workbook. I need at least 6. Does anyone know how to add sheets? I have tried this:

    Sheets.Add Count:=3, After:=Sheets(3)

    (in all kinds of ways) with no luck..

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try this:

    Worksheets.Add after:=Worksheets(Worksheets.Count)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Heres more...

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Application.workbooks.Add

    Worksheets.Add After:=Sheets(Sheets.Count), Count:=3

    Set objActiveWkb = objXL.Application.ActiveWorkBook


    This format is what Access seemed to accept- I not sure if it did it or not.
    Right now i can refer to worksheet(1), worksheet(2) and Worksheet(3) without any problem but when i try to refer to worksheet(4)- i get an "out of range" error.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How many sheets exist before you run the add statement?

    Try debugging Sheets.Count before and after the statement. I'm not familiar with the sheets object, and I don't know why access would reject it. The line of code I supplied was direct from a live application.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •