Excel 2003 :: Deleting Rows For Multiple Files In Separate Workbook
Sep 10, 2012
I'm new to this forum and to VBA
First-off, I'm using Excel 2003 SP3.
The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg
I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.
My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.
I have a separate xls file with criteria data in two columns:
The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.
the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!
Option Explicit
Sub CopyPaste()
'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).
'The data from the Results sheet is then copied to the Master sheet.
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String
I know how to separate data into different columns i.e. Marry Johnson into two separate columns; however, I have data in different rows that I need separated into different column. See screen cast [URL]
I have a company with upto 5 products, and I have created a dropdown box where you select your product.
When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.
I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.
A B C D E F G H I J K L M N O 1 ** ** *** ** ** ** ** *** ** *** ** ** *** ** ** 2 ** ** *** ** ** ** ** *** ** *** ** ** *** ** ** 3 ** ** *** ** ** ** ** *** ** *** ** ** *** ** ** 4 ** ** *** ** ** ** ** *** ** *** ** ** *** ** ** 5 ** ** *** ** ** ** ** *** ** *** ** ** *** ** **
I need a macro that can automatically export the cell data for each row from Column A--> O into it's own seperate text file, and name each text file with the text in column A. So again... everything in row 1 from A to O would be copied and pasted into a text file called (A1 cell data).txt
once that first text file has created, I was hoping that there could be a loop to automatically close the first text file and move on to row 2 and do the same, copy all text in row 2 from A to O into a new text file titled (A2 cell data).txt, close and move on to row 3 etc etc.
this is a large list of approximately 8,000 records.
When selecting multiple excel files in explorer, you can right click and choose to print the selected documents. All documents gets opened and printed but the files stay open and you have to manually click on YES or NO to save the changes.
Is there a way not to see this window appear. So documents get opened and printed and closes itself without asking anything. Maybe it can be done with an option in the preferences. I'm not sure.
By the way the documents where created with excel vba just in case it's relevant. Also the version of Excel is an older version, I think it was Excel XP or 2002/2003.
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.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have five excel separate excel files containing values covering more than 500,000 rows each. I want to put then in a single excel workbook without tedious work of copy/paste to sheets of this workbook.
Any way of Deleting duplicate records, using Excel 2003.
The raw data is in the form of a text string. For example:
Vehicle 123456_F_AB 280 Vehicle 123456_R_AB 147
So when I do a count of these cells, I obtain a count of 2. '=Count(A1)'. How can I easily exclude 1 of the duplicates (123456). It needs to be a user friendly solution as other people may have to run this when I'm on leave?
Running Excel 2003. I have a list in column B, of numerical codes. What I want to do is find the duplicates, and list the duplicates in column D. Is this possible?
I have to delete data from D15 to AZ129, D168 to AZ282, D312 to AZ426 in 46 excel files, saved in the same folder. I go through the process manually by opening each file and then deleting data.
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
The files are named: File1.xls File2.xls File3.xls
....and so on; the number of files varies. I am trying to write a macro, to combine all *.xls files in one workbook having each file as a separate worksheet.
merging the multiple *.xls files into one single *.xls file but each *.xls file com in separate worksheet.
Say i have 30 xls files in datewise i.e., 01.10.10, 02.10.10, 03.10.10 so on....
I want to merge all the above 30 xls files in single file master workbook - in that master workbook file the above 30 xls should come in separate work sheets.
I have code with downloads files from our work intranet site. When the files download the are zipped up. Is there a code which will unzip and open these excel files?
Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
I'm using 2003 (I know!) version & I can't open excel files directly by clicking on them. I get an "error in sending command" message. I can circumvent problem by opening Excel & then opening the relevant file, but this sometimes causes me problems.
I've got several Excel files that need to be edited separately. Once they have been edited, I need to paste the content of each file in a single separate Excel file.
Is there a way to retrieve the content from all the files in a folder, and merge it in a single file?
I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.
VB: Sub FormatMacro1a(ws As Worksheet) ws.Copy Rows(1).Delete Columns("D:E").Cut Destination:=Columns("J:K") Columns("F:K").Cut Destination:=Columns("D:I") Range("E1:E201").Value = "0"
[Code] .....
As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.
I have been using VBA to copy and move files to create backups for 7 or 8 years now, and it works great. However, recently I have been working on a way to use SD cards for the backups and the FileCopy command is much slower than using Windows Explorer.
I am looking for some alternate command lines that might do the job faster. I would like to know what Explorer is using.
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
I have a large list of coordinates that I pasted from a website, and in Excel it has an emty row between every coordinate. I have about 2,000 cordinates, so ~4,000 rows. Is there a way to delete every other row besides manually?
In my spreadsheet, column A can contain the same word over several rows. If column B is blank at any time - I want to delete all rows that have the same word in column A.
Apple 1 Orange 2 Apple Peach 1 Peach 2
So in this example - I want to delete rows 1 and 3 that show the word Apple.
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.
Sub DeleteRecords() Dim n As Long, lastrow As Long Dim ty As String Dim a As Integer lastrow = Range("D65536").End(xlUp).Row a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24) For n = lastrow To 2 Step -1 ty = UCase(Cells(n, a)) Select Case ty..........................
I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.
So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.
So if I had the below sheet:
I could search by column a and add up column h to read like this:
I have a hyperlink from cell M2 in a worksheet to cell B2 in another worksheet. I typed the cell reference on the target worksheet as B2. this works fine. The problem is that when I copy/paste the hyperlink down the column, the reference cell stays at B2. I need the reference cell to change when copied down to B3, B4, etc.
