Excel 2010 :: Searching For Files By Cell Value And Returning Values
Jun 24, 2014
I need a formula (but most likely a VBA macro) that will search through a folder for a file than get data from that file. The files are named in systematic way, but I need the entire formula to work from inputting a mold number in one cell. E.g. I input 6291 in cell A2 the vba macro searches for file “6291 mold.xlsx” and returns a range of numbers as well as pictures in specified cells. Is this possible? If so how?
The closest thing I have found is VBA macro that retrieves a list of media files in a folder, I listed the code below.
My Excel 2010 spreadsheet contains client data like the below:
A B C D E
What I'm trying to get from this is a personally addressed email with 2 attachments, one will be standard to all recipients and one will be unique and specific to that recipient. The filename of the unique attachment will contain the reference but will have some other stuff in the filename as well on either side that I will not be able to remove.
In terms of the file locations the unique ones will be in subfolders of the folder holding this workbook and the generic one will be in the same folder as the this workbook.This is some adapted code that solved a similar problem (in Excel 2007 though) on a different website.
VB: Sub Mail_Report() Dim OutApp As Object Dim OutMail As Object [code]....
In that case the file started with the "reference" field but in my case it is in the middle and the formats vary depending on the provider, there will only be a maximum of 5 providers but I would like to avoid 5 different macros if at all possible so I need a search function of some sort.
It will perform the sub-totalling for the column that has the current active cell
When I select Column I
It does..... -for each blue cell it finds it provides a total of all the white cells bellow it -for each yellow cell it find it provides a total of all the blue cells bellow it until it reaches a yellow cell
It works backwards, so not exactly as I've just described, but that isn't the problem
Problem is - UK Excel 2010 - results are as expected US Excel 2010 - returns zero values for totals
We've possibly narrowed the problem down to when it looks at cell properties, more specifically -- If Cells(rowX, 1).Interior.ColorIndex = 20 Then
How to get results in blue and yellow cells when you select a cell in column K then execute the CreateTotals macro.
I have created a worksheet with 7 Tabs. 6 of the tabs are tables of existing parts and their catalog information. The Front Sheet I want to be a place where people can select data via a drop down or typed in, and in turn have that headsheet display the results of the search.
I have the 6 tabs broken down by part type, and figured those can easily enough be the first choice on my worksheet. So by picking that I can direct excel to which tab we want to run the search on, but ater that I'm drawing a blank.
For an example, say I'm looking for a Elbow that has a 6 size on one end. I would like to use the drop down to choose "Elbows" and then like to select/type 6 in the End 1 size box and have Excel show me all the reslts from the Elbow Tab that match that criteria.
I have been trying to find a formula for this but I have yet to find one that works for me.
Data;
English - United Kingdom, Czech, Spanish UK - United Kingdom Swedish English - US French US France German Portuguese +8000 more various values
I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".
I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.
I'm trying to code a macro to search through all the files in certain folders to find a value defined by the user. The rows containing that value will then be copied and pasted into a separate workbook. My boss currently planning on storing about 550 different files (90 days worth of data) between these folders, but there is the possibility of years worth of data collection, should he change his mind about the 90 days, so I'd like the code to be efficient if possible. What I've tried to do is search each file for the value, then set a range equal the first row containing that value. If the value is found in that workbook, the code loops through the worksheet, adding all the other rows with that value to the range. Then the file looping exits (the values I'm looking for are only contained in one of the workbooks) and the range is copied and pasted into the master workbook. Each workbook has only one sheet.
I'm currently getting a run-time error "13" Type Mismatch error when it gets to the line where the code is supposed to find the value and initialize the range.
Code: Private Sub SubmitButton_Click()
Dim mybook As Workbook Dim masterbook As Workbook Dim rownunm As Long Dim pathparts(1 To 5) As String
I am trying to search a cell for multiple different strings and if the string is found, multiply a nearby cell by a factor of 1.5. if the strings are not found i want the original cell value to be used. I have tried using find functions but they do not seem to work. I have also tried using nested if functions with no luck..
Our software extract for our data base sucks and values do not align properly if all entries do not have tge exact same number of fields with data in them.
I have always done the alignment in excel manually. I can find where the data starts to misalign and would like to do this automatically.
There is an id number for each entry (row) that is 6 digits long. How can I search a row for a 6 digit number and return the address of that cell? i can use this address as a starting point and use the offset function to do so alignment from there.
Am creating a large medical teaching database on Excel that lists JPEG images (a few hundred) and the file paths for each image into separate Excel columns using a VBA macro.
The JPEG image filenames are numbered in ascending numerical format (i.e 1.jpeg).
I'm trying to use a barcode scanner to enter in information in a given Excel spreadsheet and then search for that exact same information in a column in the same Excel spreadsheet.
The barcode will enter in the numeric information in A1.
I am searching for the exact information in column F.
If found, I would like for the cell the information is in to turn a color such as green.
In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.
In sheet1 in column "B" are names.
I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.
Is there an Excel formula or vb script that will do this?
I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.
Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.
OrderNumber TaskName SignOffDate
1 OED 01/05/2012
[Code] ........
What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:
OrderNumber OED CTN FAD6 RFS
1 01/05/2012 17/05/2012 22/05/2012 02/06/2012
2 03/05/2012 19/05/2012 27/05/2012 02/06/2012
There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.
An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.
how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder) North South East(sub folder) West(sub folder) Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
In Excel 2010 I am attempting to replace values in a cell, the right two values with "00", but am having difficulties with the correct '=replace' function.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.
If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.
I'm using Excel 2010. I have developed a calculator (attached) to make life easier for my colleagues. It works perfectly fine. But one thing keeps bugging me.
In Cell C3 - You have the option to choose "Daily" or "Weekly" (Drop Down). This selection effects your selection for Cell C6.
If you choose Daily in Cell C3 then your options for Cell C6 are restricted to select/enter an amount between £0.00 and £6.00.
If you choose Weekly in Cell C3 then your options for Cell C6 are restricted to select/enter either £0.00 or £12.00.
This works perfect.
However, if I select "Weekly" and then select " £12.00", all my results are perfect which is exactly what I want BUT if I then go back up to C3 and change "Weekly" to "Daily", the £12.00 in C6 remains (though the data validation for "Daily" is restricted to £0.00- £6.00) and all the results are consequently wrong until C6 is changed.
The obvious thing is to delete the £12.00 from Cell C6 or introduce a "clear" button BUT I don't want my colleagues to do this as it leaves the possibility of error and since we are dealing with money, I can't have it.
So what I would love and what would complete this calculator and deem it ready is if every time Cell C3 is selected (i.e. from the drop down list you choose Weekly or Daily) it as a result resets Cell C6 to £0.00.
I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:
[URL]
As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:
Code: 'Import from www.basketball-reference.com With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.basketball-reference.com/friv/dailyleaders.cgi?month=02&day=19&year=2014" _ , Destination:=Range("$A$1")) .Name = "dailyleaders.cgi?month=02&day=19&year=2014"
I'm saving information from excel 2010 in to a text file (txt), when doing this and opening up the txt file the below line is saving it with " at the start and at the end of the line.
:32A:110809GBP4,00
Is there any way when saving as a text file without the " pulling through at the start and at the end of the line?
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
Is there a way of reading .csv files and copying the data from say Sheet1 without actually opening the file itself? I have .csv's that will take some time to open due to size so dont really want to open them, but want to copy the data from all of them within a specific folder.
I am using xp pro, excel 2010 and the ftp server has linux installed. To open files from server I press open button in excel, then choose ftp location from drop down menu. FTP logon box asks password with the option of default user selected. I provide password and go on.
Some days ago something happened, I don't know what but as a result of it when I opened the FTP logon box, the anonymous option was selected by default. So I selected user option, gave id password and hit ok. It did not connect. Then I removed that ftp location from excel ftp locations. Whenever I tried to add an ftp location, excel restarted stating error message " *** encountered a problem *** ". So I reached a site after some googling and was directed to add the whole ftp file location path in the "File name" box of the open dialog box. It worked and the ftp location was automatically added in ftp locations. [ any ftp setting in excel is automatically transferred to ms word as well]
PROBLEM > Now I have a different problem. I can access ftp folders and subfolders but when I select a file and click open, downloading bar at the bottom of the excel does not start and after a long wait an error message pops up " the internet address ' ftp://192.168.****/folder/subfolder/filename.xls" is not valid. [I can open ftp files using filezilla etc]
I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.
As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.
But I can enter 1/9/2020 and the Data Validation happily accepts that date.
I have problems inserting new rows in selected files. The files with the problems have a macro. I don't know if the problem can lie within the macro. There is no error message, it just won't insert the new row(s).
I have a whole ton of *.dbf files (98 to be exact), that are from ArcGIS shapefiles. These date1_date2.dbf files have point names, and values, as shown below.
NAMERASTERVALU Point 1-9999.00000000 Point 2-9999.00000000 Point 3-9999.00000000 Point 4-4.93072701 Point 5-8.90071201
I'm trying to figure out how to transpose each of the dbf files and save them as a text file (tab deliminated). My original plan involved saving them all as text files, then using a convoluted Unix script (I'm what you call a "dirty programmer") to transpose them and then combine them all into a single file. Since there are so many files (and I plan to do this in the future as well), I thought if I could at least automate the text file creation, I'd be ahead of the game. Even better would be a macro that will transpose the data for EACH file, then create a 99 (98 files + header row) row, 5 column text file. Creating the text files (1 text file for each dbf file) automatically .
All the *.dbf files will have exactly the same # rows/columns (2 columns, 6 rows)
This is what I've tried so far. 1)open one of the dbf files in Excel 2)record my marco (using the Personal Macro Workbook option - so it will be available in any workbook) 2a) transpose data (and paste it right below the original data so that the new data is in rows 7 and 8 of the same file) 2b) save as text file (tab deliminated) 2c) stop recording, and end up with this:
NAMERASTERVALU Point 1-9999.00000000 Point 2-9999.00000000 Point 3-9999.00000000 Point 4-4.93072701 Point 5-8.90071201 NAMEPoint 1Point 2Point 3Point 4Point 5 RASTERVALU-9999.00000000-9999.00000000-9999.00000000-4.93072701-8.90071201
So now I want to apply the macro I just created to all open workbooks (after I bring in all the bdf files), but the macro isn't listed when I click the "view macros" button. All I want to do is apply the steps I recorded in the macro to all open workbooks.
I have small bit of import code (below), that I am looking to modify. The data that I am importing is for an inventory tool that determine optimal on hand quantities and alerts to any issues. In the past there had been one inventory source (one .csv data file). So I populate that to one sheet and modify the data on that sheet. I now have multiple data sources that I need to address.
There are two things that I would like to add/be able to do.
1) I would like to add a dropdown or set of multiple buttons to the popup window that says "Select the OnHand Report". Previously I only had one report source which I posted to sheet "orow0205". I have 2 other data files now that I have to import and they need to go to sheets "orow0206" and "orow2144". I am grabbing the data the same way on each sheet so that doesnt need to change I just need the option on the pop up window to determine the sheet name I want the import to go to.
2) The files that come from the sources above have a file name like "20121213_00000_groupstatistics.csv". Where the first eight digits represent the date the file was generated. If possible, after I select the sheet via the popup solve above, I would like to have the date populated in "MM/DD/YYYY" format in the last column of the imported data (the same date for each row of data imported). The file imported has no date data on it and can represent any day (which is why I am not using some form of TODAY formula).
I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.
For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.
how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.
