Results 1 to 4 of 4
  1. #1
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368

    select range class issue

    Hi all,

    Hopefully my question is relevant to this forum seeing as though I'm using Access to control Excel. The issue itself is an Excel VBA issue.



    I'm looking to export the contents of multible table to one excel workbook. Here's what I have so far:

    Code:
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    Set xlWB = objXL.Workbooks.Add
    Set xlWS = xlWB.Worksheets(1)
    xlWS.Name = "Tab1"
    Set rst = CurrentDb.OpenRecordset("tblOne")
    xlWS.Range("A1").Select
        For Each fld In rst.Fields
           With objXL.ActiveCell
                .Value = fld.Name
                .Font.Bold = True
                .Font.ColorIndex = 2
                .Interior.Color = 1
                .Offset(0, 1).Select
           End With
       Next fld
    rst.MoveFirst
    xlWS.Range("A2").CopyFromRecordset rst
    This works fine to get the first tab working. I then use:
    Code:
    Set xlWS = xlWB.Worksheets(2)
    xlWS.Name = "Tab2"
    Set rst = CurrentDb.OpenRecordset("tblTwo")
    to move to the next worksheet, rename it, and select the next recordset to be pasted in. This also works fine. However, when I then try:
    Code:
    xlWS.Range("A1").Select
        For Each fld In rst.Fields
           With objXL.ActiveCell
                .Value = fld.Name
                .Font.Bold = True
                .Font.ColorIndex = 2
                .Interior.Color = 1
                .Offset(0, 1).Select
           End With
       Next fld
    rst.MoveFirst
    xlWS.Range("A2").CopyFromRecordset rst
    again, I get a runtime error "Select method of Range class failed." If I remove the section for the field headers, the CopyFromRecordset works fine as well and pastes in the proper data. I just can't seem to get the headers in. I've tried closing the recordset and setting it to Nothing prior to opening the second recordset, hoping to refresh things but it's not working. Help please.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Got the fix. Looks like changing the worksheet and renaming it doesnt activate the worksheet. I had to explicitly activate it by Sheets("name").Activate prior to referencing cell A1 for the second iteration.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,728

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Yea. It's helpful. More than one way to skin a cat, of course. My issue was assuming that
    Set xlWS = xlWB.Worksheets(2) would also activate the worksheet as well. It obviously didn't.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Managing a CPR class
    By DiPietro14 in forum General Chat
    Replies: 1
    Last Post: 02-06-2012, 06:02 PM
  2. Excel Automation Select Range Only with Data
    By orcinus in forum Programming
    Replies: 3
    Last Post: 02-09-2011, 01:03 PM
  3. Class Method
    By AndreT in forum Programming
    Replies: 3
    Last Post: 01-20-2011, 02:18 AM
  4. Class in Access
    By Huddle in forum Access
    Replies: 2
    Last Post: 07-15-2010, 04:08 PM
  5. In over my head with a database class
    By fixittech in forum Database Design
    Replies: 3
    Last Post: 01-22-2010, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums