Checking Multiple Files For Criteria, Via VBA
Mar 26, 2008
I have 52 weekly folders which are contained in the following directory
G:RawProduct ManagementJen DelaneyVendor ReportsLexmarkWeekly Stock And Sales2007
Wk4
Wk5
Wk6
Etc. etc.
Each (EG) Wk4 folder contains 6 files (all .xls format)
What I need to do (quickly) is create a macro which will cycle through each file & tell me which file (filename) contains the following part numbers (if at all)
0012A7610
0064080HW
0012A0150
0012A5140
0012A7610
0064080HW
So basically its checking to see if any of the above part numbers are contained in the file?
View 9 Replies
ADVERTISEMENT
Jun 29, 2014
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
View 2 Replies
View Related
Jul 23, 2007
Is there a way to check to see if there are any .xls files, open or closed, in a given directory?
View 9 Replies
View Related
Mar 10, 2013
This is what I am trying to achieve:
1) Prompt user to select a file (or multiple files)
2) Copy the files that meet certain criteria to a folder
My attempt (fail):
VB:
Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code]....
I get error 424 object not found in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
View 3 Replies
View Related
Jan 3, 2013
using a command button to input data back into the spreadsheet at a specific location.
Background: I am building a time clock spreadsheet, of sorts. I have a user form that provides a list of volunteer names in a combobox and then a list of activities they can perform in another combobox. Some activities have Details. (So, VolunteerName Todd can choose Activity Maintenance, which has no details; Volunteer Joe can choose Activity Teaching and then can select Details Intermediate 2.) The volunteer then clicks a SignInCommandButton.
The SignInCommendButton populates a worksheet (VolunteerLog) with the following information:
Column A: Volunteer Name
Column B: Today's Date
Column H: Exact Time In
Column E: Activity
Column F: Details, if populated
The SignInCommandButton also copies a formula from cell L2 into the appropriate row of column C (Time In) that rounds the Exact Time In to the nearest 15 minutes. The SignInCommandButton then does a Copy/Paste Special Values to remove the formula from column C and then resets the Userform.
What I need to do:
1) When the person first selects their name from VolunteerNameComboBox, I want to check 3 things:
a) whether the person's name exists in Column A of the VolunteerLog worksheet;
b) if it does, if the Date associated with that entry = Today; and
c) if it does, if the Time Out column is Blank.
If all three conditions are met, I want the ActivityComboBox to populate with the values in Column E and F, if necessary. The user will then click the SignOutCommandButton. (details in a moment)
I already have the code for if the conditions aren't met (Activity box populates and, depending on the selection in the Activity box, the Details box may appear for a selection to be made, or may remain hidden.)
2) The SignOut Button needs to enter the Time into Column I of the appropriate row found in (1) above. It will also need to copy the rounding formula I mentioned earlier, and do the Paste Special, but I have that already.
For (1), I think I have a start. My thinking is to first check if the Name selected in VolunteerNameComboBox is in the VolunteerLog. Each time it shows up, I'd like to add it to a list (range?) called rngSignedInDate. Then I'd loop through all the entries in that range checking if the Date = Today. If it does, then I'd add it to a list called rngSignedOut and loop through those results to see if Column C (Time Out) is blank.
I think I have the first loop, but am stuck on how to populate the results into rngSignedInDate. Here is the code:
Code:
Private Sub VolunteerNameComboBox_Change()
'Check if the Volunteer has signed in already
Dim strVol As String
Dim rngSignedInVol As Range
Dim rngSignedInDate As Range
Dim rngSignedOut As Range
Dim rngSignedInActivity As Range
Dim rngSignedInMatch As Range
[code]....
(2) I haven't started working on the SignOutCommandButton. The challenge for me with that is directly tied to the challenge in (1). If someone does pass all 3 tests, I need to have that specific instance defined in a way that I can have the SignOutCommandButton put the time in the appropriate row.
View 4 Replies
View Related
Feb 9, 2007
Is there a way to check a value, that is a multiple of another value.
E.G.
say the user specifies a height of 225 and then specify laminations of 45
is there a way to check the value of 225 to ensure 45 divids into it perfectly??
and if it doesnt then gives an error message of some kind.
So if someone specifies height of 200 and lamination of 45 an error would show because 45 cant be divided into 200 perfectly.
View 6 Replies
View Related
Jan 27, 2010
I am getting a NEXT without FOR error message when i try to run this code. This is the portion of sub im having trouble with.. been trying to work it out but to no avail. I thought that i might have to do Next Ccell, though I got the same error.
View 2 Replies
View Related
Mar 17, 2014
The project I'm working on is for checking the measured Valve clearances on 4 cylinder motorbikes
They have 16 valves in all and I had made a spread sheet which has cells to enter the Min and Max Valve clearances for 8 exhaust and 8 Inlet valves they have different tolerances.
I have been able to do this part so I am at the point where once the valve gap min and max are entered for the exhaust and inlet valves on Cylinder 1 this changes all the min max gaps for all the other inlet and exhaust valves.
And once I have measured the gaps and entered the actual valve gap the sheet then tells me if the measured value is in or outside the valve clearance tolerance. And this is all working very well!
To save money on buying new shims which is what is used to adjust the valve clearance I swap as many shims that are out of spec on the inlet side to the Exhaust side and vice verse that will bring the valve back into spec.
Once a shim is found out of spec it must be removed, it's measured and that measurement is entered into the sheet and the sheet then tells me what size shim to replace it with to bring the valve gap back to as close to the centre of the tolerance as possible.
What I won't the sheet to do that I can't work out how is once all the out of spec shims have been entered I want it to check and tell me which shims can be moved to which valves to bring them back in spec! All valves are numbered from 1 to 16 and cylinder number exhaust and inlet valves.
View 14 Replies
View Related
Nov 5, 2008
This is a good deal harder than my last issue. I have a few pieces of data:
A62:A80 - Array of Integers named X (2 through 20, to be exact)
D62:D80 - Array of Strings named Y (on a Drop Down)
AD45, AG45, AJ45 - Formula Holders
Now the tough part of explaining what I want... assume the string in question is "Pie":
If Y contains "Pie" once, I want AD45 to take the value of the corresponding X.
If Y contains "Pie" twice, I want AD45 to take the value of the first X, and AG45 to take the value of the second.
If Y contains "Pie" three times, I want AD45 to take the value of the first X, and AG45 to take the value of the second, and AJ45 to take the value of the third.
So, in the end, the formula holders will either have a number between 2 and 20, or be blank. Note that it is possible for users to put more than 3 occurrences in Y even if it's user error.
I'm not really asking for the full formulas, but I simply don't know where to start on this, on what functions would be useful in pulling out the values I want.
One idea I had is that:
AD45 searches the array for "Pie" and takes the first value of "X" that matches the find. Blank if it doesn't.
AG45 checks if AD45 is not blank, and searches the array for "Pie" starting at the next index.
AJ45 checks if AG45 is not blank, and searches the array for "Pie" starting at the next index.
View 6 Replies
View Related
Feb 17, 2010
i need to check a date within a multiple range of dates using certain key.
i've tried vlookup, but failed to get correct answer.
here is Table1
product (cell=A1)
date (cell=B1)
plan(cell=C1)
A
20080801
A
20090801
A
20090901
A
20090831
A
20090101
A
20091101.....................
View 9 Replies
View Related
Jan 4, 2014
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.)
View 1 Replies
View Related
Nov 12, 2013
I'm trying to validate the data entered into a series of cells each cell can contain a different set of data but the value N/A is also permitted. For example:
Cell A1 could contain a date from 2013-01-01 thru 2013-12-01 but the value N/A is also valid
Cell A2 could contain a decimal from 0.01 thru 302502.23 but the value N/A is also valid
Cell A3 could contain an integer from 3 thru to 9000 but the value N/A is also valid
When the acceptable values are entered then I want to be able to carry on otherwise I want to pop up with an error.
View 10 Replies
View Related
Jun 5, 2014
Create array set for checking multiple workbook?
[Code] .....
View 11 Replies
View Related
Dec 21, 2007
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
View 9 Replies
View Related
Jan 22, 2010
- I have some buttons set-up to insert pre-set diagrams from sheet “FISH PARTS” onto sheet “THE JUMPER FISHBONE”
- However, before inserting a new diagram onto sheet “THE JUMPER FISHBONE” I need to make sure the default diagram components are all gone.
- There are seven components all with structure Like “BONE_*”
- If one or more of these components are present I want to display a message to confirm the user wishes to delete the default diagram and insert new chart (or abort is they do not wish to delete the default)
- some explaination of structure below
- spreadsheet also attached
View 2 Replies
View Related
Dec 2, 2008
what i want to do is 1 have a workbook with 3 worksheets. Sheet1 contains the columns:
Reference No. Line Item No. Amount Date
1 3 500 12/01
2 4 900 01/01
Sheet2:
Reference No. Line Item No. Quantity PersonInCharge
1 3 10 Kim
5 6 60 Noel
For sheet 3, what i want to happen is that when i run the VBA macro, it searches through both worksheets (Sheet1 and Sheet2), finding rows that have matching Reference No. AND Line Item No. and combine all of the columns (and values) for that particular row into another row in Sheet 3. So for the above data set, the resulting data in Sheet 3 would be:
Reference No. Line Item No. Amount Date Quanity PersonInCharge
1 3 500 12/01 10 Kim
As this is the only entry in both sheet1 and sheet2 where there is a matching reference and line item no.
View 9 Replies
View Related
Sep 26, 2007
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
View 4 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Mar 26, 2014
How do I get my spreadsheet to play wav files I have saved according to certain criteria?
i.e. if certain value is entered into the cell, or on opening of the spreadsheet
I have message boxes appearing for the values in certain cells, however, don't know how I would add in the playing of a wav file to enhance this.
View 3 Replies
View Related
Nov 7, 2009
I have 8 different files all have a set of data in them
each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank.
I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
View 9 Replies
View Related
May 9, 2008
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
View 2 Replies
View Related
Feb 6, 2014
I'm looking to create a macro that does the following:
- Loops through a folder structure
- Opens a specific subfolder within each folder based on name (i.e. "*Financials*")
- Opens a specific file within that subfolder "*Financials*" based on both most recently modified a naming criteria (i.e. "*Model*")
- Performs an action on that file (a macro created elsewhere that I assume I can call here)
- Closes that file, and moves on to the next file matching the criteria above
As a self-diagnosed VBA novice, I'm having trouble adapting code found in various forums for my specific project.
I've found examples that address aspects of my desired macro or do similar things, but I can't figure out how to put them together.
Open most recent file:
[Code] .....
Loop through folders and subfolders and print all files:
[Code] ....
View 1 Replies
View Related
Jan 13, 2014
So, today my manager came to me and informed me that he wanted me to do a little data entry for him... WOW... So I have about 2000 invoices to go through(all excel files, in their dated folders... 12-2-2013(folder) and the filenames are 12-2-2013(1), 12-2-2013(2) etc.)
I need to take these individual files and have certain cells from each in certain cells of a new workbook for example...
file 12-2-2013(1)... I need cell C3 from that file to go to cell A2(and this will be the same for all the other files... 12-2-2013(2) cell C3 needs to be in A3 etc etc etc.. this is the sale date)
file 12-2-2013(1)... I need cell a15 and b15(they are both "stretched out" cells) to be put in C3 of the new workbook and so on.
is there an easy way/formula to do this all? I have to have this done by friday at the latest(tomorrow preferred @@) I have to input data for 7 cells...
View 1 Replies
View Related
Apr 13, 2014
I need to create a dynamic template for importing data from multiple .txt files to my workbook on a daily basis.
I recorded the macro for importing ONE txt files and starting to deveop it into looping through multiple txt files and sheets but to no avail.
1) Currently I have 4 named ranges
FileLocation = Location of my source file
FileName = All the file names of my txt files
SheetDestination = The sheet to be paste for each txt file (further explaination below)
FileFormat = .txt (for VBA purpose)
2) The macro recorded was in "ActiveSheet", thus I thought of looping through each sheet (put the activate code at the first line) for the codes to run.
3) ColA ColB
1SP.txt
2IND.txt
3US.txt
4
5
6
Column A is my SheetDestination, while Column B is the respective filename for source txt file. My final intended result would be simply macthing sheet 1 against SP, sheet 2 against IND, sheet 3 against US... so on and so forth....
View 4 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Jul 30, 2008
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View 5 Replies
View Related
Mar 6, 2013
I have written a macro that prompts the user to chose some files and if they meet certain criteria, it copies them to a specified folder.However, I have run into this 424 error "Object required".
Code:
Sub FILES2SFTP()
Dim FileNames As Variant
Dim I As Integer
Dim fso As Variant
Dim Data As String
ChDrive "G:"
ChDir "G:TEST"
[code]....
The error is in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
View 2 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
