I would like to create a PDF file from a RANGE Name, excel 2007, attach it to MS Outlook 2007, Once in Outlook I will select the recipient, add some remarks and hit send. I can do this now with a workbook but not a range.
View 1 RepliesI am trying to create a bar chart that changes color based on a percentage range. Any advice on how to do this? I am using Excel 2007, Windows XP, Sharepoint 2010.
View 1 Replies View RelatedI have a macro-enabled template file in Excel 2007. I would like the user to be unable to save in any format other than macro-enabled. They should be free to choose a path and filename, but not the file type.
I presume this means some VBA code in the before save event, but I don't know what.
I need to write a code to create visio flow diagram with excel inputs(Excel 2007). Attached is the requirement.
Requirement_Specification.docx
I have a macro enabled file with some functions. Now I want to create a fillable pdf file from this file.
I am able to create a pdf file from Excel 2007, but not able to make a fillable one
I am using MS Excel 2007.
I have column "A" a list of my PDF filenames and File path in Column "B". I want to establish hyperlinks for each of these PDF filenames (column A) and link it with the file path that I generated in Column B.
=HYPERLINK(B1,A1) did it very well, but unfortunately when converting to PDF, the hyperlink is not working, and I believe the conversion retained is only in text, so now my only resort is hardcoding it by VBA.
I am struggling with trying to create a Top 10 list in Excel 2007. I have googled and search the forum but could not find a solution. (Aplogies if i have overlooked a thread)
I have attached an example of my problem.
1. In Column F i would like to extract the names of the Top 10 performers, based on their respective score. Hence, based on the scoring in Column D, Column F should extract the Top 10 performer names from column A.
2. In Column G, same as above, but bottom 10 names
3. From the attachment, you will view some names are highlighted. I.e. Gary & Neil, and Ian & Michael. These are highlighted based on the fact that they have equal scores.
When extracting the Top / Bottom 10 list, i would like to rank the performers with same score differently. e.g. Gary & Neil, both scored 0%, hence they are equal. But in the Top 10 list, i would like to rank Gary higher as his absolute target is higher. (63 vs. 27) . Same applies to Ian & Michael, Michael should rank higher in the Bottom 10 List as his absolute target is higher.
I have this formula, ( which i found the basis of on a You tube video and Richard Scholar was accredited with improving the soloution)
=SUMPRODUCT(--ISNUMBER(F4:AH4)*10^{-29,-28,-27,-26,-25,-24,-23,-22,-21,-20,-19,-18,-17,-16,-15,-14,-13,-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1})
This forumla generates a number for each player, the higher the number the more inline they are to get a game
Problems are this works for the 29 weeks of this year but more weeks need added to the end of the year as we get there. Can i generate the array numbers from a formula and shorten.
This is a sample data ignore row 2( just a count of players) and data actually goes back to 6th Jan
Excel 2007BCDEFGHIJ1Wk21Wk22Wk23Wk24Wk25Wk26Wk27Wk28210101010101010103Player 1111104Player 210001105Player 300101016Player 4010101107Player 50110118Player 600009Player 7111000110Player 8011100011Player 9000012Player 101011113Player 11101114Player 121010115Player 13016Player 141010017Player 15118Player 160101Sheet1
Excel 2007, Windows XP
I am concatenating some cells into an array. The amount fields should always have just 2 rounded digits following the decimal. What should change in the following VBA code to achieve that result?
Currently Cells(r, 6) & Cells(r, 7) could have these values:
1.5
24.78945678
45.2341
What I want is rounded values to 2 decimals:
1.50
24.79
45.23
The array is used as an input parameter in a remotely called function module, after logging into the remote system, SAP.
' delim is a | character
' Populate Myarray with data from all rows
' - Only from rows which are not hidden ' 05/23/2008
For r = 1 To row_count 'r is row number
if worksheets("JEMASTER").rows(r).hidden = false then
i = i + 1 'increment myarray index by 1
myarray.AppendRow
[Code] .........
I am in the exploratory stages of determining whether I can create a report in Excel 2007 to add the instances of a particular entry in a column. It is tricky, because I want to survey only the first 4 entry rows of a group of 8 entries and then return the result as only one instance of the entry over each of a set number of these sets of data rows, in each case 4 sets. This process would repeat down the spreadsheet, and the number of occurrences of each particular entry summed and reported in a separate tab of the same spreadsheet.
The rows in this spreadsheet are 17-1240. The entry names are a combination of two columns, J and K. These must match identically for the counter to count an additional entry.
I would prefer to make the report in a different tab in the same spreadsheet...just available for reference.
Trying to create a macro to run through the following steps when I select a chart and run the macro:
Paste>As Picture>Copy As Picture>As Shown When Printed>OK
I used the macro record feature and when I enter the shortcut it always returns the same chart. How do I get it to run on whatever chart I have selected instead?
I am constantly using this function to copy charts from Excel into PowerPoint presentations and am frustrated with all the extra steps it takes (versus just Ctrl-C).
"How to create a 'main' macro to control other macros within a workbook". I have my individual macros created. There's one macro for each sheet that searches online data and returns the information. I have one of these per sheet (a total of 20 sheets) since I couldn't find a way to have all 20 macros be on one sheet and still work. My trouble is that whenever I'm on my 'main' sheet and try to run the macro which applies to a 'secondary' sheet, I get an error. I have to select the sheet first, then run the macro and it works. Below is the macro on each sheet.
VB:
Sub Holding1()
Dim DataSheet As Worksheet
Dim EndDate As Date [code].....
I'm trying to update a the summary sheet that will run through the 10 worksheets and bring back the entire row (or the columns I specify) that matches the creteria MS.
MS will appear against each row in the same column in all the tabs.
So in the summary sheet I would see all the rows individually that appear in tabs 1-10.
Excel 2007.
Is it possible to do a drop down list that allow user to select pre-defined selections but everytime when a selection is chosen, the list reduced?
Example,
I have can choose Apple, Orange, Durian and Mango.
The drop down can only select these 4 fruits.
So there are 4 cells in Column A1, A2, A3, A4.
A1 selects Mango and then A2 will only have 3 options to choose. So until the last cell, he can only choose the last fruit left.
A1, A2, A3, A4 are not selecting in descending order thus it could be A3 selecting first, then A2 and then A4.
I'm using excel 2007
I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database
When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.
Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?
I am trying to simplify a type of gannt chart bar across a spreadsheet. The spreadsheet has dates across row 3 that are calculated from the first cell F3 with =F3+7 to populate the rest of the row with dates. I want to be able to input a start date in D4, then all the other titles in column A. This start date will start the coloured bar at that start date in the chart, I will then copy/drag that start date cell for however number of weeks for the duration and I want the finish date to auto populate E4 with the finish date and also auto populate the numbers of weeks in C4 for that bar.
Excel 2007ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANA
OAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ
CACBCCCDCECFCG1D42342McArthur River Mine Power
[Code]....
I have pieced together a VBA script to create a pivot table. Which is all great and works fine BUT for some reason it is now forcing the table onto a new worksheet. see code below, why it might be creating a new sheet. Alsothe sheet Worksheets("TICKETS BY OWNER & DAY") does exist.
Code:
' HERE WE CREATE THE PIVOT TABLES AND CHARTS FOR REPORTS
Dim WSD1 As Worksheet
Dim WSD2 As Worksheet
Dim WSD3 As Worksheet
[Code].....
Excel 2007, Windows 7.
Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.
Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.
Excel 2007ABCDE1product lines:consist of these product subtypes2Widget series:Widget.type1Widget.type23Fidget series:Fidget.type4Gidget series:Gidget.type1Gidget.type2Gidget.type356data dump of parts sold or used in repairs7product subtype repairedpart number
[code]......
Excel 2007 I have a spreadsheet of file boxes the keeps a log of boxes, contents, locations and shred dates. Currently figuring out when and which boxes can be shredded is a manual hunt, find, and then deal with it. I can use CF and Sorting but I have been looking at some search type functions to return a list meeting the requirements and trying to step my way towards that.
1st attempt. Was find out how many boxes meet the shred (before today) that have not already been detroyed. Column K is the Shred Date, D1 is Todays date, Column G is Status. Got this working good.
[QUOTE]]=SUMPRODUCT(--(Log!K2:K2136(lessthan)STATS!D1)*SIGN(LEN(Log!K2:K2136)))-(COUNTIF(Log!G2:G2136,"Destroyed"))[/QUOTE
My failed attempt came at trying to find the earliest shred date excluding those that have been destroyed. This is not really neeed but I was hoping it would get me closer to creating the list of boxes that meet shred that have not already been destroyed.
Note: Column A is the Box No. and the spreadsheet contains blanks as we continue to add file boxes to storage.
Below array formula find the highest box number used by each department.
Code:
=A4&"-"&TEXT(MAX(IF(Log!$A$2:$A$2136"",(LEFT(Log!$A$2:$A$2136,LEN(A4))=A4)*RIGHT(Log!$A$2:$A$2136,3))),"000")
I would like to create a list of boxes including Column A "Box no.", Column H "Location", Column K "Shred Date" for any box whose shred date is before today that has not aleady been labeled "Destroyed" in Column G.
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:
Run-time error '429':
ActiveX component can't create object
and is stopping on the line
Set OutApp = GetObject(, "Outlook.Application")
FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
I want to search for a file in Sharepoint using Excel 2007 VBA.
Path to the Sharepoint location where the files are at is [URL] ....
File name is customer_list_xxxxxx.xlsx The x's are a date which changes every day or week. So a new file will be uploaded to the sharepoint path.
Example customer_list_041414.xls
I've tried so many different code options for this, but no luck.
Extracting data from an excel file, the data needed in lies in one column.
in this line
<GPSPosition pntRef="GPS_Auto_0080" wgsLatitude="33.334329659885" wgsLongitude="44.405736558207" wgsHeight="27.615921">
i need to get a table with three columns shows
wgsLatitude, wgs Longitude , wgsHeight
33.334329659885 ,44.405736558207 , 27.615921
etc
and get rid of all other information
this is an example of the how the file look like
---------Column N -------------------
</Feature>
</GPSPosition>
</GPSSetup>
<GPSSetup id="GPSSetupID_18" GPSReceiverDetailsID="GPS_1" antennaHeight="1.660000" stationName="GPS_Auto_0080">
<GPSPosition pntRef="GPS_Auto_0080" wgsLatitude="33.334329659885" wgsLongitude="44.405736558207" wgsHeight="27.615921">
[code].....
seems from above that there are too many data not needed the only thing need to be exracting is one lies of GPSPosition Line.
I have several sheets I use for MI and most have similar amounts of data in, ie, a tab for each month with out 15-20 columns and upto 10,000 rows, then a couple of summary sheets with filtering and calculations in.
One of my sheets is currently 16Mb, when the others are about a third of that, with similar amounts of data.
Is there a way to find what is causing the extra space to be taken in this sheet?
Or does this seem about right for the amount of data?
I'm using 2007.
Is there a way to Open or Import a PDF file in Excel 07 ?
View 2 Replies View RelatedI am not able to add a new Excel sheet in a existing file when I right click next to the existing page it gives this option only and I am using Arabic Charcters + I am not having too many sheets
View 1 Replies View RelatedI received a USB drive from a client with an Excel spreadsheet with hyperlinks to PDF's (not sure of the version it was originally created in but my version is 2007). The PDF's are in another folder on the USB drive.
The cells with the hyperlinks have a formula to hidden cells [i.e. =HYPERLINK(C17,B17)] - in column B is the name of the hyperlink and in column C looks to be the file trial to the PDF (please see attached picture).
When I select the link, I get an Alert Box stating, "Cannot open the specified file." How I can get these links to work?
Excel Hyperlinks.png
I have files that have thousands of rows in them with company names. The rows contain the company name plus lots of other info. I'd like to be able to create files or sheets with the company name as the file or sheet name with the respective rows contained within it. Make sense?
For example:
Original file or sheet
Date Company Info1 Info2
Jul Sams Expense Desk
Jan Freds Expense Doughnuts
Jun Sams Deposit Return
Feb Sams Expense Food
Dec Freds Expense TV
Would become:
Sheet or File Sams
Date Company Info1 Info2
Jul Sams Expense Desk
Jun Sams Deposit Return
Feb Sams Expense Food
Sheet or File Freds
Date Company Info1 Info2
Jan Freds Expense Doughnuts
Dec Freds Expense TV
I'm having some trouble with a spreadsheet copy.
I have a spreadsheet listed out with 2-3 pieces of data per row like:
Mark - abc
John - def
John - ghi
Abby - xyz
etc.
What I need to do is create a new file for each unique string in column A. So for my brief example above, the "Mark" file would have 1 row, the "John" file would have 2 rows, etc. I also need to save the new file by the name in the first column (so "mark.xls"). (Conveniently, the table is sorted by the first column already.)
What I did was create a new worksheet for each row and then save that as my file. However I'm having problems getting my file to contain more than one row (I can get either the first or last row, but not all of them). I have about 30 spreadsheets to do this for, and each will create 80+ files... I'd really rather not do them by hand.
I'm on Excel 2007 if that matters.
I cobbled together some code I found in various places and this is what I have so far:
Code:
Sub CreateWorkbooks()
Dim newSheet As Worksheet, regionSheet As Worksheet, CurSheet As Worksheet
Dim cell As Object
Dim regionRange As String
Dim lngRows As Long
Dim CurRow As Long
[Code]...
(note that the comments are what it is supposed to be doing, not necessarily what it is doing)
I have an Excel worksheet (2007 version) which I am trying to save as a PDF file. However, the PDF option is not listed in my "Save as type" drop down window.
View 3 Replies View RelatedI have an excel add-in which is a ribbon which houses a great deal of financial tools that I use on a regular basis so I don't have to hunt around excel for things I use regularly. The add-in has always loaded automatically whenever I opened excel - whether I opened excel by creating a new blank document or by double clicking a previously existing file.
Starting a few days ago though, the add-in only opens when opening excel by creating a new blank file. I can't think of anything that changed recently. Using Excel 2007.
Even when opening a file by double clicking, Excel still says the add-in is active (in excel options).