Use VBA To Install Analysis ToolPak Add In
Feb 23, 2007
I found this code in the posts to automatically install the Analysis ToolPak at Open.
Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
End Sub
I tried this in the workbook module but it doesn't seem to work. Is this code correct or have I done something incorrectly?
Also, are there any drawbacks to an automatic install of an add in like this and if it runs the code every time the workbook opens, is there any performance issues (maybe only opens a bit slower)?
View 9 Replies
ADVERTISEMENT
Apr 9, 2007
I currently have a formula like this:
e87-7+choose(weekday(e87-7),1,0,0,0,0,0,2
this formula is used because I was adding or subtracting a certain number of days from one date to figure out when a certain event would occur next. When the date fell on a weekend, I wanted to make sure that it rolled over to the next closest weekday.
HOWEVER, what I also want this formula to do is exclude weekends in the counting of the days. So basically, I want to exclude weekends completely from my formula.
example, Printing always occurs 4 days after the p.r. meeting. the p.r. meeting is in e87. so e87 + 4 = the printing date. but I dont want those 4 days to include weekends in the count AND I dont want the final date to fall on a weekend either.
View 9 Replies
View Related
Nov 13, 2008
I use various spreadsheets which use the EOmonth function. The reason for this is that I have a variable start month and then move out for a set period and this is a very easy way of doing this. My only problem is that you have to have the Analysis ToolPak add-in selected, so when you send to other people, it doesn't work. I have use a bit of code to turn this on, but if the disable macros is selected than this doesn't work.
View 2 Replies
View Related
Nov 17, 2006
I have seen spreadsheets where there are macros embedded that 1) check to see if the Analysis Tookpak is loaded and 2) Load the Analysis Tookpak if it is not loaded. I would like to do the same in a spreadsheet that I am creating and using. Does someone have example macros they could share that do what I am asking?
View 6 Replies
View Related
Feb 28, 2007
A workbook was created using the English version of Excel. It uses Analysis Toolpak functions.
When the workbook is opened on a Spanish version Excel system, embedded functions - like IF, AND, OR translate to Spanish. Other functions, like NETWORKDAYS, do not.
Any cross-language users have a tool for this? Other than doing a Find/Replace or creating custom functions.
View 9 Replies
View Related
Nov 22, 2006
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
View 3 Replies
View Related
Sep 14, 2006
I am running a fairly complex simulation at work and have quite a few different functions in it. For the simulation to work properly, i need to have the Solver add-in and the Analysis ToolPak installed. Everytime that i try and use the simulation on a different computer (or if anyone else tries to run it) the add-ins must be enabled again. Is there a way to set up a macro to enable them when either i open the simulation (first preference) or when a button is pressed (2nd preference).
View 5 Replies
View Related
Sep 14, 2007
Has anyone ever used InstallShield to install an Excel Add-In?
View 2 Replies
View Related
Nov 7, 2011
I was wondering if there was a way to use the Workbook_Open() event in Excel to install specific references. I tried to record a macro, and then open the VBA editor and then see what code it showed to install, but there was no code in the macro
I tried:
Code:
With Workbook.reference
.AddFromFile "C:WINDOWSsytem32.stdole2.tlb"
End With
Which is just giving the location of the reference I want to install, and It gives a debug error of Run Time Error 424 Object Required
View 9 Replies
View Related
Jul 26, 2007
I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed. The logic, not in true VBA syntax, is as follows;
' Check Printer
If <This Printer> Is installed Then
Printout using <This Printer>
Else
Install <This Printer>
Printout using <This Printer>
I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.
View 7 Replies
View Related
Jun 6, 2009
I am running Excel 2007 on my Windows Vista laptop. A workbook I've developed in Excel 2007 (compatibility mode) on my Windows Vista laptop has a macro that runs as expected on my laptop but will not run on my desktop which is running Windows 2000, Excel 2000.
I've addressed this question previously under the thread "Macro Bombs Excel 2000". I've not received a response as to why it won't run under Windows 2000, Excel 2000. I don't know if it's an Operating System issue or an Excel 2000 issue.
I'm developing this workbook for my children for their personal financial management. I'd like to determine if this is an Excel 2000 issue or an Operating System issue. Most of my children run Windows XP, Office 2000. I have a dual system on my laptop (Vista and XP).
I have a legal software license available for Office 2000. My question is, can I load Excel 2000 on my Vista, Office 2007 laptop. I've looked at http://support.microsoft.com/kb/218861/ and they talk about installing oldest versions first. I have Excel 2007 already installed. I don't particularly want to uninstall it. Can I install Excel 2000 without uninstalling Excel 2007?
1. Can it be done?
2. Can it be done without MAJOR operational issues.
3. What is the process?
4. Can I load ONLY Excel 2000?
View 3 Replies
View Related
Mar 23, 2014
how to retrieve Install date of Windows from registry (or form any where else). I know this date is saved in key "HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows NTCurrentVersionInstallDate", I used RegRead but it return "0" (zero)
View 6 Replies
View Related
Apr 16, 2006
Finally after 3 days of intensive studying VBA, my first add-in pack is ready to be installed in the office. I've got my custom form all ready, now all I need is to have excel create a custom button on the toolbar that calls up my custom form when clicked.
The idea of assigning a shortcut key doesn't really appeal to me. I'll be packaging it up in .xla format. Would be greatful if anyone can direct me on how to add a button to call up my form (only one form in fact).
View 2 Replies
View Related
Jan 7, 2010
The code below first asks a user to input a weekending date (which must be a Saturday). The value of the input box goes to cell C1 of my spreadsheet. That part works fine. I also have a function in Cell G1 with the function:
=TEXT(C1, "dddd")
I don't know if this is the best way to test for a Saturday but it is what I have,
Where is goes bad is if the date is not a Saturday. I have a loop to force a new date install via a input box but it won't update C1 with the inputed data to recheck for the Saturday value. Below is the whole code I am working with...
Dim aa As String
Dim bb As String
If Range("C1") = "" Then
Do While bb = ""
bb = InputBox("Please Enter a Weekending Date!")
Loop
Range("C1").Value = bb
End If
If Range("G1") "Saturday" Then
Do While aa "Saturday"
aa = InputBox("Weekending Must Be a Saturday. Please Enter a New Weekending Date")
Loop
Range("C1").Value = aa
End If
View 9 Replies
View Related
Feb 17, 2009
I have finally come up with a macro and would like to install it to around 10 people's "Personal Workbook" in my department. Let me explain a bit further...
We use web-based software which has an "Export to Excel" option which we all use. The resulting data populates into a spreadsheet automatically.
I would like the user to be able to click on Tools/Macro/Run Macro and then run that particular macro. I assume that this macro should be in the "Personal Workbook" so that the macro will be visible no matter what worksheet they may have open?
Can this be done automatically/with a macro or must this be manually done?
I"m using Excel 2003.
View 14 Replies
View Related
Aug 7, 2009
I need to write a program that will take numbers from various sheets, do a function on them (specifically a statistical analysis), and print that number to a cell on one sheet. I've tried to have all of the numbers go to one cell and have the analysis done on them, but after so many sheets, I get a memory error. I don't necessarily need all of the numbers to be saved, just the result is what I need.
View 14 Replies
View Related
May 11, 2009
how to tackle a piece of work that I really don't want to do.
We have extracted some summary information on one of our products. The workbook has 10 sheets, one for each of our 10 main sales channels. Each sheet has the same layout of information on. There are 7 tables on each sheet, and each one cuts the data in a different way (eg one summarises by age of purchaser (in age bands), another by demographic group etc). Each table then has the same 14 columns of key data (eg number of sales, average order value).
I've been asked to analyse this information "for anything interesting". At the moment, the only way I can think to do this is to print out the 10 sheets, sit down with a highlighter, and try to visually identify trends and anomalies. The idea fills me with dread, and I suspect will be quite inefficient.
I've asked whether I can get the data in a pivot table or some other format that might be more conducive to analysis, and have been told no. (There will easily be more than 100,000 lines in the original data (we're using Excel 2003)). To be honest, I'm not sure that I'd really be that much better off, even if it were in a pivot table.
View 9 Replies
View Related
Aug 20, 2009
to figure out a breakeven analysis.
Here is the situation. I bought 100 shares of stock at $40 each for total investment of $4000. The stock price is now $26 a share so the investment is down $1,400. I want to figure out the number of additional shares I need to buy at $26 so that when the price goes to $26.01 i turn a profit.
View 9 Replies
View Related
Dec 17, 2012
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
View 4 Replies
View Related
Aug 13, 2008
What's the technique for opening a .pdf file and copying its contents into Excel for subsequent analysis by VBA?
On my Mac version of Excel there doesn't seem a facility to import it or even copy and paste it. Is this something that's available in Windows versions of XL?
View 10 Replies
View Related
Feb 10, 2012
I need to summarise a batch of data in this format....
YearPeriodAccountCustomerPart NumberSalespersonChannelValue2011Jan1SteveA1KylieHome1502011Feb2BillA2KylieExport1802011
Mar3FredA3KylieHome2002011Apr4JohnA4KylieExport1002011May5AndyA5KylieHome85
And need to put it in the following format...
Salesperson - KylieJanFebMarAprMaySteve150Bill180Fred200John100Andy85
I know I can use a series of pivot tables but would like to keep the size of the file to a minimum.
Is a DSum the way to go or can I use a Sum if and use an and function with the sum if?
formula if so to saveme a bucketload of trial and error attempts...
View 2 Replies
View Related
Sep 25, 2012
I have a problem that I am finding impossible to find a solution to myself. I want to take the maximum value on a graph (in a column) that has the 2 values next to it (above and below) within 5% of that value. If they are not within 5% I want it to look for the second largest value with the same conditions and so on until the conditions are met.
If it makes it simpler, the final point can be ignored as there are only values above it in the column.
It is in column H, which goes from H3:H51
View 5 Replies
View Related
Jul 5, 2008
I have been putting together workbook for my golf analysis.
Against each hole (1-18) I enter in one row which club I used from the tee which could be any of the following: D,3w,5w,1,2,3,4,5,6,7,8,9,w,s.
I also have a row that says whether I have hit the fiarway or not. If hit fairway = 1 if missed = 0.
What I want to be able to do is show the % of times I hit the fairway with each club. This would seem ida for the if function but I'm not quite sure how it would work because the result row is going to need to look at every hole to see if the club used was d,5w,3 etc etc and 'if' so then it will need to look at the row which says 'hit fairway or not' i.e. 1 or 0. This is where I get lost and am not sure if I do need the 'if' function or something else? Perhaps I need additional rows?
View 9 Replies
View Related
Sep 4, 2009
If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.
View 9 Replies
View Related
Oct 5, 2006
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
View 9 Replies
View Related
Apr 2, 2007
I need to analyse data content in cells. For example, I need to analyse a list of post codes. I need to get the following info:
Max Length
Min Length
Data Types
Data Formats
Null Count
Most frequently used value
Unique record count
View 6 Replies
View Related
May 1, 2007
I have an excel question on how to create a table that will be able to compare actual results with our estimated results.
Basically, the column I have in blue in the attached excel file contains our estimates. Once the actual numbers come out, we will replace those numbers with actual numbers.
I would like to create a table that will automatically show the magnitude of the impact of the actual numbers vs our results -- basically how much each item added or subtracted from EPS.
The items I would like to show the impact of are:
(1) The tax rate -- how much the actual tax rate impacted EPS vs. our estimate. For example - if it was a lower tax rate there may be a $0.03 benefit.
(2) interest expense line - more/less interest..what the impact of that is.
(3) non-recurring items
(4) Share count - a lower share count or a higher share count than we anticipated - what the effect on EPS is.
For each item though, every other item should be held constant. So if looking at tax rate -- then use the actual interest expense and the actual shares.. and if looking at shares...compare that to our estimate but use the actual tax rate reported and the actual interest expense....
There should then be a sum total of these items +/- impact to EPS.
View 9 Replies
View Related
May 3, 2007
i want to run data analisys(Rank and percentile) on each row in my sheet and put the results on a new sheet
(the reuslts are 4 rows ) My problem is some how the command of the run analisysWon't run the command
kStop = . Cells(1, Columns.Count).End(xlToLeft).Column
Sheets.Add
ActiveSheet.Name = "Analisys_Results"
Sheets(" Total time data").Select
Var = 4
Var1 = 8
For ILoop = 1 To kStop
If (ILoop > 1) Then
Var = Var + 4
Var1 = Var + 4
End If.....................
View 4 Replies
View Related
Sep 29, 2007
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
View 3 Replies
View Related
May 8, 2014
Data Analysis . I have 7 tables gathered in one spreadsheet but in seperate sheets. Tables have the same columns but different data baceause they come from different locations. Now I would like to create pivot table to combine data from all 7 tables. I read a little and I got to know that relationships is needed between the tables but I can't create it because I don't have unique data in the table. Usually tables consist repeating names, locations, some numerical data.
For example every table consists worker's name. I create pivot table from one of 7th starting from worker's name in row field and put some data in value field. Then I add another workers from another table. I can do it but when I put in row field I have workers from second table under those from first one. And of course data are badly calculated. I would like to have all workers in row field with proper data. It means one column with all workers from all 7 tables an then some their data
I know I can copy all of them into one big table and then create pivot table but this is solution what I wanted avoid. I know it will work but these data can change from time to time and it will be very hard to maintain spreadsheet. When data will change I will have to copy manually .Is there any solution to create such a pivot table from multiple tables sources but with the same column structures?
View 1 Replies
View Related
