Run The 'loop Through A Folder' Code On Multiple Workbooks I Receive
Jun 5, 2008
I am trying to run the 'loop through a folder' code on multiple workbooks I receive.
The workbooks I receive are full of drop downs that have associated values of 1-3 on the first sheet. (About 100 in total) This particular workbook has the drop downs on one worksheet and the numeric results on another worksheet 'Results'
The second workbook 'Totals' (very basic) , just referenced each 'Results' worksheet and had equations that averaged all the drop downs cell by cell.
I would love to be able to use the 'loop through a folder' code to open them and then average them on the 'Totals' sheet. The main reason is that I am delegating this to another person and would like to eliminate the risk or human error. ( unless it is my own)
I am a total VBA n00b. Any assistance would be appreciated.
If needed I can upload the code or sheet as an example.
The base folder would always be the same. ie c: estresults*.xls
The naming would be very similar.
This loop code seemed relevant as it did not seem to require any file naming and would run through a folder and process all XLS files.
[url]
View 14 Replies
ADVERTISEMENT
Oct 6, 2008
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).
View 6 Replies
View Related
Aug 16, 2014
I am looking for an VBA solution to copy value from multiple workbooks in the same folder.
The master workbook will contain two columns: a list of date (col A) and value (col B) copy from other workbook.
Those multiple workbooks have the same structure which contain a date (A1 cell) and number value (B1 cell)
Basically I want to perform
i) Date matching A1 value in individual work with master workbook
ii) If entry date is matched, then copy B1 value to column B in master book
View 1 Replies
View Related
May 20, 2006
My question is:
I do multiple audits using excel, and would like to start a database as I put more audits into one folder. So it updates the values as I insert more audits into the folder.
My audit is a workbook which has multiple worksheets. But for each audit, it is the same worksheet with the same cell. The audit consists of yes and no questions, where you put in a x for either one. I would like to start a database, so for each question on my audit, I would have a percentage of yes or no for all my audits. For instance, question 1 , 7 out of my 10 audits, I had yes for that question.
How do I write a macro, so it counts the x's for multiple workbooks, and updates automatically for each question as I add more audits into that folder.
View 4 Replies
View Related
Feb 7, 2014
I have two pieces of code that each work, but I am struggling to combine the two.
I started with Ron de Bruin's code to attach multiple files to an email and then found more code that will loop through a folder to attach multiple files to an email.
Essentially, I would like the structure of Ron de Bruin's code, with the ability to have folder paths in the cell range, rather than file names.
Here is the code that works to loop through a folder, but it only works when I have the paths listed in one column, not in a range (e.g. columns C-Z)
Code:
Sub Send_Indv_Files()
Dim OutApp As Object
Dim OutMail As Object
[Code].....
View 4 Replies
View Related
May 23, 2006
I want to be able to run this many times looping through different file names. The only thing that changes between file names is the number.
Application.Run "'C:Documents and Settingspadi4545DesktopProjectRuns10.xls'!main"
ActiveWorkbook.Save
ActiveWindow.Close
View 3 Replies
View Related
Nov 5, 2008
I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.
Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.
From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:
Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.
View 14 Replies
View Related
Apr 21, 2014
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies
View Related
Apr 24, 2007
I support an excel file that has a bunch of vba code behind including calls to msquery. The customer used this excel file by opening the original pulling in data for a specific client and than saving the file to a different name. The result is she has multiple copies of the file.
It currently has two problems:I need to change the code in one of the subroutines to fix a known problem. This of course involves opening up the Visual Basic editor (from the excel file), finding the code to change and making the code change.We have just upgraded to Office 2003 and MSquery is looking for xlquery.xla which is no longer needed in 2003. The problem this causes and its solutions are described at Microsoft Support here. I have done the quick fix which is putting the xlquery.xla where excel is looking for it, but I want to do the correct fix which involves running the following code in every workbook that accesses xlquery.xla
Sub DeleteQueryDefinedNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.Visible = False And InStr(1, n.Name, "QUERY", _
vbTextCompare) > 0 And InStr(1, n.Name, _
"Query_from", vbTextCompare) = 0 Then
n.Delete
End If
Next
End Sub
and than making sure you save the file as office 2003.Both of these things are much to complicated and time consuming for my customer to do for each individual excel workbook.
Which leads me to my question: Is there a way to automate this?
View 8 Replies
View Related
Apr 26, 2014
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
View 5 Replies
View Related
Sep 15, 2008
I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.
the folders are set up as follows,
there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.
for example a1 - k1
the code i am using transfers the file names but comes up with #REF! instead of transfering the data
Here is the
View 9 Replies
View Related
Jul 23, 2014
I have an Excel VBA Macro for creating/logging drawing numbers. Each drawing belongs to a job number. Each job number has a folder name containing the job number followed by a description (ie 999999 - bracket assembly) for storing drawing pdf's. The job number is only known as a 6 figure number in the drawing creation process BUT for the PDF saving process it is a string value...as my example above
I have to change my process by pre-creating the Job Number folder, then have the macro look for the appropriate folder by searching the parent folder for a sub folder containing the job number (always the first 6 figures).
I want to insert in my macro some code that searches...
The parent folder for a folder containing the job number. The macro value for the parent folder is P:engineeringdrawings (this never changes). the macro value for the job number is iOpenair (it's an entry that is entered as start the macro). Imagine the value for the job number is "999999"...so the search would be for "999999*.*" The code needs to search the parent folder, find the folder name that begins with a six digit number. Capture the complete name of the folder and store that name as a Dim value that I can call up elsewhere in my macro.
View 9 Replies
View Related
Jul 6, 2008
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
View 4 Replies
View Related
Mar 28, 2014
I have a folder with multiples excel sheets
Destination : C: Project CustomerExcel
I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.
New sheet would contain the information of each sheet eg. Column A2= file name
And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.
VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
[Code] .....
View 5 Replies
View Related
Nov 24, 2008
code to create hyperlinks to all the workbooks in a folder? I Have about 52 workbooks in a folder and I like to place the links into another workbook so a user can open anyone they wish.
View 2 Replies
View Related
Dec 1, 2008
Is there a way to use a for next loop to open a workbook that is in a folder, then save and close the workbook then open the next workbook in the folder and do the same routine till all the workbooks have been updated?
View 12 Replies
View Related
Dec 3, 2008
A while back someone helped me out with a code to pull data out of all workbooks in a folder.
View 10 Replies
View Related
Dec 22, 2009
I have several workbooks in a make your day folder categorized under several sub folders. Each workbook has a macro that calls the following macro:
View 2 Replies
View Related
Oct 17, 2013
I have a macro that opens all workbooks in a folder and searches for a few terms returning the appropriate rows, my problem is that someone has decided to protect some of the sheets so when the files are opened you get the password box popup. I have added the (filename, readonly) segment however this has not solved the issue, the macro displays an error stating a workbook is open.
Is there any way to resolve this without unprotecting the sheets?
View 2 Replies
View Related
Oct 22, 2013
I am using this code to save all files in folder as CSV . I would like to add a letter to the beginning of file name for each file starting with a for file 1, b for file 2 etc.
Code:
strFile = Dir(mFolder & "*.xls*")
Do While strFile ""
Workbooks.Open mFolder & strFile
Range("D1").EntireColumn.Insert
[Code] .........
View 2 Replies
View Related
Mar 2, 2007
I have a series of workbooks created by my salesforce (from a master template that I created for them) which they then place into a folder for me to extract data and upload that data to SAP. I am writing a procedure, therefore, to do just that.
Essentially, I would like to open the file containing the data, extract what I need and then close it. I can do that. THEN, I want to move each data file to a folder (which would depend upon the data within the file) as part of the whole procedure. I can define the new path, but I don't know how to move the book.
In creating the master template of the data files, I built into it an auto-name-creation procedure which defines the name of the file that they create. This enables me to write a procedure to work out the file to open. In order to do this, I disabled the save function (but password protected it so that I could make changes to the master template). As such, I am unable to use Save/Save As to move the data files.
View 4 Replies
View Related
Jun 10, 2008
I have a large number of files (100+) contained in a directory with multiple subfolders that need to be made read-only at the end of an excel macro. Because of said length, I believe (open to correction) that the SetAttr method would not work (as I do not know the individual file names). It is also necessary to preserve all of the original files' other attributes.
View 7 Replies
View Related
Nov 9, 2008
I have a whole group of workbooks. Is there a way to make a new workbook that will look in the folder containing all of the other customer's books and pull information from them? In other words, I want a workbook where in Column A would contain all of the Customer's Names (Cell Info!G6), Column B would contain the Insurance Company (Cell Info!M14), etc. Is there a way to to that?
View 14 Replies
View Related
May 11, 2007
I'd like to create a macro which will
-go to a given folder
-open each of the workbooks in the folder, one at a time
-after opening the first wkbk, go to a specific cell address on the first tab of the wkbk
-paste in some values and formulas
-save the workbook
-open the next workbook, and repeat as above, for all workbooks in the folder
View 9 Replies
View Related
Feb 13, 2009
I have several workbooks in a folder with sub folders. How can I get the values located in C6,
E6 and E9 and put in a new workbook the "file name" in Ai and those 3 values in Bi, Ci and Di
respectively? Itīd be desirable get the values without open the files.
something like:
------A-----------B---------C----------D
Filename1.xls---13:11------13:57-----00:46
Filename2.xls---17:42------18:03-----00:21
.
.
View 9 Replies
View Related
Aug 19, 2009
Is there any code that can look at all the workbooks in a folder and add up the certain cell values? All the workbook's titles will change through time but all the workbooks only use sheet1.
Example: I want to add up cell Q45 from all the workbooks that exist in the windows folder. Is there vba code for this?
View 9 Replies
View Related
Nov 14, 2009
If i have 25 different workbooks in one folder is it possible to open/merge all 25 workbooks into one workbook without having to copy and paste each individual workbook.
Each workbook only has a sheet1. I hope there is it would save a lot of time
View 9 Replies
View Related
Feb 27, 2007
1. I have a large number of Test Suites that are large documents with 1 (visible) sheet w/ test cases, and 1 (hidden) sheet w/ validations. Test Suite is saved with a unique name (that follows a naming convention we've set up on this project: <Project>_<Test Suite Category>_<Location in Software>_< date>.xls
2. I need to create a master document in which i can run a macro that will copy each "test suite" sheet from each of these documents, insert it into the master doc, and sort the sheets by name. then it needs to present the data from all of the sheets on the main page of the doc, and break down the results by Test Suite Category, and Location.
Is this possible?
Can I create a macro that will import sheets from (potentially) hundreds of different workbooks, and then sort them appropriately?
I've been looking all over the place, and while i've seen macros that can import specific data, rows, or columns, i havn't quite seen what i'm looking for.
View 9 Replies
View Related
Apr 27, 2007
I have 51 workbooks that I would like to combine into one workbook. Each workbook varies in number of rows, but have the same number of columns. There are 31 Columns (AE).
Is there a fast way of doing this or will I have to cut and Paste?
View 6 Replies
View Related
Aug 1, 2007
I'm using this simple code found on ozgrid to open all excel files in a folder...
Sub OpenAllWorkbooksInFolder()
'''''''''''''''''''''''''''''''
'Written by www.Ozgrid.com
'Open all found Workbooks in specified folder
''''''''''''''''''''''''''''''''
Dim i As Integer
With Application.FileSearch
.LookIn = "C:Data"
'* represents wildcard characters
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then 'Workbook exists
For i = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(i))
Next i........
View 9 Replies
View Related
