27 august 2010 19:35
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
Set xlSheet = xlBook.Worksheets("NameofWorksheet")
xlSheet = xlBook.Worksheets("NameofWorksheet")
Thanks for any help in advance!
27 august 2010 20:49Moderator
27 august 2010 21:10
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.
- Marcat ca răspuns de Jim CorbinModerator 27 august 2010 23:33