I start my excel program from a "Shortcut" in "Folder 1".
That shortcut points an Excel program in "Folder 2".
When Excel is started,
Parent.Path returns "Folder 2",
but I really need to know what "Folder 1" was.
It seems that should be available as
when I do "Save File As"
it is pointing to "Folder 1".
Is there a way to find what "Folder 1" really was from the VBA enviornment.
I am trying to access another program via Excel VBA. The idea is that, we fill out out time and attendance sheets for work on excel workbooks. But, we have to go into a terminal program to enter this information so it can be uploaded to whereever it goes.
I am able to get the Shell command to work. But, it will be riddled with sendkeys commands and application.wait. I am sure that this is not the best way to program something like this. I believe I read that for this to work that it has to be COM compliant. I don't know. The program is a Java based terminal program.
This works for me
Code:
ShellExecute 1, "Open", "C:UsersPublic******lib***_core.jar"
But this does not
Code:
CreateObject "C:UsersPublic******lib***_core.jar"
It gives me a Run-time error 429, ActiveX component can't create object.
i am creating a break track program using excel with vba. My excel file contains the data for all employees. I have a Userform where the user will enter his employee ID which will pull up his data. I have 3 option button in which the user choose what time he would start his break. Once the user click the start button, the time he started his break will be placed in a cell and a dialog box will appear stating the time the user needs to be back. Once the user click the end button, the time he ended his break will be place on a cell as well and then it will show a message "on time" if the user came on time else if the user was overbreak, the overbreak amount of time will be displayed. I have attached my sample file together with some vba code.
View 3 Replies View RelatedI imported data from another program to Excel, it is an address database. Some of the entries are all capital, some are all lowercase, some are proper. I would like to switch everything to proper. I have tried numberous times to do EXACTLY what I am reading about copy and pasting, entering the formula, designating the cells I want it to apply to. However, when I do it, nothing changes.
View 9 Replies View RelatedFor the last half dozen years I have been using a simple Excel table to sort Christmas mix cds. The headings are: song, artist, album, year, and songwriter. This year after adding the additions, I tried to sort and it would not work. The automatic sort stopped at the end of the previous years' work and did not pick up the additions. When I try to include this year's additions, the sort grays out. I'm sure it's something simple. Adding additions to my brother's and daughter's lists worked fine.
View 5 Replies View RelatedTrying to program cell C1:
If A1 is between .81 and 3, AND if B1 is Adult, then C1 is Priority Mail
If A1 is greater than 3, AND if B1 is Adult, then C1 is FedEx Ground
If A1 is Less than .81, AND if B1 is Adult, then C1 is First Class Mail
If A1 is Less than .81, AND if B1 is Child, then C1 is Hold for Inspection
etc.
There will be a list of ~45 conditions that will populate a specific value in C1. Can this be done? If so, how?
I am currently looking for a front end program to access multiple excel files at once. In a nutshell, I work for a company that uses subcontractors. Each subcontractor is graded in about 7 different performance categories. I receive daily excel files that give us the results for each category. I get separate files for each category.
What I do right now is use a master excel file. Every day I take each file I receive from our parent company and add it to the master file. I have a huge excel file that has a separate tab for each performance category. Then I have a "Report" tab where I use various formulas to summarize a sub's performance over a certain date range.
Essentially, the report tab is just a neat and concise way to display the stats of an individual sub, an office, a region or the whole company. I use tons of VLOOKUP and COUNTIF(S) formulas to look over the hundreds of lines of data and bring back the stats. It works but it is just not very efficient as I get hundreds of new lines of data every day.
I have two work sheets where I have data.
Sheet1 contain daily input table which as follows:
Code:
Namesalary Bonus Check
XX1000 2 FN
XY900 1 NA
YY1100 2 FN
ZY1500 3 DP
ZZ1250 2 FN
AA1050 2 NA
AZ 950 1 FN
Sheet2 have table where all the information is saved. So we can say this is database of sheet1. Which store every day information of sheet 1.
Code:
SALARY DATABASE
Name SalaryBonus
What I want to do is that ---it copy all the data which fullfile condition FN and move to the Sheet 2 which is salary database. Two important things to check is that. IF name already exists in the sheet 2 then it replace old info with the new one. Second thing is to sort the whole table(Salary database-sheet 2) according to A-Z (Name column). I use office 2003. I hope I provide all the information.
However, I have got an Excel file which contains a lot of information. There are four sheets with large Pivot Tables.
But the thing is, the plan is to do the tables much larger, create tables which more specifically shows information. When doing so, the tables will show much more information, and therefore be much larger. It will be difficult to read and understand the tables because of the large amount of information.
What I want is to store information in, for example, another program such as Microsoft Access. There, I can put a huge amount of information and then link it to Excel, so that in Excel I only see the Access stored information when I "search" for it with y created slicers. Like, when I use my slicers and click on one category it sort out and shows me the information i want, even though it's stored in Access. It only shows it in Excel when I "ask" for it. This is what I have thought.
I have an Excel 2003 list with four columns as shown below:
Zipcode
CRRT
Count
Bundles
85710
C004
693
14
85710
C005
867
18
85710
C006
1021
21
I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...
Zipcode
CRRT
Count
bcount
Bundle
ibundle
85710
C004
693
50
1
14
[Code] ........
So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.
I am trying to copy some data from on sheet(sales) to another sheet(totals) and later will try to copy to all the worksheets in my workbook
I am using the following Sub macro2()
Sheets("sales").Select
Range("c16:c17").Select
Application: CutCopyMode = False
Selection.Copy
Sheets("totals").Select
Range("b2:b3").Select
Active Sheet.Paste
End Sub
the data in cells C16 and c17 is 2000 and 1850. it shows an error: sub or function not defined
I'm trying to get a loop started to perform tasks and then go to the next line if it has info in it and do the same thing, until it gets to a blank line, and can't seem to get it right. It's been a while since i've written macros, so i'm sure i'm doing something completely wrong.
View 4 Replies View RelatedEvery time I open my excel spreadsheets the "Getting Started" window opens automatically. How do I turn it off so it does not open automatically
View 3 Replies View RelatedI've a set of tasks in Column A which I would like to measure. At times, Users flip from task A to task B then back to A and so on. I would like to be able to pause a timer on task A if when timer on task B is started and same as well with the other tasks.. When Task A is started again, it should continue the timer if not add the on-going time to the presently running time under that task.
View 2 Replies View RelatedI have a workbook with about 52 worksheets, I have a script that will unlock the protection from all the worksheets. However the problem I'm having is the script runs and starts and ends at sheet1 I want the script to start and end at the sheet I run the script from lets say sheet 40?
View 4 Replies View RelatedI have 4 columns: X, Y, Z, AA.
X - Start month e.g. 1, 2, 3, etc
Y - Start year e.g. 2006
Z - Finish month e.g. 1, 2, 3, etc
AA - Finish year e.g. 2006
In column AK, I need to return a value, whether it is PLANNED, STARTED or FINISHED.
PLANNED is if start date and completed date are both > than now()
STARTED is if started >now() but completed date is > now()
FINISHED is if completed date is < now()
Issues:
1. Start date is computed using Y as year, and X as month and the day is assumed to be 1. Same princilple for the finished date.
2. Column X, Y, Z, AA can be blank. If month (X and/or Z) is blank but year is not, then date is beginning of the year. If either start year is blank but finish year is not, then status should be based on the finish year and vice versa.
3. If both year (Y and Z) are blank, then status should be "NOT SPECIFIED".
I am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.
View 2 Replies View RelatedI am, and have been using several sum- and count-ifs formulas for a time, but when the new year rolled around, decided to improve some of the sheet.
That all being said, the issue started after this. A specific set of my count and sum if functions started returning error values.
I analysed them, they seemed fine, and then selected them and hit enter. Problem over in that cell, as suddenly the correct value is returned.
But that's just it, I know the formulas are correct and working, as, if I select and hit enter on them individually, they return the right value. However, every time I close the sheet and re-open, all the values are showing error again.
I am perplexed. Did I inadvertently change a setting somewhere when I rebuilt the worksheet that is preventing the formulas from resolving correctly? As, like I said before, the formulas are fine, the data they are resolving, is fine. Just does not seem to calculate when I open the sheet.
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View 3 Replies View Relatedi am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.
Start 08:00
End 16:00
Total 8 hrs.
How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?
I am not sure that I can do this, but here is what I would like to do. I have a worksheet that I initial when I start a job in on cell and then when I finish in another cell. What I's like is to have a macro running in the back ground that will tell me the total elapsed time from when I started to when I finished.
View 5 Replies View RelatedI have set up a workbook with sheets named for each month.
I have 70+ separate excel files (one for each month) that each have a single sheet I want to copy into the new workbook's corresponding month sheet.
Each file is named different but the name of the sheet in those files that I need will all be the same.
Is there a way I could sit in the sheet I want it pasted to, run a macro that I can point to the correct file/workbook -- preferably without opening -- and then have it copy the contents of the sheet I want (again, will always have the same sheet name).
I want the sheet copied with formatting and all, an exact copy.
This link seemed useful - if i could control which sheet it copies more easily since for me the source data always has a different file name. [URL] ........
I thought I lost that original message so I rewrote everything - differently - then I saw the restore button so I'm leaving both phrasings.
I would like to know if there is a Macro that could ask me which workbook to copy from, select the sheet in that workbook called CAP (sheet will always have the same name but I need to copy the sheet from 70 different files/workbooks) copy the whole sheet with formatting and all, and then paste it in my active sheet on the new workbook I am in.
I want to take data about a particular partner we have (CAP) out of the monthly summaries for the last ~72 months (which each have their own file/workbook) and put them all in one workbook just for this partner, on each sheet by month still.
I am only interested in automating everything I have to do for 1 month, but in a way that would easily let me do it again for the next month, and the next and the next, 70 times.
But a Macro would still be easier than opening each file, going to the sheet I want, copying everything, going back to my new workbook, pasting everything, closing the extra workbook, then doing that ALL again 70 times.
There is a machine in our office that is running the same software as my machine. (XP SP3, Office 2k7, All MS Updates)
On my machine, as well as most others in the office, all the code works fine. On another machine, strange issues arise.
As I am running the Sun Dreamteam at work and have 25 different teams, is there a program that will work out how many points each team have once the points have been entered.
I have looked but cannot find anything, just wondering if someone out there has set one up that I can use.
When an .xls file is double clicked, Excel opens with the following error message:
'F:Program.xls’ could not be found. Check the spelling of the file name, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.
When the dialog box is clicked, another error message is displayed with the same words, but with the file name 'microsoft.xls'.
I have tried un-registering and re-registering Excel, and uninstalling and re-installing all of Office 2003.
KB 177248 refers to this problem and suggests: ....
How to call another program from excel using VBA, then excetue commands within it.
All the commands are text based, and the program will respond to it, but i am not sure how to automate this.
I would like to know if it is possible to run a macro from excel for another program and then have results imported into excel.
Currently I have a program called Reflection WRQ that I run a macro in and the results are captured in a text file. Then I run a macro in Excel that imports that text file. If possible I would like to have my macro in Excel run the command in Reflection and then import the results in Excel.
I have data in an Excel worksheet that needs to be entered into a proprietary programme. I believe this is possible using SendKeys but as I need to keep switching back to Excel to get the next bit of data I am unsure as to how to go about this. The program would already be open as it is a dial up situation and I would have to dial into the relevant site first.
View 4 Replies View RelatedI am making a Program search tool for my company. I would like the operator to type a part number into a cell. If the part number is valid (from a master list on a different sheet), then I would like to pull the information from the master list and populate a few cells on the search worksheet.
Example:
An operator types: "W3303-01" in a cell and clicks a button. The macro would populate cells on that worksheet with information from another worksheet that pertains to "W3303-01"
i m trying to use Excel program to keep track of the hours he is working. I know there is a way to do it but it has been many years since I used Excel and can not remember how to do it. He would like to insert the time (hours & minutes) he works in column A and have a sum in the next column.
View 7 Replies View Related