Excel 2010 :: Searching A Folder For A Keyword And Returning The Full Path?

Jun 19, 2013

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.

View 2 Replies


ADVERTISEMENT

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.

[Code] ....

[URL] ....

View 3 Replies View Related

Excel 2010 :: Using Current Month / Date And Time In A Folder Path In VBA Code

Feb 4, 2014

I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.

1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …

2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?

View 4 Replies View Related

Excel 2007 :: Full Path In Title Bar

Apr 1, 2014

I would like the full path in the excel 2007 title bar automatically every time I open a excel file.

The macros I try from other sources all end up in errors. I need step by step instructions.

View 6 Replies View Related

Macro For Returning URL Of First Search Result On Google From Keyword List In Excel

Apr 3, 2013

So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?

View 3 Replies View Related

Excel VBA Current Folder Without Complete Path

May 15, 2009

What's a proper way - in Excel VBA - to get the current folder *without* the preceding folders/path?

For example from folder "C:oracleora81sqlplusdemo" I'd like to retrieve "demo".

Currently I have:

Code:
DirNames = Split(ThisWorkbook.Path, "")
CurrentFolder = = DirNames(UBound(DirNames))

It works, but I suspect something exists specifically for this one.

View 7 Replies View Related

Excel 2010 :: Barcode Inventory Searching

Jul 18, 2013

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.

Is that doable with a formula in Excel 2010?

View 9 Replies View Related

Excel 2010 :: Making Worksheet Open In Full View Mode At User End

Sep 3, 2013

I'm using Excel 2010. I have a workbook with only 1 worksheet in it and it will be sent to several people. I want that excel file to open in Full View when the end user opens it. Is this possible without any VBA codes?

View 2 Replies View Related

Select A File And Get Full Path

Jan 10, 2014

I'm wondering what is the syntax to open a msoFileDialog box and select a file, resulting in setting a variable to the entire file path. If I use

msoFileDialogFilePicker

I'll only get the file name, without the path. I'd like to get the full path ending in the file name.

View 3 Replies View Related

Trim Full Path To Filename Only

Jan 31, 2008

I have path data (subfolder depth varies) that I need to trim to filename only.

Example:
/folder1/folder2/filename1.eps
/folder1/folder2/folder3/filename2.eps
/folder1/folder2/folder3/folder4/filename3.eps

Need to end up with:
filename1.eps
filename2.eps
filename3.eps

View 6 Replies View Related

Function To Get Full Path Of Workbook In Cell?

Oct 26, 2011

The folder which contains the Excel file I'm working on is

C:Usersar3DocumentsKey Performance Indicators

however if I place =INFO("directory") in a cell I get

C:Usersar3Documents

Is there a function where I can get the full path of the workbook in the cell?

View 2 Replies View Related

Hyperlink To Display Filename Not Full Path

Apr 5, 2012

I have the following code and I it works except it gives the full file path rather than just the name of the file.

I have been racking my brain for ages to try and figure how to just get the filename to show but I have been unsuccessful.

Code:
Private Sub CheckBox1_Click()
Range("K43").Select
' Set up list of file filters

[Code].....

View 9 Replies View Related

Including The FULL File Path On All Printouts

Aug 11, 2004

whats the simplest method of including the FULL file path on all printouts from excel.

View 9 Replies View Related

Trim Full File Name & Path To File Path Only

Sep 27, 2006

I have a variable ("DestFile") that defines a path to a file (used in saving the file)...

I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...

How would I go about trimming the "DestFile" address to get a "DestFldr" address?...

An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?

View 4 Replies View Related

Search A String And Return The Full File Name And Path

May 27, 2014

In the attached excel, I will be putting a string (Which will be a result of another formula) and there will be a table in column A & B(Length of the table will vary).

What I need is a formula to search entire column B and get the file names which is having that string and also the corresponding path

