Conectare
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Excel Automation in Project

Answered Excel Automation in Project

  • 27 august 2010 19:35
     
     
    Sign in pentru a vota
    0

    I am trying to change the active excel worksheet dynamically within a project macro.  I can’t access any of the sheets programmatically unless the excel workbook opens to the one that I am specifying.  I don’t get any errors it just ignores the set xlsheet assignment.  Here is the code that I am using:

    Dim xlApp As Excel.Application

    Dim xlBook As Excel.Workbook

    Dim xlSheet As Excel.Worksheet

    Dim xlRange As Excel.Range

     

    Set xlApp = Excel.Application

    Set xlBook = xlApp.Workbooks.Open("c:\my_data\Excel to Project\..")

    xlApp.Visible = True

     

     

    ‘xlBook.Activate                                                                                              

    Set xlSheet = xlBook.Worksheets("NameofWorksheet")           

     xlSheet = xlBook.Worksheets("NameofWorksheet")

     

    Thanks for any help in advance!

     

Toate mesajele

  • 27 august 2010 20:49
    Moderator
     
     
    Sign in pentru a vota
    0

    What does xlsheet.activate do for you?

    One way to learn is to record a macro in Excel of what you want to do.

    The one and only Project VBA Book Rod Gill Project Management
     
  • 27 august 2010 21:10
     
     Răspuns
    Sign in pentru a vota
    0

    Saintfan4life,

    The last line in your code does nothing. You've already created the xlSheet object, now just activate it as Rod suggested.

    Just as a point of reference, you don't need to have Excel visible to load data into it from Project. Matter of fact, I normally open Excel and then set it's visible property to false. Your code run faster if you have a lot of data to transfer. When it's all done, you can make Excel visible.

    John