Posts by Mavyak

    Re: Month Function Missing


    I'm not sure how you have your date variable declared.


    If it is a String then try one of the following:


    Code
    1. Month(CDate(date_variable))


    Or


    Code
    1. Month("#" & date_variable & "#")

    I'd like to programatically hide a table I create. I've tried the following:


    Code
    1. Currentdb.tabledefs("BTN_Update_Log").Attributes = Currentdb.tabledefs("BTN_Update_Log").Attributes + dbhiddenobject


    And it works. The problem is, when I go to Tools --- Options --- show hidden objects, I still can't see the table. I noticed that when I right click on a table and select hidden that the "Flags" value in the MSysObject table changes to an "8". I tried:

    Code
    1. DoCMD.RunSQL "UPDATE MSysObjects SET MSysObjects.Flags = 8 WHERE (((MSysObjects.Name)='BTN_UPDATE_LOG'))"


    to update the flags field to an "8" for my newly created table but I got an error message telling me I needed to use an updateable query.


    How can I programatically do the exact equivalent of right clicking on a table and checking the "Hidden" checkbox in the lower left hand corner?

    I have a process that automatically imports a text file into my database. When the import happens, the table it is replacing gets renamed so that the current data always has the same table name. For housekeeping purposes I am writing some code that checks a custom property that I add to the backed up table when the new table is created. My problem is that the first time I run the code, everything is snappy and fast. Thereafter, the for loop on my TableDefs collection takes approximately 5 seconds per tablename to check. Does anyone know why that happens or better yet, how to speed it up? Here is the code:


    Re: Change mouse pointer to hand


    From below there is a limited set of options available.


    Perhaps an API call would work?

    Re: Right-click Menus Compiler Add-in


    I didn't really see the value in this at first but thought, "Whiskey Tango Hotel, I'll give it a shot."

    I don't know if I could function without it now. Very nice add-in. Muchos Appreciado! :music:

    The following code will take selected cells (if more than one cell is selected) or all cells from A2 and down until a blank cell is reached and place the contents of each cell in a notepad text file enclosed in quotation marks and separated by commas. The list is particularly useful when running an Access query with an In or Not In clause(i.e. ...Where Last_Name In(list of names from Excell goes here)).


    There may be some tweaking involved in the Shell statements depending on which version of Windows you are running.


    Here's some code I wrote today that I believe will be very reusable by me. With some very slight tweaking I suspect a lot of people can use it. As it is pasted below, the code takes data that is sorted by the first two columns (and the first two columns must be column A and column B) and scrolls through the rows. When it finds an instance where data is different in one of the two columns between rows, it copies all the like rows and pastes them in a new worksheet. Hopefully it comes in as handy for you as it does for me.


    Re: Splitting a column of text into 2 Different Columns


    This code will take the selected cells and split the names up placing the last name in the very next column followed by the first name in the column after that. It is contingent on there being only one comma in the selected cell.


    Code
    1. Public Sub split_them_up()
    2. For Each c In Selection
    3. c.Next = RTrim(Left(c.Value, InStr(1, c, ",") - 1))
    4. c.Next.Next = LTrim(Right(c.Value, Len(c) - InStr(c, ",")))
    5. Next c
    6. End Sub

    Re: List files with Hyperlinks


    I frequently receive inventory files from clients that have pieces of data within each file. I wrote some code that would search those files and return the file name to the comments of the cell containing the data but I would often have to manually go into the cell and copy/paste the file location to go find it. Slightly edited, this code will get put to use by me, probably on a daily basis.

    Much appreciated. :music:

    Here's my code if you're interested:

    Re: AutoSizing Comments via VBA


    I swear I tried that but I must've had something off because when I copied your code and pasted it, it worked like a champ.


    Much appreciated Andy! :music:

    I have written a macro that individually takes every cell in a selection and searches a folder on my harddrive for any file containing the value of the cell. The resulting list of files with matching criteria is then programmatically inserted into the comments of the particular cell whose value was found. The list is sometimes extensive. I need to find out how to programmatically set a cell's comment shape to autosize so that I don't have to individually show each cell's comment and then drag the corner to see all the files that contain the value of that cell. Sometimes I search in excess of 30 cells worth of values. The info I find in Excel's help is remarkably lacking. Any help on this would be great!

    Re: Automated Module Importing


    Kudos to Dave Hawley for posting a link to your proposed solution. I'll reiterate the link for those that find this thread. It is:
    http://www.ozgrid.com/VBA/excel-add-in-create.htm

    I opened a new workbook which already had my user form and module (I use the user form as a menu for the module subs) attached and then saved it as an add-in (*.xla). I then closed Excel and reopened it to get a fresh spreadsheet. i went to Tools - Addins and checked the box by my new addin. Then I went to my VBA code and saw my new add-in was there. i removed the form and module from my Book1.xls project since it is now in my add-in. I then saved the workbook as Book.xlt in my Excel\XLSTART folder so that whenever I open it the code is available. More importantly, however, whenever I open any Excel file, my code is now available.

    I've spent some time creating a module that speeds my every day tasks. I've set up Excel so that it is attached whenever I open a new spreadsheet (Book.xlt). However, I am frequently emailed spreadsheets from others and would like the functionality of my module without having ot import the module everytime. How do I force Excel to attach/import my module from my hard-drive to any Excel file I open? I understand naming conventions could come into play in the event someone emails me a spreadsheet that already has "Module1" attached. I'm sure I can come up with a unique name for my module should the automated import process actually be possible. How 'bout it folks? Who's got a procedure to help? :)