Table E6 to F9 contains the desired result (The result wont be more than 3 at any point of time

Search.xlsx

View 3 Replies View Related

List Files/Workbooks With Full Path & File Name

Sep 6, 2006

I do a search on my directory for all *.xls files each month which produces a list some 11,000+ lines long. Attached is a very small sample.

I then import this into Excel see tab Raw Data

What I want to produce is a list shown on tab Finished Data, so that I can then sort etc.

Has someone already done this before and got the code, or can someone point me in the right direction on how to arrive at the finished data?

View 9 Replies View Related

Show Full File Path Ignoring Drive Letter

May 2, 2014

I am trying to allow users to select a file, and then the file name/ the file path / and date are added.

Right now, all those functions work in the following code, but the file path shows up as a letter drive. As there could be upwards of 50 people using this file and the mapped drive could be different, i wanted the full path to be displayed.

Code:
Private Sub File_1_overwrite_Click()

Dim filename As Variant
Dim filename1 As Variant

filename = Application.GetOpenFilename(, , "Select Programme")
filename1 = filename
filename1 = Mid(filename, InStrRev(filename, "") + 1)
File_1_link = filename
file_1_name = filename1
File_1_date = Format(Date, "MM/DD/YY")

View 8 Replies View Related

Excel 2010 :: Using VBA For Sub-Totaling Returning Unexpected Values

May 8, 2014

This relates to this thread - [URL] .....

This is the only macro in this file

[Code] .....

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.

Attached File : Example-1p.xlsm

View 6 Replies View Related

Excel 2010 :: Returning Value From Third Column Based On Two Other Columns

Jun 24, 2012

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.

I'm using Excel 2010.

View 5 Replies View Related

Excel 2010 :: VBA Code To Open Save As Box In Specified Folder

Apr 24, 2014

I have windows 7 and excel 2010 and am using a macro that opens up a csv file(I think) of daily reports into Excel and then automatically delineates it and formats it how I want it. I will be using this to save a new file every day for the reports from the previous day and want to include at the end of that macro a way to prompt the user to "save as" so that each day they can run the macro and enter in the date and save that report for further use. I am wondering what VBA code I could use at the end of the macro code to prompt the Save As box and if I could already have the save us set up in the following folder... "W:Daily to Fortis Excel2014(the user will put in the date here)".

I've been looking around sites and trying to figure it out. I need the file format to be the same as when you save as "Excel Workbook". I was trying to use the Saveas (filename) function and could get it to save every time as a specific file name in that location but when I run it the next day it has the same name and saves over itself. So I need the user to be able to put in todays date as the filename to create a new one every day.

View 7 Replies View Related

Excel 2010 :: Placing Pictures From Folder In Spreadsheet

Jan 17, 2013

We updated excel from 2003 -> 2010 and this stopped working.

We have a macro which takes pictures from a folder and pastes those pictures in the excel -sheet.

At 2003 the pictures were correctly pasted to their positions, but at 2010 the pictures appear in somewhere at the same sheet. (not at the right places, which should be at column "o" at the same row.)

Macro (working correctly at 2003) :

Sub Kuvan_piirto(ic As Integer)
On Error GoTo loppu

SourceFile = Enari
DestinationFile = "Live1.JPG" ' Define target file name.
FileCopy SourceFile, DestinationFile
ActiveSheet.Pictures.Insert("Live1.JPG").Select

[Code] ..........

What should I change for 2010?

View 1 Replies View Related

Excel 2010 :: Loop Through To Check For All Filenames In Folder And If Then?

Dec 22, 2013

getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.

View 1 Replies View Related

Excel 2010 :: Copy File / Rename And Move To New Folder

Jan 15, 2013

I am trying to copy a file, rename it, and save it to a new folder. I keep getting a "Compile Error ; Syntax Error" at line FileCopy (ImagePath & oldName, NewPath & newName). I am using Excel 2010.

FileCopy (ImagePath & oldName, NewPath & newName)Sub RenameFiles()
'Renames file based on "sheet 1" - Column 1 Old file name - Column 2 New file name
Dim oldName As String
Dim myfile As String
Dim newName As String
Dim ImagePath As String
Dim NewPath As String

[code]....

View 5 Replies View Related

Excel 2010 :: Search Variable Folder And Insert First JPEG

Jul 17, 2013

I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.

View 1 Replies View Related

Excel 2010 :: Retrieve Photo From A Folder And Paste It Into Cell

Apr 20, 2014

Excel 2010, Windows 8.1. I want to retrieve a photo from a folder and paste it into a cell. I have the paste and format process figured out but I'm having trouble putting the file path together to retrieve the photo. If I use the full path, the statement below retrieves "IMAG1234.jpg" and puts it where I want it.

ActSheet.Pictures.Insert("C:UsersEljafeDocuments1-QCReport_PhotosIMAG1234.jpg").Select

But if I have a different photo file name as a string in a cell (e.g. "IMAG5678.jpg") what is the syntax to attach it to the file path? i.e

ActSheet.Pictures.Insert("C:UsersEljafeDocuments1-QCReport_Photos") & "IMAG5678.jpg".Select '(which of course doesn't work)

So, I have a path but I want to attach different photo number to the path from a sting in a cell.

View 2 Replies View Related

Excel 2010 :: Macro On Multiple Files In A Folder Run Time Error 424

Jun 23, 2013

I have obtained a piece of code from online that runs a macro on multiple files in a single given a single folder. when i implement it though it throws a run time error 424 "object required' and point to 'Documents.Open FileName:=path & file' line of code. Is this because Dim file type is not declared?how do i fix this problem. also, im using excel 2010.A

VB:
Sub Mac()
Dim file
Dim path As String

[Code]....

View 4 Replies View Related

Excel 2010 :: Multiple CSV Files - Macro To Point Folder Where These Saved

May 11, 2012

I'm working on a project where I need to import 150 csv files into an Excel2010 workbook, with each csv file being on a seperate worksheet.

I would like to set up a macro to point to the folder I will have these saved in this up so that each month when I get updated csv files I can repeat the process and bring in the new data. how to create this, I'm very new to VBA?

View 1 Replies View Related

Excel 2010 :: How To Activate File And Create A Folder In Named CustomUI

Jul 28, 2013

I have a Excel 2010 file that I want to activate the file and create a folder in it named customUI.

the steps to achieve this?

View 7 Replies View Related

Excel 2010 :: Rename Files In Multiple Folders And Copy To New Folder

Sep 22, 2013

For the last year I have been downloading cash register X1 and Z1 files onto an SD card. Each night's files (X1 & Z1) are stored in a new folder with the naming convention "RP + [DATE]", (EX: RP120910, [YYMMDD]). I have 265 folders! I need to go through each folder, select the Z1 file (I don't care about the X1, X2 or Z2 files) and rename each with the following naming convention: "Z1 + folder_name + .xls". For Example: Folder RP120910 contains the Z1 file named "Z1_T1729.ECR". After renaming, the file should be "Z1RP120910.xls". As the VBA code loops through each subfolder, selecting and renaming the Z1 files, I would like the new files to be placed into a new folder named "Z1Files". Note: All subfolders are currently in a directory named "RegisterFiles". I am using Excel 2010.

View 3 Replies View Related

Searching Returning Multiple Matches

Mar 6, 2007

I am looking to perform a search that will return a match and place it somewhere else, and then search again until its finished the list?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved