|
Unfortunately I see the megathread has gone to archives. As the new intern at an italian brake designer, and the second youngest employee at the particular facility, I have of course been drafted as the go-to guy for making poo poo in VBA in Excel since people automatically assume "I know about the computers". I've been able to handle all requests up until now with searching google and copy/pasting, then loving around till it somehow magically works. Until, of course, this morning. I've been asked to, in the middle of a macro, pull up an AutoCAD drawing. I can handle the rest of the coding (I don't know if you could really call changing random poo poo and praying it works "coding", but whatever) by going back to previous projects... I just need to know how to open the draft using VBA code. Thanks guys.
|
# ? May 7, 2009 12:53 |
|
|
# ? May 11, 2024 16:18 |
|
Do you work for Brembo? Open it in AutoCAD I presume, or embedded in Excel somehow? Google tells me you're not the only person with this problem: click here for a horrible website G-Dub fucked around with this message at 20:46 on May 7, 2009 |
# ? May 7, 2009 20:43 |
|
If you can run an external command, you can often get away with running "start filename.ext" to get the shell to open it. i.e. Call Shell("start " & filename) (untested, I don't have access to windows machines) Although for future reference, there are alternatives if you need a little more freedom: Resolver One is a spreadsheet that uses python on .net internally: http://en.wikipedia.org/wiki/Resolver_One And if you're that way inclined, there are always libraries to interact with excel in other programming languages, even Haskell has one: http://alfa.di.uminho.pt/~jacome/HaExcel.html
|
# ? May 7, 2009 21:10 |
|
G-Dub posted:Do you work for Brembo? Sure do, theres a plant and an R&D facility out in BFE Michigan. Also thanks for the input guys, I'll let you know if I can bastardize it enough with my horrible excuse for code to get a halfway decent program running. EDIT: Yeah trying to embed it in an Excel macro. The first link looked promising but of course Excel doesn't understand the "AutoCAD.AcadDocument". I honestly haven't a clue how to begin to handle the second post there, but I'll research the commands and see what I can come up with. Adiabatic fucked around with this message at 13:07 on May 8, 2009 |
# ? May 8, 2009 12:23 |
|
Adiabatic posted:Yeah trying to embed it in an Excel macro. The first link looked promising but of course Excel doesn't understand the "AutoCAD.AcadDocument". I honestly haven't a clue how to begin to handle the second post there, but I'll research the commands and see what I can come up with. You would either have to import it (I don't remember how right now) or create it with CreateObject("AutoCAD.AcadDocument"). The second would be assigned to an Object and you wouldn't have autocomplete, but it would work.
|
# ? May 24, 2009 06:42 |
|
I want to know if its possible to select more than one item from a criteria when using the autofilter. Example: i have a list of fruit. I can use the autofilter to select only the apples, or only the oranges. But I want to see both apples AND oranges at the same time.
|
# ? May 25, 2009 12:04 |
|
Swink posted:I want to know if its possible to select more than one item from a criteria when using the autofilter. Yeah, go to "Custom" in the list. In the example, you set the first one to equals oranges, the radio button to "or", and select apples in the second.
|
# ? May 26, 2009 17:09 |
|
Ok... I have a spreadsheet with an install date in column A. I have another date in column C (which represents the date of a service call). The dates in C are always after the date in A. If an install date is 4/10/2006, then I need all dates in column C that are up to 7/10/2006 to be Quarter 1, from 7/11/2006 to 10/11/2006 to be Quarter 2, and so on. Basically a quarterly calender year, but instead of Jan 1st being the start of a quarter, the install date in column A is representing the start of the year. Edit: And to complicate things, is there a way to simply go Q1-Q20 instead of resetting to Q1 after the fourth quarter? Eg. Column A Column C Column D (this is the column I need represented!) 4/10/2006 4/11/2006 Qtr 1 4/10/2006 4/15/2006 Qtr 1 4/10/2006 8/20/2007 Qtr 7 EDIT: NEVERMIND! =CEILING((DATEDIF(A1,C1,"m")+3)/3,1) (ok another forum got it for me but still i'm feeling pretty drat good at 230am) DFWlly fucked around with this message at 07:24 on Jun 12, 2009 |
# ? Jun 12, 2009 04:41 |
|
Quick formula question that I can't figure out. I have some data that looks something like this: pre:YEAR 2009 TRO-01 2009 TRO-01 2009 TRO-01 2008 TRO-01 2006 TRO-01 2008 TRO-01 .... 2009 TRO-02 2009 TRO-03 2009 TRO-04 2008 TRO-04 2006 TRO-04 2008 TRO-04 ....etc,etc EDIT: I've tried something like code:
ilikechapstick fucked around with this message at 20:14 on Jul 6, 2009 |
# ? Jul 6, 2009 20:11 |
|
ilikechapstick posted:Quick formula question that I can't figure out. This should do the trick: code:
code:
|
# ? Jul 7, 2009 19:38 |
|
I think this is a little problem but then again I am entirely not sure. I'll make a thread out of this if it is more complicated than I think. Basically I have to clean up an Excel file with thousands of cells of data for a cell phone distributor. Here is my process: 1. In my Service Status Column I have to Filter and Delete invalid Statuses. 2. In the Sim Column I have to remove a Character from a string of numbers. 2. In my Service Number, IMEI Number, and SIM Number Column I have to replace blanks with a phrase, replace numbers greater or less than a certain value with a phrase, and replace all numbers that are duplicates with a phrase. What I do is.... By Filtering the Service Status Column I can easily remove the invalid Statuses. And again by Filtering I can mark the Blanks. I insert a separate column and I use the =LEFT(~,~) to output all characters I want from a SIM and not print the Character I would like to be excluded. Then I copy the Values over that and delete the old SIM Column. I insert 3 separate Columns to check the length of my SIM, IEMI, and Service Numbers. Then I filter those that are good which allows me to Flag the improper ones. I use Advance Filter to find the Unique Values of the SIM, IEMI, and Service Numbers and then Color them in a color to distinguish the Duplicates from the Unique. I filter what is not colored (the duplicates) and copy/paste them to another sheet. Then I use the Find and Replace on the copied/pasted numbers to replace all duplicates with the phrase I am suppose to. This whole process takes me about 20-45 minutes to do depending on the size of the spreadsheet and the amount of duplicates that I have to find and replace. My idea was to create a Macro to do the work for me. However, it is not working so well. I am going to retry it a couple of times but maybe you guys have a better idea for me. I have used the web to get me this far but I am struggling at finding a way to reduce my time in this lengthy process. Any suggestions? I really suck at this. >_< Edit: I have been doing more research and figured out that as 1 Macro to complete all these tasks would crash. However, what is working is creating a Macro for each set of Steps and then having a Master Macro run the whole thing. So far its going well. The code is basic but I am no expert. I'll keep you posted in case I have any questions. rekk1ess fucked around with this message at 17:01 on Jul 11, 2009 |
# ? Jul 10, 2009 22:07 |
|
This may seem like a stupid question, but how do I go about learning VBA in excel. I've been rigging macro's together by recording transofrmation and bastardising them with loops, but is there any way to get any sort of elegance out of the code? What's a good first step?
|
# ? Jul 13, 2009 07:02 |
|
If it's possible to create elegant works of art out of poop, then I'm sure you can make better code in vba. Learning the document model helps a lot - I have read VB&VBA in a nutshell, which is pretty useful and flawed at the same time. A good reference when you know what to look for. And I also have a copy of 'writing word macros'. I can't recall it being that useful. In the end cargo culting VBA code together works pretty well for the trivial tasks it can cope with
|
# ? Jul 13, 2009 12:06 |
|
I am trying to edit my macro to allow me to fill in the Blank Cells, once filtered, with 05-MM-Blank. I am having issues though. I don't think that when it runs the first cell is blank. Anyway around this. Sub Step_6A() ' ' Step_6A Macro ' Fill Service Number Blanks ' ActiveCell.FormulaR1C1 = "05-MM-Blank" Dim FirstRow As Long FirstRow = [A1].End(xlDown).Row Dim LastRow As Long LastRow = [A65536].End(xlUp).Row Range(Cells(FristRow, 12), Cells(LastRow, 12)).Select Selection.FillDown End Sub If it helps, this is what works: Sub Step_6A() ' ' Step_6A Macro ' Fills Service Number Blanks ' ' ActiveCell.FormulaR1C1 = "05-MM-Blank" Range("L5", "L85").Select Selection.FillDown End Sub However, L5 and L85 might change when I copy a new sheet into this. They might be L6 to L90 for example. I need this to adjust tot hat. Everything else seems to work fine. rekk1ess fucked around with this message at 22:04 on Jul 13, 2009 |
# ? Jul 13, 2009 14:18 |
|
The following somewhat does what I want. Problem is, that it re-labels my 1st Column as 05-MM-Blank. Is there anyway to further this and Offset the FirstRow? Sub Step_6A() ' ' Step_6A Macro ' Fills Service Number Blanks ' ' Dim LastRow As Long LastRow = [A65536].End(xlUp).Row Dim FirstRow As Long FirstRow = [L65536].End(xlUp).Row Range(Cells(FirstRow, 12), Cells(LastRow, 12)).Select ActiveCell.FormulaR1C1 = "05-MM-Blank" Selection.FillDown End Sub
|
# ? Jul 13, 2009 22:05 |
|
rekk1ess posted:The following somewhat does what I want. Problem is, that it re-labels my 1st Column as 05-MM-Blank. Is there anyway to further this and Offset the FirstRow? Not quite sure what you mean by offset the first row, but if you mean "refer to the row under FirstRow", you should try: FirstRow = [L65536].End(xlUp).Row.Offset(1, 0) That will return a reference to the row directly below what FirstRow is currently being set to.
|
# ? Jul 26, 2009 01:52 |
|
Is there anyway to highlight a whole row based on whether or not a value in the row is in a list posted in a column? For example if I have a list in the last Column that has Apple, Orange and Lime I want rows where the name of the fruit is in the row to be highlighted. Edit: Figured out what I wanted to do with Conditional Formatting. Strong Sauce fucked around with this message at 02:00 on Jul 30, 2009 |
# ? Jul 30, 2009 00:40 |
|
Is this something you're doing through a macro, or what? If you're using a macro try something like:code:
code:
|
# ? Jul 30, 2009 02:02 |
|
I have a worksheet that looks like this:code:
Thanks in advance for any help ya'll can provide
|
# ? Aug 26, 2009 19:08 |
|
gigabitnokie posted:I have a worksheet that looks like this: How automated would this need to be? Filter and copy/paste seems like the easiest, unless you're looking for an automated solution where every time you open the workbook the second sheet has only the "expired" info.
|
# ? Aug 28, 2009 14:50 |
|
(I guess this is the new Megathread?) I have multiple Excel files, each with a column of countries and some columns of data pertaining to those countries. File 1: code:
code:
|
# ? Sep 1, 2009 21:28 |
|
TheSpook posted:
is it too complicated for copy & paste?
|
# ? Sep 2, 2009 02:23 |
|
rekk posted:is it too complicated for copy & paste? The problem is this: Some of the files have the countries listed in different orders, some are missing countries entirely. I don't care about efficiency (it's only 45 files, so too much for a human but not much for a computer) -- my plan is this: code:
Again, runtime = schmuntime for this. Edit: I have it working for one file! Will post the code when finished. This seems like it'd be a very common problem. TheSpook fucked around with this message at 04:01 on Sep 2, 2009 |
# ? Sep 2, 2009 02:38 |
|
I am working on this while drinking a beer because it seems like a useful thing to have handy, I will be interested to see how you did it because I was making a lot of assumptions about your files (they would all have a header row, they wouldn't have duplicate data points, etc).
|
# ? Sep 2, 2009 04:16 |
|
Thanks! We can certainly discuss. Each of the files turned out to be significantly different, so I'm not using the outer "for each file f" loop. I'm opening each by hand and tweaking my code a little for each file. The code itself copies all rows of N columns from the source to the destination, matching on some Key Column (in this case, the country name). If no match is found, a new row (Country, {columns}) is appended to the destination file. Edit: Here's a version: http://gannon-house.com/matching_module.txt. This is a mess of my own work with snippets from the Internet. TheSpook fucked around with this message at 05:13 on Sep 2, 2009 |
# ? Sep 2, 2009 05:04 |
|
Here's what appears to be an annoying Excel 2007 bug. Got a pivot that's created each time using VBA, so it's not a case of cached data that's not there any more. Got a form where the combobox is populated using the pivotitems from the pivotfield, so it's not a case of the filter not actually being in the pivotfield. Try and set the visible property of the pivotitem, and it throws a fit. Basically, if you've got a pivotitem that's a UK format date (dd/mm/yyyy), it seems impossible to set a load of the properties. Throws up error 2042 instead. Can still get at the name, caption, etc, just not the properties I need. Ooooooooooooooh, thinking about this, I wonder what would happen if I set the name to something else to start with, then tried to set the visible property?
|
# ? Sep 9, 2009 11:33 |
|
MattWPBS posted:Ooooooooooooooh, thinking about this, I wonder what would happen if I set the name to something else to start with, then tried to set the visible property? For anyone who runs into the problem, this works. Added "str" to the front of the name at the start, took it off at the end after setting the visible property. Bloody 2007.
|
# ? Sep 10, 2009 14:33 |
|
I have a question when about exporting data from a website into excel. I'm still new to it so I'm not sure how I would be able to do this, I know I can click on the data tab > get external data > from the web and grab a table off a website but I need to get more complex than that. I want to export data off a website and then have excel arrange the date/company/name/etc in an easy way so I can then upload it somewhere else. For example, say I want to get the data off this website. I want Excel to take the Company Name, Person's name, address, city, state, zip, etc and upload it into each corresponding column. In the end it should look like, code:
Edit - Actually, a directory like this is more of what I'm looking to export, http://www.directoriesusa.com/sampleReport.html. Since the data/address information is already organized with Address, City, Zip, etc, I imagine it would be easier to export it the way I want right? Dr. Video Games 0089 fucked around with this message at 10:02 on Sep 16, 2009 |
# ? Sep 16, 2009 09:46 |
|
I'm trying to make a Worksheet in Excel that pastes values from certain cells into successive rows of another worksheet, according to a Change event [1]. This is important because some people who are basically computer illiterate are going to be the primary users of the Workbook and I want it to be pretty easy for them to make order forms from a list of items sold by different vendors at different prices. I've made little progress so far. I made a small amount of headway yesterday on the 'copying into another worksheet' part by using the name manager to define a name that had the values I was interested in. For some frustrating reason, this did not work at all when I tried to make the name definition column absolute instead of fully absolute (e.g., one cell in the defined name would be $A1 instead of $A$1). In this case, the same (incorrect) cells were called by the name no matter where on the spreadsheet I called the change event. I've had no success on the 'into successive rows' part of the problem, and no idea how I might go about deletion. [1] Basically I have a list of item names, prices, and quantities. I want to copy the name and price into another worksheet if the quantity is changed to something greater than 0, and removed from the worksheet if the quantity is zero or less. smug forum asshole fucked around with this message at 20:57 on Sep 22, 2009 |
# ? Sep 22, 2009 20:55 |
|
I'd just like to throw this out to anyone trying to figure out how to do something in Excel since at a job may years ago I wrote tons of Excel macros. The easiest way to figure out how to code something in Excel is to start the macro recorder, do what you want, stop it and look at the code that gets generated. It will be overly verbose, but just find the part that is needed and adapt to your needs. It'll save you tons of time searching documentation and the internet.
|
# ? Sep 22, 2009 21:47 |
|
I have a problem that I feel like should have an obvious, simple solution, but am coming up short. I have a column with address information in the format of "[City] [State] [Zip]" and need to separate them into their own columns in order to export it from Access into ODBC. It would normally be a simple matter of delimiting the column by spaces, but the problem is that cities like San Francisco don't allow for something so easy. Example: code:
|
# ? Sep 30, 2009 19:49 |
|
shotgunfilibuster posted:I have a problem that I feel like should have an obvious, simple solution, but am coming up short. I have a column with address information in the format of "[City] [State] [Zip]" and need to separate them into their own columns in order to export it from Access into ODBC. It would normally be a simple matter of delimiting the column by spaces, but the problem is that cities like San Francisco don't allow for something so easy. There's probably a better way to do this, but as long as the zip is in ##### or #####-#### format (no spaces or this won't work), this works, it's quick and dirty, but it works: code:
Just-In-Timeberlake fucked around with this message at 14:58 on Oct 1, 2009 |
# ? Oct 1, 2009 00:33 |
|
gently caress yeah, that worked! Thanks! A minor bit of tweaking of my table and some clean-up after running the script and it did just what I wanted.
|
# ? Oct 1, 2009 19:50 |
|
Is there a way to apply string manipulation functions to array blocks? I was trying to do this: quote:1 Dog asdf Sum the left column if the right column starts with "dog" in case insensitive. I guess you can do this with multi-column retardation and (LOWER, ISERROR, FIND, IF) functions pretty trivially... is there a way to do this in a oneshot? Incidentally it turns out you can do a case sensitive with SUMIF(B1:B5, "dog*", A1:A5) - where is criteria stuff like this documented?
|
# ? Oct 2, 2009 00:55 |
|
If any of you excel folks would like to take a look at a macro/script project I'm working on, this may be your chance to pick up $15 in SA-Mart: http://forums.somethingawful.com/showthread.php?threadid=3221648
|
# ? Oct 29, 2009 16:24 |
|
Mark Kidd posted:If any of you excel folks would like to take a look at a macro/script project I'm working on, this may be your chance to pick up $15 in SA-Mart: http://forums.somethingawful.com/showthread.php?threadid=3221648 Ha ha, I remember you. I sent you a quote once for an online ticket booking system. You never responded after the quote and it all makes perfect sense now. You'll probably shat your pants at the quote. I can't believe you're finding suckers to do your day job for so cheap.
|
# ? Oct 30, 2009 05:01 |
|
geetee posted:Ha ha, I remember you. I sent you a quote once for an online ticket booking system. You never responded after the quote and it all makes perfect sense now. You'll probably shat your pants at the quote. I can't believe you're finding suckers to do your day job for so cheap. Howdy, yeah that was more than we had on hand to spend, so I wound up sticking with a much shallower integration with our ticketing service and am keeping an eye out for future opportunities to build that out a bit more. I did send you a note to that effect though, sorry to hear that you didn't get it. Unfortunately it's not really anyone's day job in my office to handle this kind of work so sometimes it does become my responsibility to hunt up someone who has expertise we otherwise don't. In my line of work, making an airtight case for a new expense can just mean that we have what we need to begin talking to our donors and adding it to proposal budgets so as to try to find funding. One of our accomplishments in 2009 was funding, finding, and retaining IT consultants. Our organization is working carefully for 2010 to raise funds to build relationships with database and web developers, two other professions I have had to dole out work to piecemeal. So, one step at a time.
|
# ? Oct 30, 2009 13:26 |
|
I have a spreadsheet with the following bit of data: The user of the spreadsheet will enter data under the Actual column for Calls, Sales, and Hours. CPH and SPH are just Calls Per Hour and Sales Per Hour. This part is already figured out. What I need: The third column is Year To Date. These cells should reflect the number entered into the Actual column. This is of course, easy enough. However, each week, this worksheet will be duplicated and the YTD column will need to be a running total based on the previous week. Each week the data in the Actual column will change. Can someone help me out in getting a formula together for this? I'd rather not do it in VBA but I can if necessary.
|
# ? Nov 23, 2009 17:58 |
|
Is there a simple excel function to evaluate a text string as a function? Here's a possible example sheet:pre:Sheet To Evaluate Cell To Evaluate Result Sheet2 A1 =EvalStr("='" & T(A2) & "'!" & T(B2)) Sheet7 D4 =EvalStr("='" & T(A3) & "'!" & T(B3)) Edit: Looks like I'll have to read up on VB Script. From this pcreview forum: code:
Little_Yellow_Duck fucked around with this message at 20:35 on Nov 23, 2009 |
# ? Nov 23, 2009 19:58 |
|
|
# ? May 11, 2024 16:18 |
|
Chinaski posted:
My first inclination would be to talk you out of doing it on multiple sheets. Failing that, however, you can sum cells across worksheets. For example, this formula =SUM(Sheet1:Sheet3!A1) will sum all of the A1 cells on the worksheets that are in between Sheet1 and Sheet3 (inclusive).
|
# ? Nov 23, 2009 22:42 |