Dean,Originally Posted by DcD
The code as I gave you doesn't need that reference set. If you uncheck it as Dennis said, then distribute it you should get no problems.
Dean,Originally Posted by DcD
The code as I gave you doesn't need that reference set. If you uncheck it as Dennis said, then distribute it you should get no problems.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Semi-guilty!Originally Posted by firefytr
That part of the code was not mine, I just added the code to overcome the problem. I should change it though I agree
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Dean - Use the so called "late binding"
Bob - Next time change it as Zack seems to have more sparetime then the rest of the world
Zack - Get a life
Kind regards,
Dennis
Dennis,Originally Posted by XL-Dennis
Now that Excel 12 will have 1M+ rows, I might go on a new evangelising crusade to persuade people not to use them all, using 65536 rows for my end-point rather than Rows.Count
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Bob - Nothing wrong with Rows.Count if we will be using 2 x CPUs and 8 GB RAM
Kind regards,
Dennis
ROFL!!! Dennnis, you made my day. Thank you my friend. LOL!
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Dennis,Originally Posted by XL-Dennis
Apart from the fact that I only have 1 CPU and 512Mb RAM (which will struggle with Vista as I understand it), my crusade is aimed at not using Excel when one should be using a database. Too many people do it already, and with more than 1M rows, it will be even worse. Still, lots of work for the consultants.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Bob,
Then we are at least two with that opinion.my crusade is aimed at not using Excel when one should be using a database.
During the years I have seen so many poor solutions due to the overload in workbooks.
I'm also supporting and recommend to use simple textfiles to store data in.
After all, Excel is still a spreadsheet and not a DBMS
As for Vista it will raise the requirements a lot on RAM and graphic card as well.
Kind regards,
Dennis
Hi Xld & Everybody.
Thanks for all the information. The code that was provided from Xld that allowed me to deselect the reference for microsoft project doesnt allow me to update the MS Project with Changes. Where as the old code from Dennis did. It doesnt seem to save any changes to existing data now.
Is there something that im missing?
Thanks.
Dean
Dean,Originally Posted by DcD
Can you post a zip file with the xls amd MS Project files to help us test it?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi Xld,
Here is the xls and the mpp file as requested. Hope this helps.
Excuse the mess, as im learning...
Thanks once again.
Dean
Hi Xld,
Has there been any luck. Ive tried changing some things, but to no avail. Hopefully you've had more luck
Thanks,
Dean
Hey I have a similar problem.
I have two tabs, one is Refine and Add Factors and the other is Inventory. Im am running a nested loop and when I get to the second interation of the outter loop a problem occurs. The task is being added but the outline level and work associated with that task is being kicked back up to the top and rewriting the values associated to the other tasks.
Here is the code:
Private Sub cmdGenerate_Click()
'Variables surrounding applications
Dim wbEstimates As Workbook
Dim wsRefineAddFactors As Worksheet
Dim wsInventory As Worksheet
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
'Variables surrounding Refine or Add Factors Tab
Dim vaFactorNames As Variant
Dim vaResources As Variant
Dim vaTasks As Variant
Dim vaOutlineLevel As Variant
Dim vaPhase As Variant
Dim vaWBS As Variant
Dim lnEnd As Long, lnRefineAddCounter As Long
'Variables surrounding Inventory Tab
Dim vaFactors As Variant
Dim vaRequirements As Variant
Dim vaAnalysisDesign As Variant
Dim vaBuildUnitTest As Variant
Dim lnLast As Long, lnInventoryCounter As Long
Dim vaID As Variant
vaID = 1
Set wbEstimates = ThisWorkbook
Set wsInventory = wbEstimates.Worksheets(2)
'The parenthesis is the index of what worksheet needs to grabbed
Set wsRefineAddFactors = wbEstimates.Worksheets(1)
'This section is storing all the values on the Refine Add Factors Tab from the fields to
'the corresponding variables
With wsRefineAddFactors
lnEnd = .Range("C65536").End(xlUp).Row
vaResources = .Range("V15:V" & lnEnd).Value
vaTasks = .Range("J15:J" & lnEnd).Value
vaOutlineLevel = .Range("I15:I" & lnEnd).Value
vaFactorNames = .Range("C15:C" & lnEnd).Value
vaPhase = .Range("G15:G" & lnEnd).Value
vaWBS = .Range("H15:H" & lnEnd).Value
End With
'This section is storing all the values on the Inventory Tab from the fields to
'the corresponding variables
With wsInventory
lnLast = .Range("D65536").End(xlUp).Row
vaRequirements = .Range("R3:R" & lnLast).Value
vaAnalysisDesign = .Range("S3:S" & lnLast).Value
vaBuildUnitTest = .Range("T3:T" & lnLast).Value
vaFactors = .Range("D3" & lnLast).Value
End With
'Instantiate and open MS Project and the project.
Set prApp = New MSProject.Application
prApp.FileOpen "D:\Documents and Settings\adrian.martinez\Desktop\ACN\Wells Fargo\Estimating\CORE PMO Estimator\Work Plan Generation\New Utility\Workplan Generation 20070523 v 1 0.mpp"
Set prProject = prApp.ActiveProject
With prProject
For lnInventoryCounter = 1 To UBound(vaFactors)
For lnRefineAddCounter = 1 To UBound(vaTasks)
If vaFactors(lnInventoryCounter, 1) = vaFactorNames(lnRefineAddCounter, 1) Then
.Tasks.Add vaTasks(lnRefineAddCounter, 1)
With .Tasks(vaTasks(lnRefineAddCounter, 1)) ''''''''I know the problem is happening right here because the counter is going back to 1
.ResourceNames = vaResources(lnRefineAddCounter, 1)
.OutlineLevel = vaOutlineLevel(lnRefineAddCounter, 1)
Select Case vaFactorNames(lnRefineAddCounter, 1) & vaPhase(lnRefineAddCounter, 1) & vaWBS(lnRefineAddCounter, 1)
Case vaFactors(lnInventoryCounter, 1) & "Requirements" & "Task1"
.Work = vaRequirements(lnInventoryCounter, 1) * 60
Case vaFactors(lnInventoryCounter, 1) & "Analysis and Design" & "Task1"
.Work = vaAnalysisDesign(lnInventoryCounter, 1) * 60
Case vaFactors(lnInventoryCounter, 1) & "Build and Unit Test" & "Task1"
.Work = vaBuildUnitTest(lnInventoryCounter, 1) * 60
Case Else
End Select
End With
End If
Next lnRefineAddCounter
Next lnInventoryCounter
End With
End Sub
Last edited by Phatie17; 05-24-2007 at 08:35 AM.
Im sorry here is the correct code
Private Sub cmdGenerate_Click()
'Variables surrounding applications
Dim wbEstimates As Workbook
Dim wsRefineAddFactors As Worksheet
Dim wsInventory As Worksheet
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
'Variables surrounding Refine or Add Factors Tab
Dim vaFactorNames As Variant
Dim vaResources As Variant
Dim vaTasks As Variant
Dim vaOutlineLevel As Variant
Dim vaPhase As Variant
Dim vaWBS As Variant
Dim lnEnd As Long, lnRefineAddCounter As Long
'Variables surrounding Inventory Tab
Dim vaFactors As Variant
Dim vaRequirements As Variant
Dim vaAnalysisDesign As Variant
Dim vaBuildUnitTest As Variant
Dim lnLast As Long, lnInventoryCounter As Long
Dim vaID As Variant
vaID = 1
Set wbEstimates = ThisWorkbook
Set wsInventory = wbEstimates.Worksheets(2)
'The parenthesis is the index of what worksheet needs to grabbed
Set wsRefineAddFactors = wbEstimates.Worksheets(1)
'This section is storing all the values on the Refine Add Factors Tab from the fields to
'the corresponding variables
With wsRefineAddFactors
lnEnd = .Range("C65536").End(xlUp).Row
vaResources = .Range("V15:V" & lnEnd).Value
vaTasks = .Range("J15:J" & lnEnd).Value
vaOutlineLevel = .Range("I15:I" & lnEnd).Value
vaFactorNames = .Range("C15:C" & lnEnd).Value
vaPhase = .Range("G15:G" & lnEnd).Value
vaWBS = .Range("H15:H" & lnEnd).Value
End With
'This section is storing all the values on the Inventory Tab from the fields to
'the corresponding variables
With wsInventory
lnLast = .Range("D65536").End(xlUp).Row
vaRequirements = .Range("R3:R" & lnLast).Value
vaAnalysisDesign = .Range("S3:S" & lnLast).Value
vaBuildUnitTest = .Range("T3:T" & lnLast).Value
vaFactors = .Range("D3" & lnLast).Value
End With
'Instantiate and open MS Project and the project.
Set prApp = New MSProject.Application
prApp.FileOpen "D:\Documents and Settings\adrian.martinez\Desktop\ACN\Wells Fargo\Estimating\CORE PMO Estimator\Work Plan Generation\New Utility\Workplan Generation 20070523 v 1 0.mpp"
Set prProject = prApp.ActiveProject
With prProject
For lnInventoryCounter = 1 To UBound(vaFactors)
For lnRefineAddCounter = 1 To UBound(vaTasks)
If vaFactors(lnInventoryCounter, 1) = vaFactorNames(lnRefineAddCounter, 1) Then
.Tasks.Add vaTasks(lnRefineAddCounter, 1)
With .Tasks(vaTasks(lnRefineAddCounter, 1)) ''''''''I know the problem is happening right here because the counter is going back to 1
.ResourceNames = vaResources(lnRefineAddCounter, 1)
.OutlineLevel = vaOutlineLevel(lnRefineAddCounter, 1)
Select Case vaFactorNames(lnRefineAddCounter, 1) & vaPhase(lnRefineAddCounter, 1) & vaWBS(lnRefineAddCounter, 1)
Case vaFactors(lnInventoryCounter, 1) & "Requirements" & "Task1"
.Work = vaRequirements(lnInventoryCounter, 1) * 60
Case vaFactors(lnInventoryCounter, 1) & "Analysis and Design" & "Task1"
.Work = vaAnalysisDesign(lnInventoryCounter, 1) * 60
Case vaFactors(lnInventoryCounter, 1) & "Build and Unit Test" & "Task1"
.Work = vaBuildUnitTest(lnInventoryCounter, 1) * 60
Case Else
End Select
End With
End If
Next lnRefineAddCounter
Next lnInventoryCounter
End With
End Sub
Can you post your workbook to save us the work re data.
Here it is. Thanks for your help
Hello Folks,
I am a beginner to vb and am trying to do a similar task to this post and would greatly appreciate your help.
I have extracted actual dates and forecasted dated across an excel row and am trying to populate it into a ms project template where the task names are already populated.
Dim strCellA, strCellB As String
Dim iRow, iCol, k, k2, Col As Integer
Dim strResult(1 To 300) ' forecasted dated
Dim strResult2(1 To 300) 'Actualized dates
'Get Results-> Finish dates
strResult(1) = strCellA
k = 2
For iCol = 64 To 236 Step 3
If Not ((iCol = 187) Or (iCol = 196)) Then
strResult(k) = Cells(iRow, iCol)
k = k + 1
End If
Next iCol
'Get Results2->Actualized Dates
strResult2(1) = strCellB
k2 = 2
For iCol = 65 To 236 Step 3
If Not ((iCol = 188) Or (iCol = 197)) Then
strResult2(k2) = Cells(iRow, iCol)
k2 = k2 + 1
End If
Next iCol
Question: I think it needs some kind of loop to be entered into project fields
.Finish = strCellA-> this is not working
.ActualFinish = strCellB-> this is not working
I once again want to thank you in advance for your help
Regards
Bcry
Originally Posted by XL-Dennis
hello,
i tried this macro but it gives me problem with:
.Duration = lnTime & " days"
it seems that it dosent like & "days".
in Excel i have a number that defines the duration.
any help to solve this problem?
interresting .......
Hey guys,I just stumpled upon this very helpful thread with code that almost perfectly solves my problem - but somehow the code doesn't work out properly, as I get the "type mismatch" error as a result of the line .Duration = vaDuration(lnCounter, 1) & " days"I would really appreciate every piece of advice as to where this might come from.Thanks a lot,Chris