I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code: Sub MergeSelectedWorkbooks() Dim SummarySheet As Worksheet Dim FolderPath As String Dim SelectedFiles() As Variant Dim NRow As Long Dim FileName As String Dim NFile As Long Dim WorkBk As Workbook
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Description ... 1111E.000000001
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE Sub ImportMSProject() Dim FileToOpen Dim mpApp As MSProject.Application Dim prjmacro As Object 'Identify the File to Open - START FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp") If FileToOpen = False Then Exit Sub End If 'Identify the File to Open - END
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
I'm trying to protect my project so that others can't unhide a sheet I have tagged as veryhidden within VB.
I followed these steps: 1. Select project in the projects window 2. choose Tools 3. Project Properties 4. Protection tab 5. checked lock porject for viewing 6. entered passwords twice 7. clicked ok and saved
On another file this has worked perfectly as I wanted it to.
However on another very large file with multiple VB projects it is not "taking" on the project I need it to.
I can open the file, the VB project and change the setting on any sheet I need to without entering a password.
I'm not close to done yet, but when it's complete I want to distribute the project to people that might not have Excel 2003 or 2007. So, can I package an Excel file with UserForms into a stand-alone tool?
Is there a place on the net where I can work on, or take part of real Excel project ( for training purpose ). If not, do you think that is possible to make such a place were people can learn and practice excel working on simulation of a real Excel projects??????
I have created a spreadsheet in Excel where it gives me a report of how many hours my employees do per week and it seperate them in different categories.
What I would like to do is find a way to match the cell's description with the amount of total hours that were spend on certain project.
So here is the scenario.
In my department there 6 employees that are assigned to work on certain projects in daily basis. These projects are called CRs and to identify them I've added a number after them. So we will have CR0001, CR0002, ect. These are unique projects.More that one employee that could woork at the same project as well on other sections but I am only interested on the projects.
To help you understand what I am trying to do I will give you an example.
John is working on project CR0005, CR0006, CR0001. He has been working on them three projects for the last week.
Here is the summary of the hours:
- 5 hours on CR0005 - 10 hours on CR0006 - 5 hours on CR0001
This information is inputed in Excel spreadsheet Week 1 .....
I have attached a sheet with a sample of how my spreadsheet will look. This might involve a number of steps. I have actuals, forecast, and prior year information (for rolling months) for a number of line items. To the right, I calculate a variance (in total and %). What I want to do is create a list to the right (not sure how to format it) that populates the 10 items from the left with the most variance (units, not %). I would like it to populate the line item from the right as well as the number of units for the variance (columns F and H). Lastly, and I'm not sure if this possible, I might want to just select the items that are greyed out. That is, I would want the line items with the most variance but just from the greyed out lines. If this is not possible without a macro, I don't want to do it.
is if I F8 through the code it works fine. But when I run the code through the button on the spread sheet. It stops at the Format comand. And says "Can't Find Project or Library." Has anyone ran into this issue or know how to solve it.....
I'm a noob to programming in XL, and am teaching myself through a project for manipulating vectors. One thing that has me stumped is that when I open the file in which I'm working, I get a "This action will reset your project..." message.
Is there a way to protect a VBE project using a macro instead of going to the properties and manually typing in a password? I'm using a macro that emails just one worksheet in my workbook (creates a new temp file, emails, and deletes it) but when the new file gets to the recipient the VBE project is unlocked.
I found a way to protect the worksheet before saving the temp file, so the worksheet is protected as it should be, but the project explorer window is completely open. Then anybody can see the passwords I'm using to protect the sheet in the first place. So I need to somehow re-protect the project before it saves and emails.
If i have a table full on excel data, date, names etc, and I have a userform where someone has filled all that in. On that particular userform I was wondering if their's a way I can get it to write each column over to a sample MS project document.
To save me having to manually change it over?
so on clicking a button, what ever's in sheet1 say it writes out out to specific ms project strips, e.g. start date(excel) written out to data(project)
I have written a program in VBA. When I run this program on other computer VB showes "Can't find project or library" error and all of the the common function like "Str" and "Left" and... are not known by BVA. I think I know the reason I have a missing references in my program.
first , can I check it before runing the program by a macro? second, How can I solve the error?
As of a few weeks ago the project explorer in VBA on my laptop has started retaining the information on all files/projects opened during the current Excel session so closing a file in Excel does not remove it from the VBA editor.
Furthermore if a file has been opened several times during an Excel session, it appears several times in the project explorer and editing the “dormant” versions does not impact the currently open copy of the file so the whole thing can get quite confusing and result in changes to not being saved.
Have a bit of code to list all procedures in project to a sheet. Idea from Chip Pearson, adapted with help from Norie. List procedures in module/project Reference is set to MS VBA Extensibility. Thought solved, but trying to use now only lists one procedure in the project Namely the procedure in the module identified here :
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("mdl_comp_pl_ProcedureList").CodeModule
I tried many variations by stopping at
Set VBCodeMod = ThisWorkbook.VBProject
But receive error message
Objest doesn't support property
Sub raw_pl_ProcedureList() 'Uses reference to Microsoft VBA extensibility Dim VBCodeMod As CodeModule Dim StartLine As Long Dim Msg As String Dim ProcName As String Dim wbBook As Workbook Dim wsList As Worksheet Dim rngProcedure As Range Dim intCounter As Integer intCounter = 1 With Application .DisplayAlerts = False .Calculation = xlCalculationManual . ScreenUpdating = False.........................
is it possible to remove a VBE project from the VBE editor? This is a project that is opened as a read-only workbook, and then converted into an addin (essentially just to hide the worksheets). It therefore shows in the VBE editor but is in neither the add-ins or workbooks collections. This file is basically used as a source for data for another charts workbook. I need to clear this out of the VBE editor so as to reload an updated version of this workbook every so often.
I have an educational workbook which I am not familiar with. I cannot open vb project in vbe. When I try to open it says "project is unviewable". As far as I am concerned it should ask a password. But it doesn't. Can somebody tell me why. I am not asking somebody to crack it for me. I just want to learn why vbe doesn't ask a password. I think the only possibility is the creator of the program want nobody to open the code forever.
I have some project that works on my computer and doesn't work on other. When I run it on other computer it fail on functions right and mid, and throw "Can't Find Project Or Library". What the problem and how I may be sure that my project will run on other computers?
Fail on row: ext=Right(sel,1)
My computer: Windows XP, Excel 2003 Failed computer: Windows 2000, Excel 2000