I have a folder containing about 100,000 files and subfolders. Approximately 20% of the files are duplicates. I would like to create a list of all the files with some details so that I can sort through and identify which ones are duplicates, then create a batch file to delete them. How to get a file list with additional details, like size, modified date, etc, into an excel format?
View 3 RepliesI have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.
View 9 Replies View RelatedOn my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:
Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"........................
What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).
Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.
View 8 Replies View RelatedI have created a monster Excel file that contains loads of vlookups and is 95MB and extremely slow.
I guess that vlookup creates tables in the background, hence the file size. Does anyone have any ideas on how to decrease the file size while keep the vlookup functionality?
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies View RelatedI am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
I put together some code which at the end of it I am wanting to show in cell D1 the creation date for the most recently created file found.
View 5 Replies View RelatedI am adding a list of file names to a combobox list and want to filter this by adding only the filenames that have been modified today. I have used the FileDateTime(Fil) but it leaves the combobox empty. I am using the code below. how to add only the files modified today.
View 3 Replies View RelatedI have a series of files, all named sequentially by date, for example...
Sales Report - Week of 2009-07-27 - NH.xlsm
Sales Report - Week of 2009-08-03 - NH.xlsm
Sales Report - Week of 2009-08-10 - NH.xlsm
...and so on. (The dates are always Mondays.)
I want to get the contents of a particular range on the previous file (e.g. the "RaceResults" range a.k.a. B4:B33 on "Sales Report - Week of 2009-08-10 - NH.xlsm")
I have a workbook which has a file size much bigger than I would expect. To test it I inserted one blank worksheet and deleted everything else. I have removed a macro that was embedded in the workbook. I have removed all protection etc. I would now expect the file to be similar to a new workbook (circa 13k) but it is still 782kb even though there is absolutely nothing in it.
View 5 Replies View RelatedI built a workbook that is used by our account team to summarize account status. Each worksheet has several cells that use conditional formatting to call attention to the status reported in that cell. Each manager sends their worksheets to a Senior manager who then consolidates into a single workbook containing approximately 50 worksheets. Once consolidated, the file size has grown to over 12 Mb and this needs to be reduced to a more manageable number, preferable 4 Mb or less. Prior to buliding the new version of this workbook, the file size was less than 2 Mb.
I have removed the Macros and believe the size is due to the conditional formatting in each worksheet.
Once the worksheets are compiled, is there a way to remove the conditional formatting and save the existing format - Similar to a "Paste Values" function? The senior manager does not need to change cell value to the formatting of the worksheet he receives could be saved.
To be specific - one cell turns has no color if the value is 10 or less, is yellow if 11 - 15, and Red if 16+. Once sent to the manager, the variance in this cell is not needed; he only needs to see the color associated with the value when sent. Next week he will receive an updated worksheet and a new value will be entered. The conditional formatting is only needed by the individual preparing the original worksheet.
I am creating a statistics database for my personal use. I have been developing this for many, many months now, and I have learned a lot about Excel and its capabilities in the process. However, I am becoming concerned because my files occasionally crash Excel, and I am wondering whether this is because my files are getting too large. Typically, the crashes occur when the main file is calculating (but not always). It stops responding, and I have to force the files closed through Windows Task Manager.
Without getting into the specifics of how the files work (although we can do that if it is necessary), here are the names of the files and their current sizes:
Baseball Database.xlsx -- ~28 MB
Baseball Reports.xlsx -- ~5 MB
Statistics.xlsx -- ~4 MB
I will have all three of these files open simultaneously when I am working on them, and my laptop has 4G of RAM. I do not have much (if any) extraneous formatting in the files -- I know how to remove that -- but there is quite a bit of conditional formatting in the files that I want to maintain. I am considering partitioning (separating) the tabs in the Database file into separate files.
I have a spreadsheet which is currently about 10 MB, I would like to reduce this as much as possible.
I am using lots of Vlookups but on each I am only referencing the data I need, there are also quite a few macros.
I paste value all the formula I do not need.
I have an 11mb file with:
5 worksheets
30 columns per worksheet
Total of 1300 rows of data across the 5 tabs
The row heights are not uniform across all worksheets but when I change them all down to 65536 on each sheet to height of 12, the file reduces in size from 11mb to 3mb.
It concerns me as it is almost like data could be getting lost.
I have a workbook that permits the user to enter data in no sense of order. From the original I am working for, there could be over 1500 rows of data. I then created a new worksheet to accept the unordered data to be sorted by a customer and date. I then created 20+ new worksheets within the same workbook, that will accept via an IF statement, the data for each customer. The problem as I see it is as the formula runs, I need to delete the blank rows on each of the 20+ worksheets. This is fine and the customer is happy with the set up. My problem is the file size. With just the formulas/worksheets, it is over 62 MG.
Is there a different formula/function I can use that won't require so much space?
I have xls file size 50MB
I want to reduce size kindly give me some tips to reduce size.
I created a new form for our field personnel to complete daily. They are telling me that over a few weeks the file grows in size. The file size is 335KB starting out end up in a few weeks being 3.5megs. Now they add on some other sheets and things which undoubatbly make the file size larger but I am wondering if there is anyway thet VBA could somehow make the file bigger as the open it each day over a few weeks? They are not adding in any VBA, they are only using the code which is provided, which is accounted for in the 335kb. It seems unlikely but i thought i would ask.
View 9 Replies View RelatedI created a new excel file with filtering and after i saved the file , i found that the size for this excel file is 19 MB, so is this normal size for an excel file or there is a problem in this file and i have to fix it
View 9 Replies View RelatedI don;t know if there is a way around this but we have a Excel workbook that holds around 14 worksheets taht is getting too large. It currently is around 2.5mb and we constantly use it as a template which is completed and emailed. Sometimes we have to email 4 of these all together and so teh email is too large, even zipping them has next to no effect on the size. The sheet that seems to causing the problem is one that has around 500 lines of formula across 20 columns. Most of these fields have conditional formatting and its this taht is boosting the size.
View 6 Replies View RelatedI am working on a spreadsheet that pulls data from an external pivot table.
The spreadsheet is 25 columns wide by 72 rows. I have 15 worksheets currently but need to add another 7. The current file size is 22MB and I keep getting a NO MEMORY error message.
I am not using any fancy formulae just references to an external pivot table.
I currently have an excel file with lots of formulas on quite a few pages and these are carried down for so many records - its sort of a template to generate information from/to certain sheets for many other purposes.
- is there any way to compress the file (without actually zipping it) as it must remain a working document?
My excel file has grown to 23Mb. It has around 10 worksheets and a similar number of userforms. Because the file is large, it slows down the VBA code when it encounters a save command. Is there any way I can improve this? Do userforms use alot of memory or is it the length of code? There are a few images on the userforms but the images are less than 1 Mb in total when all added together.
View 3 Replies View RelatedMy file is currently about 70,000 mb,consisting of 10 spreadsheets.
Can i decrease the size by combining the spreadsheets?
Also, will nested formula take longer to load compare to 3 different cells referencing upon each other?
list that tells us how much each and different formula affects the size of the file?
I am putting together a price list in excel and using images and hyperlinks to jazz it up a bit - what I am finding is that if I insert a 25KB GIF image, the excel file grows in size by over 100KB
why is this disproportionate growth happening and is there a way around it - I dont want my Price List growing too large but need the images....?
I have a table made up of 4 columns and variable number of rows. Column A has 8-digit number; B has 8-digit number; C has text up to 40 chars; D has a number with two decimal points. I would like to generate or replace a text file in csv format containing each row's data EXCEPT where column D value (number with 2-decimal points) is 0.00.
Using Excel 2003.
I have a restaurant and every evening the manager emails me a sales report. The names of these files are all based on the day's date so I know what the file name will be in advance (e.g. today is 20120109.xls and yesterday was 20120108.xls)
I have a master spreadsheet collating the monthly sales data. It links to the daily spreadsheets with links like this:
=IF(ISERROR(VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE)),0,VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE))
Each day I create a new column and drag the previous column's formulas over. I then do a find and replace to change the file name (20120105.xlsx in the example above) to the current day.
What I want to be able to do is change the filename reference in the formula above to a cell within the master spreadsheet that contains the date, so that when I drag over a new column it picks up the date from that cell and automatically uses it to create the filename in the link. The path for the file name remains the same.
I have a file that I want to name based on content in certain cells. I.e. "Q-(text from cell G1)-(text from cell B7)-Date(mm_dd_yy)". I want to be able to specify the location where I save this file each time I try to save it, but I want it to update the file's name if any of the parameters have changed.
I have some code generated that does just about everything right except that it automatically saves a version of the file in the folder where the original is located even though it also brings up the dialog box and allows me to save it in another location.
Basically, I want to fix the code below so that it doesn't save what appears to be a backup file each time. Should I be using some sort of savecopyas?
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Add the quote # to the header
ActiveSheet.PageSetup.RightHeader = "Quote No. " & Range("G1").Value
[Code].....
I am thinking to make a excel file which can generate a headre file for my c++ source file. Previoulsy we used to generate .h files using excel but i dont know the logic behind that(Hope some macros using for that).
MY header file contains this many data and my intention is to give "MYapp Alpha 0.0.3" through excel file because the version number changes for each release. If i used excel file then I can edit that excel and it creates .h file for me, later some more informations i can make configurable through excel file
#define APP_FLASH_APP_ID 0x123
#define APP_VERSION_NUM "MYapp Alpha 0.0.3 "
#define APP_PRODUCT_NAME "TPI "
#define APP_DESCRIPTION_STR APP_PRODUCT_NAME APP_VERSION_NUM
#define APP_RELEASE_DATE_STR "10/11/13"
#define APP_SOFTWARE_PARTNUM_LEN 10