|
Schedule a repeating meeting in Outlook and trigger VBA off a combination of Category and Subject for your taskscheduler functionality!
|
# ? Aug 27, 2015 06:17 |
|
|
# ? May 11, 2024 06:32 |
|
fosborb posted:Schedule a repeating meeting in Outlook and trigger VBA off a combination of Category and Subject for your taskscheduler functionality! My work uses Lotus Notes.
|
# ? Aug 27, 2015 16:01 |
|
Yay! This thread is still active. Nice. I'm having a bit of a VB coding issue with my Userform. I created a Userform to track some tech issues. It works great, but one feature it needs is automatically generated incident numbers. Screenshot I currently have it set up so a form pops up, asks for a bunch of incident details, then populates it into the worksheet once a 'Submit' button is pressed. That works just fine. But I'd like to set it up so a number (ranging from 0001-5000) is sequentially generated and inserted into the cell beneath the 'Issue No.' header every time a user clicks the 'Submit' button. Any suggestions? I've tried most of the solutions that I've been able to find online, but I can't quite get it to work properly. Here's my code for the UserForm code:
melon cat fucked around with this message at 02:43 on Aug 28, 2015 |
# ? Aug 28, 2015 00:45 |
|
Hughmoris posted:My work uses Lotus Notes. So this is really embarrassing but if you're using Lotus Notes there are several registered DLLs you can include in your VBA to automate data pulls and other interactions.
|
# ? Aug 28, 2015 05:33 |
|
melon cat posted:
Ignoring the main question because I'd need to be in front of excel to be certain my suggestion would work, the second question is change this line: code:
code:
code:
|
# ? Aug 28, 2015 08:23 |
|
*Ignore, figured out the problem.
Hughmoris fucked around with this message at 05:04 on Aug 29, 2015 |
# ? Aug 29, 2015 04:40 |
|
Hughmoris posted:VBA is kicking my butt just trying to accomplish basic tasks. I picked up the basics of Python and Perl pretty easily but I'm having a hard time trying to manipulate Excel using VBA. here, try this: Writing Excel Macros with VBA, 2nd Edition https://www.amazon.com/dp/0596003595/ref=cm_sw_r_awd_s7s4vbSHRTS2B
|
# ? Aug 29, 2015 05:04 |
|
fosborb posted:here, try this: Thanks for this. I'll see if I can get my hands on it. In the meantime, I'm getting tripped up. If I run this code with worksheets("ALERTEXTRACT") selected in the background, it works fine. If I run it with Worksheets("Sheet1") selected, it gives me the runtime error '1004': Method 'Range' of Object'_worksheet' failed. Why does it toss me that error even though I'm explicitly naming what worksheets I want to read from? Also, is this code considered "good practice" for iterating over a column of unknown length?? Visual Basic .NET code:
Hughmoris fucked around with this message at 01:42 on Aug 30, 2015 |
# ? Aug 30, 2015 01:22 |
|
This code will avoid the active sheet issue: code:
Other tips: Use Application.ScreenUpdating = False to avoid draw cycles eating into your execution time. use Application.Calculation = xlClaculationManual for the same reason. Write your output to an array and then write the entire array to a output range. This is a huge savings, but requires some fuckery with rediming and transposing arrays. This is pretty hacky and needs to be cleaned up to match your own code (I didn't worry about offseting for the physician code, etc) but it should get you most of the way there. My parameters were just to test a variety of result sets. The CTimer class was for benchmarking too. You'll certainly want to replace the UsedRange function with something better -- it was reliable for a proof of concept but is unreliable in real life. Visual Basic .NET code:
code:
|
# ? Aug 30, 2015 03:30 |
|
fosborb posted:here, try this: I should add that this is for Excel 2002. It is still the best laid out reference for Excel VBA object models and methods that I have ever read, but you are going to miss out on several new features like sparklines, slicers, and ribbons. Personally, by the time I need to start worrying about hooking into slicers with VBA the project is probably way out of scope already.
|
# ? Aug 30, 2015 03:46 |
|
fosborb posted:Great VBA stuff... Wow, thank you for taking the time to write that up. The read file for this project is of unknown length but typically 220k+ rows. I'll put up the final version of the script when I finish it, for critiquing. Also, that benchmarking tool looks pretty handy. I'm curious how performing these actions natively within VBA will compare to my Perl script that accomplishes the same thing.
|
# ? Aug 30, 2015 04:12 |
|
Here's a simple timer with little overhead. http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code
|
# ? Aug 30, 2015 04:16 |
|
I would also add that depending on your array dimensions and string sizes be careful if you are using 32 bit office. Its pretty easy to bust the memory limit as vba memory management doesnt seem to be that efficient and garbage collection doesn't always seem to work correctly. If you use the code you posted taking the last cell in the column and doing xlUp allows you to account for any blank rows. If you're really getting into writing a lot of complex vba (and don't end up hating its limitations and writing it all in a library instead) make a few "I always use this" classes or modules. Error handling, app initialization, 'everything off' 'everything on' methods (screen updating etc), unfilter all, adding context menu buttons and so on that you can just import into each new project. Always nice to save time.
|
# ? Aug 30, 2015 09:48 |
|
Hughmoris posted:I'm curious how performing these actions natively within VBA will compare to my Perl script that accomplishes the same thing. Your read file is pipe-delimited text, not an xls/xlsx, right? I'd expect Perl to smash the gently caress out of VBA. A regex looking for "PHYSICIAN CONSULT" after the 16th | and looking back to the beginning of the line to the first | should return exactly what you want. Cycle through that and kick it out to whatever. VBA is great at enabling the nerd hired into enterprise environments (but not under IT) to do some things that would normally require budget approval and IT controls. Which is why Microsoft tries so drat hard to kill it and also why Microsoft will never be able to kill it. There is almost always a better, easier-to-maintain long term solution to complex VBA, but those solutions almost always require more resources than "call that kid who's good at Excel"
|
# ? Aug 30, 2015 12:20 |
|
fosborb posted:AutoFilter is going to work faster than testing each cell in the range in VBA. My guess is this is because native Excel functions can multithread, but your VBA code can't (without some insane trickery or writing your own libraries) That's probably part of it, but also Excel's own functionality is compiled code so faster, it probably has more direct access to the data in the cells so can do so faster as well, and it doesn't need to marshall every single cell's value back and forth between Excel and VBA (which is a huge cost, as you've noted). Also, you probably shouldn't use "Redim Preserve" so much because it's quite costly (copies the whole array). If you can't get a count first (which you can here, actually), start off with a minimum size and increase by doubling or adding a chunk when you reach capacity, not one entry at a time. fosborb posted:VBA is great at enabling the nerd hired into enterprise environments (but not under IT) to do some things that would normally require budget approval and IT controls. Which is why Microsoft tries so drat hard to kill it and also why Microsoft will never be able to kill it. There is almost always a better, easier-to-maintain long term solution to complex VBA, but those solutions almost always require more resources than "call that kid who's good at Excel" Yeah it's a great way for businesses to trade development costs for hidden technical debt. It's especially bad if your business is really small and doesn't have proper IT, and they actually believe that your Excel poo poo pile is totally normal and how things should work.
|
# ? Aug 30, 2015 16:16 |
|
headcas3 posted:Ignoring the main question because I'd need to be in front of excel to be certain my suggestion would work, the second question is change this line: Thanks very much for this! I'll give it a go. I'm still trying to get that number generator working, so I'll keep you all updated on that issue if I'm able to solve it.
|
# ? Aug 30, 2015 18:19 |
|
fosborb posted:Your read file is pipe-delimited text, not an xls/xlsx, right? I'd expect Perl to smash the gently caress out of VBA. A regex looking for "PHYSICIAN CONSULT" after the 16th | and looking back to the beginning of the line to the first | should return exactly what you want. Cycle through that and kick it out to whatever. Yeah, Perl flies through the file but the kicker is that I also need to print these sheets to different network printers and VBA seems to handle that like a champ. Here is where I ended up last night after staying up way too late writing code. This is meat of it: Visual Basic .NET code:
|
# ? Aug 30, 2015 22:24 |
|
Out of interest have you tried loading into and looping through an array and setting the value of each cell individually? Never tried it at what i assume is (200k x 4) 800k lines but for an order of magnitude lower i believe it can be quicker. Turn off calculations and screen updating.
|
# ? Sep 1, 2015 05:31 |
|
Just trying to brainstorm a few different methods to achieve an excel goal here: For each item (with Type X and Price n) in Range A, find all items in Range B that exactly match Type X and are within 5 dollars of price n. I have a "dumb" method to do similar operations on a smaller scale which is basically "for each item in Range A, copy range B in full and delete each row which does not match" into separate files. That's ok when I am doing 30 searches into 4000 rows, but this one I am thinking now is going to be 20-30 searches into 20,000 rows. How can I make this more efficient (and not copy 20,000 rows 30 times)? I've done dictionaries in the past but I'm decidedly rusty. The overarching problem to solve here is finding wines from master list B that would be comparable to wines from little list A.
|
# ? Sep 2, 2015 19:42 |
|
A pretty easy way to just get that information in front of you would be to use filters (Filter your Type column by X and your price column using the "Between" option from n-5 to n+5). If you have VBA abilities, you could probably whip up a macro to loop over all of the wines in list A and duplicate the sheet with appropriate filters selected each time. Do you need to do something more once you have your information?
khazar sansculotte fucked around with this message at 20:01 on Sep 2, 2015 |
# ? Sep 2, 2015 19:59 |
|
Yeah, filters would be fine if I was looking at one wine at a time, but I ultimately want to present a flat, macroless file to someone less excel savvy, and it would probably be like 20 different wines that I want comparative lists for. So 20,000 rows x 20 wines, even if filtered, is going to be a massive file. VBA is going to be the way, I'm looking for a way that is more elegant than "copy 20,000 lines, delete 19,950 of them, repeat 20 times."
|
# ? Sep 2, 2015 20:44 |
|
Cast_No_Shadow posted:Out of interest have you tried loading into and looping through an array and setting the value of each cell individually? Never tried it at what i assume is (200k x 4) 800k lines but for an order of magnitude lower i believe it can be quicker. Turn off calculations and screen updating. I haven't tried that method yet but I'm going to. Once I have a full working version using my current method, I'm going start over and try different techniques to improve the speed. Right now it takes about 12 seconds to run, but the starting file is roughly (200k x 15) cells.
|
# ? Sep 2, 2015 23:11 |
|
Turkeybone posted:Yeah, filters would be fine if I was looking at one wine at a time, but I ultimately want to present a flat, macroless file to someone less excel savvy, and it would probably be like 20 different wines that I want comparative lists for. So 20,000 rows x 20 wines, even if filtered, is going to be a massive file. VBA is going to be the way, I'm looking for a way that is more elegant than "copy 20,000 lines, delete 19,950 of them, repeat 20 times." You could just output the results of each filtering operation to a new sheet in a new workbook, no need to carry the macro over. I guess this is not really that much more elegant, it's more "filter 19,950 rows out of 20,000, copy the result, repeat 20 times." The only other VBA solution that comes to mind is putting your 20 wines across the top row and looping through your master list 20 times looking for price and type matches, and then inserting those wine names into successive rows below the wine they're being compared against. Do you absolutely need to have all 20 outputs in front of your end user at the same time? Or could they pick a wine from a drop down list and then the output updates with the relevant info? If the latter you don't need VBA at all, you could just jazz up a edit: don't need a pivot table, a regular table can do fine khazar sansculotte fucked around with this message at 19:03 on Sep 3, 2015 |
# ? Sep 3, 2015 18:27 |
|
gently caress my life My boss has asked me to merge two Workbooks together. One workbook as 130 columns, the other 129. Besides the difference in columns, some column headers have different labels. So the merged file will have more than 130 columns. How would you all tackle this? It seems like it's going to be a miserably manual process but I just can't wrap my head around an easy (or least painful) way of doing it. I've been asked to turn it around by COB tomorrow. My first approach is to simply begin copying each column one by one. If I find a column that doesn't match oh gently caress this is going to suck. If it matters, One of the workbooks is 139 rows The other is 1,950 rows Thoughts? I've never had to do anything with so many columns.
|
# ? Sep 3, 2015 21:53 |
|
Use the consolidate tool.
|
# ? Sep 3, 2015 21:56 |
|
I've looked into a bit but I don't know if Consolidate will to work. It seems like the header data needs to be in the same order for this to work. Other instructions I saw indicate that there can be no blank rows, and I've got a lot of blank rows in my data.
|
# ? Sep 4, 2015 15:09 |
|
me your dad posted:I've looked into a bit but I don't know if Consolidate will to work. It seems like the header data needs to be in the same order for this to work. Other instructions I saw indicate that there can be no blank rows, and I've got a lot of blank rows in my data. Here's what I created to sort column headers... I changed it to rely on fewer functions, so hopefully it still works. To use: create a sheet named "Master" with the correct column headings in row 1. Run the code. Each sheet will now have a corresponding sheet with the name "SheetXXX Sorted" This requires a reference to the Microsoft Scripting Runtime library. code:
|
# ? Sep 4, 2015 15:15 |
|
Ronald McReagan posted:You could just output the results of each filtering operation to a new sheet in a new workbook, no need to carry the macro over. I guess this is not really that much more elegant, it's more "filter 19,950 rows out of 20,000, copy the result, repeat 20 times." The only other VBA solution that comes to mind is putting your 20 wines across the top row and looping through your master list 20 times looking for price and type matches, and then inserting those wine names into successive rows below the wine they're being compared against. Hmm.. yeah I am usually of the mindset to code through but honestly the drop-down is probably more useful for the end user. Or the other thing is that ultimately, even if "filter 19950 out of 20,000" is ugly, I'm only going to do it once. So I maybe I should just suck it up and do it that way, and then go spend those ten minutes making a coffee or something.
|
# ? Sep 4, 2015 16:00 |
|
schmagekie posted:Here's what I created to sort column headers... I changed it to rely on fewer functions, so hopefully it still works. Thanks. I really appreciate it. I've decided to just do it manually. This is going to be a nightmare either way and I've settled into accepting this will be my day. This really falls outside of my normal job too, which adds insult to a poo poo situation.
|
# ? Sep 4, 2015 16:12 |
|
me your dad posted:Thanks. I really appreciate it. I've decided to just do it manually. This is going to be a nightmare either way and I've settled into accepting this will be my day. This really falls outside of my normal job too, which adds insult to a poo poo situation. Good luck! I give up after about 10 columns, hence writing that.
|
# ? Sep 4, 2015 16:26 |
|
I've got an excel sheet with a few columns of values in. I'd like to use the values to lookup other values in an SQL database and populate other columns in the sheet for them. Simple example: Column A contains integers corresponding to the ID column in a table of Foos in my database, I'd like to lookup their names in the database using the ID, and fill the names in in column B. Is this do-able? p.s. I tried loading in the entire table in a seperate sheet and just doing lookups in it, but there are too many rows.
|
# ? Sep 4, 2015 17:37 |
|
I have a big list of string values and want to categorize them. My idea is make a list of keywords to search each string for, and go from there. Colors Fruits Red Apple Blue Pear Gray Melon Pink Pepper So if any of those 'colors' are in the string, the category becomes 'Colors', but if the substring 'Pear' is found, the category becomes 'Fruits'. What do I need to learn to do? The lookup and the substring thing, right? I want to do it this way instead of hammering out some massive compound if/else formula.
|
# ? Sep 9, 2015 20:11 |
|
I'm a bit stumped on a problem. How do I execute a macro after a user clicks the Refresh All button? I have a fully functioning macro intended but I'd like to have it key off the action of Refresh All, instead of the user having to manually launch the macro. My googling is not turning up any examples I can get working. *Disregard. I got it working. Here's some info I used incase anyone needs to reference it in the future: https://excelandaccess.wordpress.com/2014/01/18/create-beforeafter-query-update-events/ Hughmoris fucked around with this message at 01:43 on Sep 16, 2015 |
# ? Sep 16, 2015 00:21 |
|
I need to populate a column with seemingly random time stamps. I would need to establish a range for the dates - something like, between October 14, 2014 and the current date. The time stamp would need to be in this format: 9/5/2015 16:08 No time stamp should be the same. Is this possible?
|
# ? Sep 17, 2015 14:32 |
|
=RANDBETWEEN(4192600000,NOW()*100000)/100000 There is a nonzero chance of a duplicate. 0.1% in 50,000 generations. To avoid duplicates, you could generate your list, copy/paste as values, then use Data > Remove Duplicates fosborb fucked around with this message at 14:53 on Sep 17, 2015 |
# ? Sep 17, 2015 14:49 |
|
fosborb posted:=RANDBETWEEN(4192600000,NOW()*100000)/100000 Thank you! I entered it and then formatted the cells to date and time and it worked great. Can you please tell me how this works? For example, what is 4192600000? What are the other numbers?
|
# ? Sep 17, 2015 15:02 |
|
Excel records dates as a number of days since 1/0/1900 and records times as a fraction of a day. 0.000001 is about 1 second. (It's actually 1/86400 but close enough for this) So 1.000001 is 1/1/1900 12:00:01 AM. Randbetween only does whole numbers, so we multiply the date.time values by 100000, get the random number, and then divide by the same to convert back to date.time. This is really useful knowledge if you work with times a lot in excel (and also how you can get charts of time into whole minute increments) but it's completely useless in every other area of your life because no other program records datetime in this dumb way.
|
# ? Sep 17, 2015 19:11 |
|
A client has asked me whether it is possible in an Excel macro to specify a user account with which to print. I have never done macros, can be considered clueless. He has an Excel file that should only print out to a selected printer (that printer being loaded with the very special paper to be used). He's limited the available printers to that one. Now he wants to make sure that that very special paper does not get used otherwise, without impacting daily work of the users, who should have a all the other, normal, printers available. So I came up with the idea of limiting usage of that specific printer to a certain domain user account. Which leads to the question above. Is it possible? Would it mean account credentials stored in the macro?
|
# ? Sep 18, 2015 18:41 |
|
You can grab username from windows pretty easily. Millions of examples online. Then its just if username = x use this printer? Unsure on what exactly your trying to do from there?
|
# ? Sep 18, 2015 21:52 |
|
|
# ? May 11, 2024 06:32 |
|
The idea would be to not grab the user of the current session, but use a special user account that is exclusively allowed (on the print server) to use the special printer.
|
# ? Sep 18, 2015 22:40 |