joe Posted September 25, 2007 Report Share Posted September 25, 2007 Over the past couple of years, we've accumulated Excel / Macro Express functions that were developed for different projects. Floyd and I want to begin the process of formalizing them into a library for other developers and users. To that end we would like your input as to what YOU want. What is it that you want to see Macro Express do for Excel? What kind of operations, tools, handlers, functions, features, and so forth, would make your automating Excel easier? Quote Link to comment Share on other sites More sharing options...
Cory Posted September 25, 2007 Report Share Posted September 25, 2007 One that I have on the to do list is to write a routine ot generate an integer date. I've done one for day of year that works for leap years and such but maybe there's a built in function in VBScript or at least a existing script that could be tailored for ME users. Quote Link to comment Share on other sites More sharing options...
johnboy691 Posted September 26, 2007 Report Share Posted September 26, 2007 Be nice to have the choice to be able to capture the formula in a cell instead of the result. Quote Link to comment Share on other sites More sharing options...
joe Posted September 26, 2007 Author Report Share Posted September 26, 2007 Be nice to have the choice to be able to capture the formula in a cell instead of the result. Do you mean that you want to get the formula in a cell and return it as a string to Macro Express? Quote Link to comment Share on other sites More sharing options...
joe Posted September 26, 2007 Author Report Share Posted September 26, 2007 One that I have on the to do list is to write a routine ot generate an integer date. I've done one for day of year that works for leap years and such but maybe there's a built in function in VBScript or at least a existing script that could be tailored for ME users. This is easily done. The command itself is a one-liner. Quote Link to comment Share on other sites More sharing options...
Cory Posted September 27, 2007 Report Share Posted September 27, 2007 Well then just tell me what it is and save me a lot of time. I have an instance where I will be needing it soon. Quote Link to comment Share on other sites More sharing options...
floyd Posted September 28, 2007 Report Share Posted September 28, 2007 VBScript lessons are beyond the scope of a Macro Express forum, but ... Within VBScript, you need to look at these three functions: DatePart - Returns the specified part of a given date. DateAdd - Returns a date to which a specified time interval has been added. DateDiff - Returns the number of intervals between two dates. What the heck does that mean; "intervals"? Well, an interval can be a year, quarter, month, day of year, day, weekday, week of year, hour, minute, or second. It simply depends on what you tell the command to return in relation to the function call. Hopefully Joe won't see this Quote Link to comment Share on other sites More sharing options...
joe Posted September 28, 2007 Author Report Share Posted September 28, 2007 Yes well, let's get back to the subject; Excel and Macro Express. What is it that you want to see Macro Express do for Excel? What kind of operations, tools, handlers, functions, features, and so forth, would make your automating Excel easier? Quote Link to comment Share on other sites More sharing options...
cyberchief Posted September 28, 2007 Report Share Posted September 28, 2007 Joe, Maybe this has been done before... and I am just too lazy to search, but I have a project on my table right now that requires me to view different tabs within an excel file. I need excel to launch a program, and I need to be able to navigate to a specific tab. Have you done this before? I am not an excel expert, but I don't see a way to navigate directly to a specific worksheet. Quote Link to comment Share on other sites More sharing options...
joe Posted September 28, 2007 Author Report Share Posted September 28, 2007 Joe, Maybe this has been done before... and I am just too lazy to search, but I have a project on my table right now that requires me to view different tabs within an excel file. I need excel to launch a program, and I need to be able to navigate to a specific tab. Have you done this before? I am not an excel expert, but I don't see a way to navigate directly to a specific worksheet. Cyberchief it's good to hear from you again. As a matter of fact take a look at this topic. It is exactly what you need IF you are referring to worksheets when you say TAB. This itty-bitty applet will allow you to activate not only an underlying workbook (if you have multiple workbooks open) but also whichever worksheet you need within the workbook. I strongly suggest that you download the Help_Active_Window.zip file and read it before doing anything else. It explains all in detail. Quote Link to comment Share on other sites More sharing options...
Cory Posted September 28, 2007 Report Share Posted September 28, 2007 I use F5 to navigate within Excel. Many folks don't realize it exists but it's a seriously handy way to specify ranges and cells in Excel. I can F5 and enter C18 to get acell, A1:D46 for a range or C:C for acolumn. And of course Sheet1!A12:C56. Also I've been known to create a worksheet in the workbook that pulls cell contents from various places and may even do calculations for my macro. This way I can control format and such in a way that the user won't muck up. Sorry, this is a bit off topic but I didn't know how else to respond to the guy. Quote Link to comment Share on other sites More sharing options...
cyberchief Posted September 28, 2007 Report Share Posted September 28, 2007 Joe, Maybe this has been done before... and I am just too lazy to search, but I have a project on my table right now that requires me to view different tabs within an excel file. I need excel to launch a program, and I need to be able to navigate to a specific tab. Have you done this before? I am not an excel expert, but I don't see a way to navigate directly to a specific worksheet. Thanks Joe! Been pretty busy so haven't had much time to post on here. I do read through the threads regularly... but don't always have time to reply. And thanks for making those corrections to the re-direct. Couldn't figure out what was going on. I saw your post before regarding the Excel functions. I didn't go into it deep enough because I thought it had to do with mulitple workbooks rather than worksheets. I am going to give it a look. Thanks!!! Quote Link to comment Share on other sites More sharing options...
cyberchief Posted September 28, 2007 Report Share Posted September 28, 2007 Thanks Joe! Been pretty busy so haven't had much time to post on here. I do read through the threads regularly... but don't always have time to reply. And thanks for making those corrections to the re-direct. Couldn't figure out what was going on. I saw your post before regarding the Excel functions. I didn't go into it deep enough because I thought it had to do with mulitple workbooks rather than worksheets. I am going to give it a look. Thanks!!! Joe, Working with that applet now. All I can say is... WOW!!! This is exactly what I needed. I can modify this to do quite a few things that I handn't been able to do in the past. Great work!!! Quote Link to comment Share on other sites More sharing options...
cyberchief Posted September 28, 2007 Report Share Posted September 28, 2007 Hey Joe... Another question for you. In the Example "Activate Workbook - Interactive"... Had a question on your coding. You set T11 to the path of the applet... and you set T14 to the applet name. Seems to me like this is a waste of variables. Couldn't you set T14 to be the entire path and application? In my world, variables are at a premium. I need to cut waste wherever I can because some of our programs are stretched to the brink. Let me know your thoughts on why it was programmed this way. Quote Link to comment Share on other sites More sharing options...
joe Posted September 28, 2007 Author Report Share Posted September 28, 2007 Hey Joe... Another question for you.In the Example "Activate Workbook - Interactive"... Had a question on your coding. You set T11 to the path of the applet... and you set T14 to the applet name. Seems to me like this is a waste of variables. Couldn't you set T14 to be the entire path and application? In my world, variables are at a premium. I need to cut waste wherever I can because some of our programs are stretched to the brink. Let me know your thoughts on why it was programmed this way. Good question! In this case, T14 is also used as the window title (caption) for the error MsgBox and for terminating the process. Look at lines 110 and line 118. Quote Link to comment Share on other sites More sharing options...
joe Posted October 29, 2007 Author Report Share Posted October 29, 2007 Topic bumper. Quote Link to comment Share on other sites More sharing options...
brad6499 Posted November 1, 2007 Report Share Posted November 1, 2007 Well since it applies to my last post, I thought I'd throw in a request for the Excel Functions Library to handle direct write to Excel files without opening them. I know how to create CSV or Text files that Excel can open, but if the file is never opened in Excel, Excel cannot link to the cells in these file types. For example: If I create a CSV file that has a variable value written to cell A5, another Excel file cannot reference the stored value in cell A5 unless it is open in Excel. If there is a workaround on this - PLEASE SHARE. If not, let's get this added. It sure would be nice for some of the crap I'm working on right now. Thanks! Brad Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.