Copy Excel Data To MS Project Fields
Apr 28, 2014
Writing a macro in excel where you can use the data in your excel file to copy data to microsoft project.
I have an excel file of task name, deadline, time remaining, and assigned to and I want to copy those 4 columns to a correlating column in project. So basically I would loop through these fields on each line til I reach a blank which could be entering a varied amount of tasks.
The full macro would change ms project start date to today's date then copy the 4 columns from excel to project to add a new task for each line in my file and then save the project file.
I've seen some links on macro with project but most are more advanced then this or are exporting data from project to excel.
View 1 Replies
ADVERTISEMENT
Sep 5, 2013
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
[code]....
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 Number
Project Description
...
1111E.000000001
[code]....
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.
View 4 Replies
View Related
Jul 30, 2013
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
[code].....
View 9 Replies
View Related
Jul 11, 2014
I need to be able to export a project list to Excel and overwrite the contents in an existing Summary Report with the exported list. Is this possible? I am using SharePoint 2013 and Excel 2010.
View 4 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
Oct 2, 2012
Is it possible to fill an embedded pdf (AcroPDF) fields with data from excel?
For example: I have an embedded pdf file, it has a field name "NAMEFIELD", on the click of a commandbutton, I would like Sheets("Sheet1").Range("A1").Value to be input into the PDF file text field "NAMEFIELD".
View 1 Replies
View Related
Feb 20, 2014
I have a spreadsheet with a heap of raw data in it. I need to perform a search based on a customer name, then return certain columns from the raw data IF the customer name is matched in one column, AND a special flag name is matched in a second column. The customer name is always a single entry for the purpose of the query, however the special flag must be checked against a possible list of values in a table. I know this is hard to explain, and I can't add attachments to my posts, so I have created a sample document and placed in in dropbox:
[URL] ........
View 9 Replies
View Related
Feb 8, 2012
I am in the process of comparing the data between 2 different sheets and the data on each sheet contains 2 columns of information. I am trying to find a way to compare one sheet to that same item on the second sheet and see if anything is missing, however the second sheet contains more information between the two columns and the rows are never the same during my comparison. I've tried Match, Lookup, Indirect, and if/then with an And functions and because the rows do not stay the same the data is not accurate. Here is a brief example of what I am working on:
SHEET 1
A B
1 Red Delicious Apple 125 Count Apple
2 Red Delicious Apple 125 Count Fresh Fruit
[Code]....
So in these comparisons, the names in Column A match identically, so if I try to say take the name from sheet 1 and match to that in sheet 2 and if the wording in Column B matches then put a "Yes" value. But you can see on Sheet 2 by the time it gets to rows 14, 15, 16, the Baby Food Chicken has a 3rd item in Column B in comaprison to Sheet 1, so any type of match doesn't work.
I am using Excel 2003.
View 9 Replies
View Related
Dec 4, 2012
- The macro should then try to find this data on the worksheet "Sheet2" in the same workbook. This data on Sheet2 is essentially a table with four columns "Number" (Col A), "Country" (Col B), "Consol" (Col C) and "Bypass" (Col D) but can have around 70k rows
- It should look for Number first, and if its not found, the macro should insert the message "Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it matches the value input for Country on Sheet1, then it insert the message "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "Y" but the Bypass field input on Sheet1 has "N", then put "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "N" but the Bypass field input on Sheet1 has "Y", then put "Multiple Records - Bypass - Do Not Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows that have a Bypass value of "N" and it does match the value input for Country on Sheet1 then put "Multiple Records - No Need to Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is different for all the rows that have a Bypass value of "N", then put "Multiple Records - Refer" in cell A8 on Sheet1
[URL] ....
TestBook.xlsm
View 6 Replies
View Related
Aug 4, 2014
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.
View 5 Replies
View Related
Apr 27, 2007
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies
View Related
Oct 21, 2011
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)
View 8 Replies
View Related
Nov 22, 2009
I'm brand new to programming in Excel, and currently building a system that will help me trade equity options (similar to trading stocks). So far, I've put together the ranking system I want to use based on a live data feed that updates in real-time (during market hours). For every possible trade in my 'universe' (currently about 600), Excel provides a live score (based on my formulas and weightings) as well as prices and other info about the trade (options strikes, symbols, expected return, etc.).
What I'd like Excel to be able to do is keep a separate sheet where a trade (row) that scores over an arbitrary threshold, say, 100 'points,' is recorded as a static line (instead of continuing to dynamically update), like a snapshot of that line. I'll use this list of trades to submit to my broker for trading.
If it was as simple as a 'triggered' copy - paste special - values, that would do just fine. The trigger would also need to see if that trade has already been recorded for the day, so I don't get an infinite list of the same trade.
After a trade makes it into this list, I'll need to compare it to what I already have in my portfolio (which I'll also need to build) so I don't become too heavily weighted in any one trade. (Just thought I'd let you know where this will be going, too) ...
View 14 Replies
View Related
Nov 24, 2013
Uses excel 2010 to track the project in the form of project status report. Basically it has to look simple yet tracking should be in the form of progress bar etc.. Our aim is to track a 3month project.
View 1 Replies
View Related
Aug 30, 2012
I'm a basic user of excel 2003. I recorded a macro that copy a teacher absent schedule to a daily cover schedule as follow:
Sub ABS_M1()
'
' ABS_M1 Macro
' Absent teacher monday 8/29/2012 by Oscar
' Daily cover schedule
'
Range("A65:J67").Select
Selection.Copy
Sheets("Covers").Select
Range("B5").Select
ActiveSheet.Paste
End Sub
Using the same VBA: What function should I use to make the Sub ABS_M1 move down 4 rows to a new range on the cover sheet if the first Range ("B5") is already used and so on?
View 4 Replies
View Related
Aug 15, 2013
Why does this code no longer work? It gives me the error code Cannot find project or library and MyMsg = is highlighted in blue. This worked in 2003, but does not seem to like 2007 version of excel.
Sub Send2()
'This is the "Send to XX" button
MyMsg = "Did you remember to name and save this file to your computer?"
Response = MsgBox(MyMsg, vbYesNo, Attention)
Select Case Response
Case Is = vbNo
[Code] .....
View 9 Replies
View Related
Jun 8, 2014
Is there any Software / Excel Add-in to provide VBA Project Documentation? I'm looking for something that can tell me which code does what? Which codes and functions are related. What are the references (with Other MS Office products / foreign-objects if any). How many codes and code names
View 8 Replies
View Related
Mar 20, 2014
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
Formula where A1 is the Start Date:
Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1
View 2 Replies
View Related
Aug 28, 2007
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].
View 4 Replies
View Related
May 25, 2014
It works fine with several versions of Excel (2003, 2010, and 2013). But the problem arises when I move from Windows XP to Windows 7 or 8.
I use a scanner to scan barcode serial numbers into a textbox, and then populate these serial numbers with other associated data into a spreadsheet. The serial numbers are supposed to be separated by each line and placed into an array.
I get a compile error "Cant find project or library" with CHR highlighted in blue from this line of code...
SNs = Split(Str, Chr(10))
Once again, it works fine on many machines with XP, but not on Win 7 or 8.
[Code] .....
View 4 Replies
View Related
Mar 4, 2014
One of our employees has wrote a VB project in excel 2010 which works on some machines and not others, it comes up with the below error message.
I have checked VB and there are no missing references.
Compile Error: Can't find project or library
Private Sub UserForm_Initialize()
'Empty ClientCodeComboBox
Me.ClientCodeComboBox.Clear
[Code] .....
View 14 Replies
View Related
Feb 8, 2013
I have got an issue. in MSE 2003, this beginning of macro worked:
Sub Consolide()
MYFOLDER = "D:DATAMypath"
In MSE 2010, it gives me an error: "Can't find project or library"
I really know that my path is defined fine, because it worked perfect before MS update.
View 1 Replies
View Related
Jun 29, 2014
I have the below code which was working at work using Excel 2010. We have upgraded to Office 2013 and it no longer works
Error: Compile Error. Cant find Project or Library
It does highlight this : If Right(varFile, 4) = ".xls" Then
Code:
Sub PropertyName_SAVEAS()
Sheets("Menu").Select
Range("I27").Select
Dim varFile As Variant
Dim strFileNameDefault As String
On Error Resume Next
[Code] .......
View 2 Replies
View Related
Dec 9, 2013
Attached is a spreadsheet wherein I'm trying to extrapolate project costs across various months based on working days in a month subject to start and end dates of the project. Need an accurate formula to spread the cost.
Days & Cost Allocation Example.xlsx
View 2 Replies
View Related
Nov 5, 2012
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
View 3 Replies
View Related
Sep 17, 2012
I have a macro that seems to work okay for older versions of Excel - I use Office XP (2002) tell me if this will work for newer versions (2007 / 2010)?
KB1017 - Trust access to VBA project
Code:
Function VBATrusted() As Boolean
On Error Resume Next
VBATrusted = (Application.VBE.VBProjects.Count) > 0
Exit Function
End Function
Private Sub Workbook_Open()
If Not VBATrustedAccess() Then
[code]....
I was thinking that i should probably have
VBATrusted = -1
as the third line so that it is negative unless the tick is there. No way of checking older versions right now though.
View 3 Replies
View Related
Dec 18, 2013
I have created a userform with 4 textboxes and "ok" button. whenever I press OK I want it to copy TB1(text),TB2(number) and TB3(number) in the first empty row a number of times based on what is in TB4(number). however, each time it has to add 1 to TB2 and whenever (TB2+1) exceeds 5 then 1 is added to TB3 and TB2 start over from 1 again.
View 3 Replies
View Related
Dec 31, 2008
Using Excel 2007, how can I copy several HTMLTextArea value fields into a range?
View 9 Replies
View Related
Nov 15, 2006
I've attached an example of the .xls file.
There are four data worksheet. Each share a common column data set; ITEM NUMBER. Is there a way to create a macro so that when you enter an ITEM NUMBER into a textbox on the "Output sheet" page, it will automatically gather all rows from all worksheets that is associated with that ITEM NUMBER?
Also, on worksheet 3, the ITEM NUMBERS have an extension that defines the type. Can a wildcard be setup so that it will only match the first six characters of the cell?
View 3 Replies
View Related
Jan 8, 2008
I want to filter data using two sheets. i want only the names in a field that are listed on BOTH sheets. i have a two lists of names and i want only the names that are included in both sheets while still including the rest of their information.
View 4 Replies
View Related
