Processing Multiple Files
Oct 10, 2006
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)
View 4 Replies
ADVERTISEMENT
May 11, 2007
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?
View 9 Replies
View Related
Mar 8, 2007
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
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
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
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 31, 2007
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.
View 9 Replies
View Related
Jan 17, 2007
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.
View 9 Replies
View Related
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
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
Dec 12, 2009
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.
View 11 Replies
View Related
Jun 8, 2007
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.
View 10 Replies
View Related
Dec 7, 2007
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.
View 11 Replies
View Related
Jul 25, 2008
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.
View 11 Replies
View Related
Feb 10, 2010
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.
View 3 Replies
View Related
Aug 27, 2009
Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.
One way - Press Escape and it goes about twice as fast! - Wy does it do this?
Another way - Inserting these lines into the code -
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
View 9 Replies
View Related
May 12, 2006
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..........................
View 2 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
Nov 20, 2009
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.
View 9 Replies
View Related
Jan 24, 2007
I have created 2 seperate VBA's that I would like to combine and improve upon. One removes Duffs, and the other eliminates blank rows. My goal is to do a copy and paste/special, then remove the duffs, and then delete all rows that do not have data in column H.
I have tried several VBA's listed on this sight, but cannot seem to get a VBA that does everything I need it to do, and do it quickly. I have attached an example of my spreadsheet.
View 5 Replies
View Related
Mar 1, 2007
I want to open each file in a folder, one at a time, and save each file with a new name (based on cells in the worksheet) to a different folder. This is what I have so far:
Sub aaa()
Dim i As Long, files As String, data As String
Dim fName As String
ChDir _
"C:Files1"
Cells.Select
Range("A1").Activate
Selection.ClearContents
Application. ScreenUpdating = False
i = 0
files = Dir("*.txt")
Do While files <> ""
Open files For Input As 1
This code doesn't open any files unless there is a value already in the cells that fName uses.
View 6 Replies
View Related
Oct 30, 2008
about making my code a bit more useful.
I started with a basic code as below which basically looks at upto 1000 part ids in column A and goes to my database to find corresponding Product Codes from a table in the database call PART
View 14 Replies
View Related
Dec 30, 2013
I have a very long (> 2 hr) macro that sifts through thousands of files and creates a table of contents (more or less). Anyway, the macro will operate just fine when I am also working at the same PC but if I walk away for an hour, Excel will sometimes generate an error (application defined or object defined error). I believe that it has something to do with the PC going idle although it really isn't going idle because I turn off sleep and hibernate while running the macro. Also, there are only two locations where the error seems to occur. See below:
Code:
Dim oApp As ObjectSet oApp = CreateObject("Shell.Application")
Dim files As New Collection
Dim fZip As Variant
For Each fZip In oApp.Namespace(zipFile).items
View 7 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
Jun 17, 2009
I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.
View 4 Replies
View Related
May 8, 2007
I have an Access application with a form containing an Excel chart based on a pivot table based on an Access query based on an Access table which is refreshed every 5 minutes using a Timer event. When the table is refreshed the Excel chart is refreshed. The chart monitors some processes and is active throughout the day. As the day progresses more and more data points are available for plotting on the x-axis. The x-axis labels get very crowded as the day progresses and I need to programmatically change the x-axis scale at various times of the day. To that end I am trying to find an event that is activated at some point during the automatic refresh of the chart/pivot table.
I have tried the Workbook_Change event, the Worksheet_Change event, and the Chart_SeriesChange event. None of these are executed during the refresh process or I have done something wrong in trying to use them. At the moment they only have a MSGBOX message in them so that I can determine if they are even being executed.
View 9 Replies
View Related
Apr 10, 2007
I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.
View 9 Replies
View Related
Sep 23, 2009
I have an excel file for each day of the month. Each file has 2 sheets for sure and some files have 3. I want to make one master file at the end of the month that shows all sheets. *1st sheet is master report. 2nd & 3rd sheets are created as a pivot table.
View 2 Replies
View Related
May 15, 2006
I have a question about CSV files...I have to import about 80 csv files into excel as separate sheets in the same workbook. I can only get it to import into its own workbook. Is there a way to import all of them into the same workbook all at once?
Also, I am currently using csv files, but if what I want done can be done with a different file type, it doesn't matter.
View 7 Replies
View Related
