Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Excel Worksheet from Access (2000)

    I have this fancy access database that stores a bunch of junk in it and it does some great calculations and such. It's pretty high tech.

    My boss, however, is not high tech. She needs numbers in a pretty Excel workbook that she can print & e-mail, & whatnot.

    I have code to open excel & populate the worksheet, but I can not for the life of me figure out how to select a tab by name, instead of index.

    Any suggestions?
    <pre> Dim db As DAO.Database
    Set db = CurrentDb

    Dim appExcel As Excel.Application
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open "C:Cool Workbook2.xls"

    appExcel.Visible = True

    Dim rstOut As DAO.Recordset
    Set rstOut = db.OpenRecordset("SELeCT * from tblQACs", dbOpenForwardOnly)

    While Not rstOut.EOF

    appExcel.Sheets(rstOut!ID).Select</pre>


    How do I change the last line of the code to use the name of the tab rather than the index?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Excel Worksheet from Access (2000)

    You should be able to use

    appExcel.Sheets("SheetName").Select

    or

    Dim strSheetName As String
    strSheetName = ...
    appExcel.Sheets(strSheetName).Select

Posting Permissions

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