Locate Files Of Predefined Extension, In Predefined Location & List In Spreadsheet
Oct 22, 2008
I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
Struggling a bit on this one! I have tried the index function, but doesnt quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is Index is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
I've seen a lot of traffic around running macro's on a clock at regular intervals. But what if I want to run a Macro (Macro1) at 8:55, 9:55, 11:55, 12:55, 13:55, 16:55 and another Macro (Macro2) at the top of each of those hours? The string of times is not consecutive, so I have built a table to define the release times. At each of these times I need to run a macros, and I'm not sure where to begin.
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Some criteria:
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
I am having a set of names in one column and would like to print various combination of names in to another column. In sample data set, there are 30 names of entries in column B and like to print 4 data set i.e. 1st three set of combination of column B print into the column C similarly for other set of six, twelve and all kind of combination. Please find the detail in attached excel sheet.
I have a button in my worksheet. When clicked on I want to change the text in this button so far so good. I have however a problem how to return to the cell that was active, the moment I clicked the button. so far I have the following code
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.
I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top. The first inserts a row, which I did by recording a macro and then copying the code into the button.
The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".
After all the work and calculations applied to data from various macros, I would like to have a final macro simply insert a title row, always the top row with various titles. Theres 11 columns, thus it would be great for the code to simply insert the row with predefined bolded titles, such as T1, T2, T3....T11. These 11 cells would than share then same bolded bottom line, making it visibly separated from the rest of the rows. I know this is easy, and Ive looked throughout the forum, but I can find things close, but not close enough.
I want B1 to copy from A1, but if A1 contains a certain text string, then remove that text string and keep everything else. For example, I want B1 to remove "ru" "la" "fm" and "mu" so if A1 is 5464ru-xl then B1 will return 5464-xl or if A1 is 36944la-s then B1 returns 36994-s
I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.
What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.
I have a workbook which has a variety of command buttons with assigned macros, one of which is a product detail button which redirects to a new worksheet.
Is it possible to be able to click on the button, display a new window (the new worksheet), but only display a section of it?
For example, I have 5 products. Each of the five products are detailed on the same worksheet and I have a button for each. I would like Button 1 to open up the details for product 1 only and button 2 to open up product 2 details etc.
I could go to individual sheets, but if I had 2000 products, the book would be a little large.
Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.
I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.
I've attached a sample workbook.
If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.
I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.
I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.
I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.
Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.
The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.
A1 B1 C1 D1 State BUName InsuranceCompanyName Claim ROnumber Renter Last Name
The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.
So i want to be able to send a single Excel sheet to a recipient, not already pre-defined in the VBA. I am trying to get to where i can click a button/link and a window pops up, asking me to enter recipient's email address. When I enter the address and press- 'Send', the email is sent using Outlook.
I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column: 000122042009ABCDEFG00567
Required result: 0001|22042009|ABCDEFG|00567
when running below macro; result shows: 1|22042009|ABCDEFG|567 (Beginning zeros figures of the first and last column disappear)
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example: Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
I have a few thousand files that all have the extension of .kdm.kdm. I need to change them all to .kdm.xml. All of the files are in a single directory on my hard drive. I know there's a way to do this in VBA that's simple, but I can't seem to locate it in any of the books I have....
I have a folder containing 1000's of files. All the files end in .txt (for example test.txt) but are actually excel files. When I go to open the files with excel I get the following warning:
The file you are trying to open is in a different format than specified by the file's extension. Verify that the file is not corrupt and from a trusted source before opening the file. Do you want to continue?
I click "yes" and it opens fine as an excel file.
I want to merge all these files one after another into one file.
I know how to delete duplicates using the function under the data tab, but how can i locate them. For instance, a spreadsheet with a list of PO numbers, how can i do a search to see if it is showing the same PO number multiple times?
Is there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
