I have a VB codes to process the data from a file that located in the folder from directory. This VB will copy the data from the file and paste it into new workbook by clicking the command button. I want to modify this code in order to make VB can process the data from multiple file from multiple folders. Previously, I just write the code for one folder only. So now, I want to modifiy the codes in order to expand more folders. All the results will be pasted into the same workbook.
Private Sub cmdStart_Click()
Application. ScreenUpdating = False
Set outbk = Workbooks.Add
Workbooks.Open "C:" & TextBox1.Value & "" & TextBox2.Value
Sheets("bin_trip").Select
With ActiveSheet
I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.
The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.
I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.
Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).
Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?
Sub Click2()
Application. ScreenUpdating = True Dim MCDrow As Long 'Dim SubFolders As String MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row
Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory 'SubFolders = True Fname = Dir(Fpath & "*.xls") Do While Fname <> ""
I need to back up files, which may be excel or MSword, by copying them from different directories, say C: to the backup directory, say X:. any vba to backup the files? I expect the vba can copy all file listed in column A.
E.g. C: est1File1.xls or C: est2File2.doc, and then pasted to the directory in column B. e.g. X:BackupFolder1 or X:BackupFolder2.
I was searching through the internet to look for some help with regards to consolidating data from multiple workbooks from different directories into a single worksheet called Summary.
I would like to copy the entire worksheet from different workbooks in different directories into this workbook I like to call Summary. To manually do it I have to open the workbook I want to copy the worksheet from. Right click on the tab and select Move or Copy, select the sheet I want to copy and select the workbook I want to copy this sheet to. Repeat this process for all the workbooks.
The next step is to copy and paste from these sheets into a main summary sheet. The data range from these sheets are starting from row 13 onwards and they may be of different size between Column A to Column W.
For example the workbooks are coming from :
S:Area 1West.xls from worksheet Andy S:Area 1East.xls from worksheet Bill S:Area 1North.xls from worksheet John S:Area 1North.xls from worksheet Albert etc...
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
I have to apply formulas to many different excel files. They are all seperate, and there are litterally thousands of them. They are all seperated, cause each of them represent and individual user.
I am unsure of how to work on so many files. I cannot select all of them to open at once either, cause they are in different folders.
I need some sort of batch running program, that could go thru each and every one of them, and apply some formulas to them (probably using VBA scripts)
I often use multiple workbooks containing large data, (more than 10MB) with formulae built in. When I update one workbook, i cant use the other because Excel is still processing the first. Is there a work around or a MS patch to rectify this, or this is just a limitation of Excel program?
I have a cell in my spreadsheet that is used to designate if the row needs to be hidden or not. If the user enter's "Y", the row is hidden. I need to check some of the previous cells for entered data before the row is hidden. If data is not entered in these cells I want the empty cell(s) to be filled with a color. The user will then have to enter a value into all the correct cells before the function to hide the row will work. What is the best way to do this? I am using the following function to check for the "Y" and hide the row.
I have a large number of sheets with data in the following general format:
x ..... y ............ z etc
A Unique value Unique value A Unique value Unique value B Unique value Unique value B Unique value Unique value C Unique value Unique value C Unique value Unique value D Unique value Unique value D Unique value Unique value E Unique value Unique value E Unique value Unique value
In seperate sheets, I want the following:
x y1 y2 A B C D E
x z1 z2 A B C D E
And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.
The way I have this set up right now is that data is being submitted by an Infopath Web Form and emailed to me, it produced an XML file with values for all the possible entries for each person but only certain departments are filling in certain blocks.
That XML file is uploaded into a worksheet and then I'm trying to process data off that table to get the information I'm needing. I'm trying to divide data if a certain piece of the formula matches (IF CCU & August then Divide F column on same line by G column on same line and display the result)
I'm also trying to ignore empty blocks which is where my problem is at. So far I've come up with
[Code].....
but that is only working if the data happens to match on the same line in the data table.
Added the excel workbook: patientdashboardexperience.xlsx
macro and I think this has me on my way; however, I do not understand the code and I am having difficulty modifying it. It returns the path in cell $A1 of my workbook, but I get reference errors in $B1:$L1.
I would like for the code to start entering the values in row 2 of my current worksheet. Row 1 I want to make a header row. The first column I like having the file path. Can the path be changed to a hyperlink? I would then like for it to return the values in $AP1:$BC1 to $B1:$O1 of my current workbook.
I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.
As an brief example.
Cell "B3" on the master(worksheet) contains a job name I-40 bridge So initially I would like the Folder name and file name to be I-40 Bridge
However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.
I am currently working with Excel 2007 Pro edition on windows 8. I
I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.
Public Sub SaveAsA1() ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub
I have the following code that transfers all the Excel files in Folder 1 to Folder 2:
Code:
Sub Move_NRAuto() Dim fso As Object Dim FromPath As String Dim ToPath As String Dim FileExt As String
[Code]...
I'm trying to amend this code so that instead of hard coding "Folder1" into the code, I can loop this process through a number of folders (Folder 1, Folder 3, Folder 4, etc) and move all of the files in each of those folders into "Folder2".
and for each Project in Projects, create a new folder with the actual month (e.g. "03" or "12")
Im running the Makro from a file where I'd like to put the month in a cell -> so the code refers to cell("A1") wich has value 01 and creates a folder "01" in every projectfolder in C:Projects
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 have a series of files in *.ew7 format (i know its a random program)
I have some code which i can run which will simply open the relevant file and save it as xls.
The files are saved in multiple folders in format "yymm" eg: 0910 - oct 09.
inside each folder there is a ew7 file for each day in rthe format: "01ddmmyy" eg 01271009 = 27th oct 2009.
I have folders going back as far as may 2002 (in folder 0205) and i need a way to convert these all into xls format and save them in the same location.
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code: Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx" End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
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.
I've seen a lot of code on how to pull data from workbooks in the same folder. However, I'm trying to pull the data in workbooks in various folders.
For examply, I have one folder, called "Master Files". In this folder is 12 folders, one for every month of the year. Within these folders, is one for each week, with the last day of the week being the name for the folder. In other words, it's like \Master FilesApril4-6-07data.xls
Now, I know how to get the data I need if all files are within the same folder. But how would I write a macro to run through all of those files when they're in separate folders?
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.)
is there a vba code which I could use such that it will show me how much processing has be completed.
So basically the user first enters all the information required for the calculation(which is basically a bunch of records on which the processing is carried out), then they press a button which will start the calculation however currently the user has to keep on pressing the refresh button (ctrl+alt+F9) and keep on refressing until the results have been calculated then they are available to view.
I was thinking if it is possible that after the user click on the calculate button a form appears which will start the calculation and within the code it will automatically refresh the worksheet ('s) and then when a field within the worksheet (Status) changes to completed the form will then disappear.
I am trying to do a conditional format for a cell and I think that it will contain some VB.
If a cell(C3) is a certain background color (gold), then I want another cell (E3) to subtract the number within the cell(C3). If the cell(C3) is not gold, then it is not to be subtracted.
I'm currently working on a project with data that includes columns for both latitude and longitude which I will need to convert from its current form into decimal degrees. The problem is that the coordinates are in the format DDDMMSS followed by a single letter indicating the direction, which means I have to first extract proper degree minute second format before I can do anything. Further complicating the problem is that each of the entries does not necessarily have all of the components I just described (eg some of the latitudes only have degrees and minutes but not seconds) and also if a longitude is less than 100 degrees, the third digit is not used in the data. There are over 2000 records so doing the conversion by hand just won't work and I've only got a few days to present this part of my project anyway.
I'm looking for a formula or series of formulas that will reliably convert something like 1083000W into -108.5000 when the last two zeros may or may not be there and the first digit may also be missing.
I get equation in the form of a string from the output of a software that I want to modify by Excel. I am no expert in Excel and I am wondering if some expert in Excel at this forum can do this for me.
see the attached Excel file. The string in cell A1 is an example string that I will be explaining how I want this string to be modified. Here are the attributes of the string.
a. The string will have ASCII characters in it.
b. The string is a mathematical equation. I will be pasting it into Mathcad once the Excel program modifies it.
c. The string has parameters and mathematical operators in it (+,-,*,/,^). Each parameter is seperated from another parameter by at least one space. There is no space in a parameter. So, each parameter starts with a space and ends with a space. The parameters of the example string in cell A1 are listed from cells A5 to A14 in the attached file.
d. If a parameter has paranthesis in it (), then the program will disregard any character in the parameter outside the paranthesis and take the characters inside the paranthesis. For example, it will modify the parameter in cell A5 from "F(u)_X1" to "u". If the parameter has no paranthesis in it, like the one in cell A14, then it will take it as is.
e. The format of the string is such that the multiplication sign may be omitted between parameters. If there is no mathematical operator between two adjacent parameters, it should be assumed that the math operator between them is multiplication. So, the Excel program should insert a multiplication sign between them.
f. Then the program will reassemple the string in the form of an equation by appropriately keeping the mathematical operators in the original expression.
The cells B5 to B14 show the modified parameters of the original parameters in the string in cell A1. The Excel program should put together the parameters in B5 to B14 as shown in cell A18.
Attached is a sample Excel file. In one worksheet, the RawData sheet, there is a column of text data. Each cell in that column has one or more values in the form: 2.x, or 2.x, 2.y, 2.z. The x, y, and z values are "Rqmt Numbers".
In the second worksheet, Counts, I am trying to count the frequency of each Rqmt Number. I've been trying to figure out the VBA code to do this for longer than I'd like to admit.
I am having trouble coming up with a way to retrieve certain pieces of data out of a text file. It is a file full of commands for some custom software we use. Here is a snippet of the file, the red parts are what I need to capture.
TOTAL RESET
Set 1=-1,8=-1,14=1,51=5,53=2,64=1,67=0,68=1,72=-1;
TABULATE TA-TOTWWRTHP TA-TOTCOLES TA-BI-LO TA-TOTIND3 TA-TOTIND2 (NORANK) BY L-MAT With ZVARIABLE FUTS-TOTWWTP FUTS-TOTCOLS..........................
I've been working on a variety of ways to display a 'loading' animation into Excel while other processes are running and although there seems to be a lot of strategies out there, none seem to jive with the form I've created. I've been reading a lot about "DoEvents", because that seems to be the common theme in trying to display a UserForm while other processes are running. However, I just don't have enough of a grasp of how it works to make it function properly with my form.
Basically, the form I have features several combo boxes, several command buttons, a few text fields and a bunch of labels. On initializing the form, captions for the labels and items in the combo boxes are added based on the ActiveCell and its surrounding values. Additionally, it includes an 'UPDATE' button that changes the captions and values on the form itself without hiding it, and once again makes reference to the active cell. This 'UPDATE' function can take quite some time and this is where I want to have a loading animation running.
Like I said, I've tried to achieve this in a few ways and the most hopeful avenue seems to be creating a separate UserForm with a WebBrowser element that references the animated GIF in question. However, when one UserForm is showing, the code from another will not run until it is closed. Thus, if I call the animation from the UPDATE button, the animation form will appear, but the update itself will not occur until the animation has been manually closed. Which defeats the purpose of a loading animation.
I understand that "DoEvents" is supposed to surrender some of the allotted processor power from the application so other code run, but the documentation surrounding it is rather convoluted to me. I can get it to work for simple operations, but I have not been able to simulate a situation where the command button on one form will show the animation form, run some code and then hide the animation with one click.
