Multiple Instances In Project Explorer
Dec 20, 2006
If from a macro I open "workbook A" from within "workbook B" then "workbook A’s" VBA project remains in memory and in the VBA project explorer. If I then run the macro again I have 2 instances of workbooks A VBA project showing, and so on. I read on this forum that if you have a public variable that links the workbooks then this is the reason but I have created a test workbook with the following code
Sub test()
Workbooks.Open ("C:Path Data.xls")
Workbooks("Path Data.xls").Close SaveChanges:=False
End Sub
If I run the macro test the workbook opens and then closes but its project remains open. How do I remove the projects when I close a workbook?
View 7 Replies
ADVERTISEMENT
Jul 7, 2009
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.
View 9 Replies
View Related
Sep 20, 2007
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice.
How to fix it?
View 3 Replies
View Related
Nov 21, 2007
Im trying to construct a nested Countif statement. I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N. The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"
View 2 Replies
View Related
Dec 7, 2012
When selecting multiple excel files in explorer, you can right click and choose to print the selected documents. All documents gets opened and printed but the files stay open and you have to manually click on YES or NO to save the changes.
Is there a way not to see this window appear. So documents get opened and printed and closes itself without asking anything. Maybe it can be done with an option in the preferences. I'm not sure.
By the way the documents where created with excel vba just in case it's relevant. Also the version of Excel is an older version, I think it was Excel XP or 2002/2003.
View 1 Replies
View Related
Feb 16, 2008
I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.
I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.
Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.
Sub SearchAllXLSFiles()
Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook
On Error Resume Next
Set wbDestiny = ThisWorkbook
View 5 Replies
View Related
Jan 13, 2013
I have a large dataset with a company name in Column A and a Town in Column B, e.g.
ABC Company London
ABC Company London
XYX Company Swindon
STJ Company Bristol
STJ Company Bristol
STJ Company Bristol
DEF Company London
DEF Company London
DEF Company London
and I need to identify every instance where the same town appears for each instance of each individual company and add in Column C incrememnt the town name, so the above should end up looking like this:
ABC Company London London
ABC Company London London-1
XYX Company Swindon Swindon
STJ Company Bristol Bristol
STJ Company Bristol Bristol-1
STJ Company Bristol Bristol-2
DEF Company London London
DEF Company London London-1
DEF Company London London-2
I'm thinking I need to use some sort of lookup, but because of the volume of the instances of each company I have no idea how many instance appear in the data set. How could I go about achieving this and incrementing the town names in Column C automatically for each instance?
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
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
May 28, 2014
What I want to do today is, I want to get the extension (Type) of a file from the string. I have managed to get formula for that.
PHP Code:Â
=IFERROR(RIGHT(C12,LEN(C12)-FIND(".",C12)+1),"")Â
But the problem is, if the name of the file also have any dots, it is not giving correct result.
For Eg, if the string is ABCD_V1.0.mpp, I am getting 0.0.mpp not .mpp
NB: The file name may or may not have dots. So cannot tell that, it is always the second instance. Need to find the last dot and trip the things before that
View 11 Replies
View Related
Apr 28, 2009
I have four different sheets with email addresses in an excel file. There are some email addresses which are present in more than one sheet. I wanted to find these email addresses and remove its multiple instances.
I can't use the simplified version of vlookup, as I can't enter each email address to see if there is another instance.
Is there a way where the excel sheet can search within itself for duplicate values and if not delete/at least point them out, so that I can delete them manually?
View 3 Replies
View Related
Jan 7, 2005
I want to return the 2nd field for Jim in a single list:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM
I want to pull Jim's accounts to another worksheet:
HP
Toshiba
Lenovo
When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?
View 14 Replies
View Related
Apr 17, 2009
Using Excel2007 / Vista
Weekly I receive commission payment data. Essentially 2 columns, Col A the client name, Col B the commission amount. I like to sort the data so that I have a total commission amount for each client
Firstly I sort the 2 Cols alphabetically. Where there is only one instance of a client name, the total commission is obvious.
But any client can appear several times (anything up to 6) with corresponding commission amounts for each name occurrence.
One week a client may not appear at all, or they may appear from 1 to 6 times.
I seek a formula which, where there are multiple instances of the same client name. will total all the commission payments for that client. Naturally the formula can be in a new Col.
View 9 Replies
View Related
Jul 28, 2009
I have an excel file with around 15 sheets. While i open it, i am getting 5 instances of the same file, that means after i open it , i could see 5 copies of the same file. If i make changes in one file, in others also its getting reflected.
View 9 Replies
View Related
Sep 10, 2009
how to label/define what I need so I'm not sure what needs to be provided...
I have two columns of data- phone number, city. Each row is an individual record of a call from that phone number to that city. And, I need to ID, for the eventual end goal of deleting, those phone numbers that called multiple cities.
View 13 Replies
View Related
Sep 13, 2007
I just installed excel 2007 and encounter a strange problem. When I open 2 or more excel instances from windows explore by double-clicking excel icons, those instances are not linkable, they seem absolutely independent to one another (in View/switch windows only sees one workbook). However when I open second file from within the first instance (Ctrl+O), I see 2 in the view/switch windows and it works fine.
View 9 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 16, 2007
I have a column of IDs (260, 266, 405, 437, etc.). There are several instances of the same ID throughout the column. I would like to remove all but one instance of an ID so that I see what IDs I have. Is there a way to identify what IDs are there and then remove all but one of them?
View 9 Replies
View Related
Mar 19, 2013
What I'm trying to do is input a formula in col G which will look for instances of the city named in col F in both cols A and C. This should then return the total of these, from cols B and D that have the letter "F", into col H. Therefore, in the attached example, cell G2 would return "1", G3 would be "0" etc.
Should I be using VLOOKUP or COUNTIF, or maybe a combination of these or something totally different?CityCodeCount.xls
View 3 Replies
View Related
Nov 5, 2008
This is a good deal harder than my last issue. I have a few pieces of data:
A62:A80 - Array of Integers named X (2 through 20, to be exact)
D62:D80 - Array of Strings named Y (on a Drop Down)
AD45, AG45, AJ45 - Formula Holders
Now the tough part of explaining what I want... assume the string in question is "Pie":
If Y contains "Pie" once, I want AD45 to take the value of the corresponding X.
If Y contains "Pie" twice, I want AD45 to take the value of the first X, and AG45 to take the value of the second.
If Y contains "Pie" three times, I want AD45 to take the value of the first X, and AG45 to take the value of the second, and AJ45 to take the value of the third.
So, in the end, the formula holders will either have a number between 2 and 20, or be blank. Note that it is possible for users to put more than 3 occurrences in Y even if it's user error.
I'm not really asking for the full formulas, but I simply don't know where to start on this, on what functions would be useful in pulling out the values I want.
One idea I had is that:
AD45 searches the array for "Pie" and takes the first value of "X" that matches the find. Blank if it doesn't.
AG45 checks if AD45 is not blank, and searches the array for "Pie" starting at the next index.
AJ45 checks if AG45 is not blank, and searches the array for "Pie" starting at the next index.
View 6 Replies
View Related
Dec 31, 2007
I am looking for a formula that will return the cell numbers of multiple instances of the item found using VLOOKUP. For example if I am looking for
"A123", this may occur in more than one cell in the search range. I want to be able to return all the cell reference numbers of that instance.
View 9 Replies
View Related
Aug 14, 2007
I wanna extract anyt text in a call that starts with LQ1 to a length of 10 characters to an empty cell, coma delimited.
View 14 Replies
View Related
Jan 31, 2012
Windows 7 . How do i open multiple instances of excel? even when im working with two workbooks rather than both appearing as the active book in excel only one can be on top. id ideally like to have both appear side by side on my desktop, vertically not horizontally.
View 1 Replies
View Related
Feb 4, 2014
So I know vlookup wont work for this, I could do an array index but that wouldn't easily return exactly what I need.
I have two sheets in the same book. One has three columns
Area
Room
ID
A-1
1101
BG11
A-1
1101
BG12
A-2
1102
BG12
The other has a bunch of columns with different information for the ID field
ID
INFO
INFO
INFO
ETC...
[Code]....
Sheet1 with the three columns will often have multiple rows for the same ID z with different room/area infor. Sheet2 only has one instance of each ID.
What I would like is something, either macro or in a straight formula that will merge both of these. I'd like to add the Area and Room columns to the second sheet (or merge everything into a third sheet) while keeping all the other info and having repeating lines if the ID shows up more than once.
Result:
ID
AREA
ROOM
INFO
INFO
INFO
[Code].....
I explained this well enough
View 7 Replies
View Related
May 20, 2014
I have an input sheet with 3 columns: Currency pair, Tenors, and Value.
Like this:
EURUSD 1m 3
EURUSD 3m 0
EURUSD 1y 5
GBPUSD 1w 2
GBPUSD 1m 9
GBPUSD 2m 1
etc etc for about 100+ rows.
This input is exported from a system we have and may vary in length and values from time to time.
What I want to do is pull all the data for specific currency pairs (probably for 5 of the 10+ currency pairs it displays) onto another worksheet. And for each tenor for that currency pair, i want to perform 10-20 lines of analysis on the next sheet.
So, if GBPUSD was a currency pair I was interested in, I would need 10-20 lines for GBPUSD 1w, 10-20 lines for GBPUSD 1m, 10-20 lines for GBPUSD 2m, etc.
How can I pull this? While maintaining flexibility so if the tenors, currencies, and # of lines change?
View 1 Replies
View Related
Jun 23, 2009
Sheet 1 has one column
Customer Number (unique)
e.g.
111
222
333
444
Sheet 2 has two columns
Customer Number
Email Address
e.g.
111-----billgates@msn.com
111-----billgates@microsoft.com
111-----billgates@hotmail.com
222-----davidbeckham@mufc.co.uk
222-----davidbeckham@fa.co.uk
333-----me@test.com
444-----you@test.com
Now, when I do a vlookup on sheet one to obtain all email addresses for customer 111, it only returns the first email address - e.g. billgates@msn.com
Ideally, I would like;
Is there a function that can achieve this?
If not, can the multiple occurrences be returned in one cell, separated by;?
View 9 Replies
View Related
May 23, 2008
I have a vba routine in my source application that creates a new instance of Excel and opens a new workbook in the new application using the following lines of
Set WoApp = CreateObject("Excel.Application")
Set WoBook = WoApp.Workbooks.Add
Set WoSheet = WoBook.Worksheets(1)
(This is standard code straight out of the vba Help for 'createobject')
The source appplication builds output in the form of worksheets which includes cell content and pictures placed in shape objects.
I need to copy each completed worksheet from the source application into the new workbook.
For example, if MyBook is in the same Excel application as the source then this code will do it:
Worksheets("mysheet").Copy After:=MyBook.Worksheets(1)
But this does not work for WoBook as it is in another Excel instance.
WoBook is recognised by the source. The following code does work:
WoSheet.Cells(1, 1) = "hello"
I have all the right reasons for wanting to do this (without saving the sheet and re-opening in the new instance).
View 9 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
Feb 21, 2014
I have an excel application which works extremely well in one single instancec. It has a bunch of ActiveX control objects on the worksheet. Generally I call them by convention Sheet1.Cmd.show.
I have since experimented with having multiple instances of excel running on the machine. I start to notice something very strange. The first instance I open and run all the activeX buttons are all clickable. As soon as I open a second instance of an excel program all the activeX buttons on the worksheet are not even clickable. They are enabled by my program, but not clickable as if they were not there at all.
I also try open a normal excel workbook then followed by my excel program in a second instance. Even though the first excel file does not utilize any activeX objects all my buttons on the subsequent instance are not clickable.
The weird thing is all my activeX control objects on a userforms are still clickable and call to all my planned events in my program. It is just all the activeX controls on my worksheet that do not have any reaction at all.
I always think when one opens a new instance it will be totally indpendent and would even occupy another CPU if my machine has more in it. If they are truly independent then I am at a loss to understand why my activeX controls would fail to work at all.
View 8 Replies
View Related
Feb 2, 2010
I'd like to use VBA to open 3 workbooks in their own session/instance/etc of Excel 2007. Ideally, there will be one main workbook, that upon opening will subsequently open the other 3 workbooks.
Also, when I open that first main workbook, I will obviously have to enable macros to get any code to run. Is there a way to carry this enabling of macros over to the other sessions to avoid having to enable macros in all 3 other workbooks? (Changing my security settings isn't an option)
View 3 Replies
View Related
