I have a large number of files that are structurally the same - only the data and the file names are different. I want to insert some code into all of them.
I don't yet know how to open all the VBA pieces together at one time for editing. Is that possible? Otherwise all I know how to do is a manual, file by file, process.
I have created a workbook to input, store and part edit defects on plants and machinery by userforms. I have everything working except either picking up a number I have pre-loaded into a column or auto generating a number onto the inputting userform. I need a number in the userform to be either generated in the next empty row in column I or taken from the numbers in the next empty row I have loaded into column I. Password is snatch.
Input 2003 test1.xls
I am extracting data from an electronic medical record (EMR) which has many search limitations. I am able to export data into a CSV file (attached) to perform some calculations (All the patient data is fake btw). I am trying to find the number of appointments for each patient within a specific date parameter. For each patient, one appointment appears on one row.
pt appt sample.png
What I want to do is count the number of rows (appointments) for each patient in an efficient manner. Right now I've just used the COUNTA function, but don't want to repeat this for every patient, due to the way that the workbook is set up. The data is all from the CSV file from the EMR; the only column I've added is the "Appointments since 2013" column.
The patient data is fake because I am using a test server for the EMR. However out in a real doctor's office, there will be over 2000 patients with possibly up to about 100+ appointments.
Not sure if there's formula out there that would make my task a lot easier and more efficient, or I need to play around with the columns to set it up differently.
In the attached work book I have 2 sheets named, Data Entering page-plan and Varaince Summary-in hours.
Data Entering page-plan - is used to enter data
Varaince Summary-in hours. – is used to retrieve data
In ‘Varaince Summary-in hours. sheet in Cell A6 I have a drop down to select the job nos, according to the job no selected date, position no, and milling hours are filtered and displayed. I have written a index/match formula in G6 and have dragged it downwards since I don’t know how many positions would be fetched in each job no selected. Is there any way not to display N/A in unused cells in Column G each time a job no is selected ?( embedding the function in a IF using ISNA or ISERROR would be inefficient as I feel since this sheet is going to house lot of data of about 30 sections, I have shown only one section here)
I've managed to clean up my code removing all of the selecting to get the macro to run faster but it seems to slow down when it gets to the page setup. Is it because of the ActiveSheet? Is there a better way to code the following?
View 10 Replies View RelatedI have a two workbooks, one which is a daily schedule and the other is a yearly summary. The worksheet in the yearly summary uses a date in the first column and the daily sheets each have a date on them. I've isolated my data in the daily schedule and I've retrieved the date in its numerical equivilant. I have data that is 6 cells wide by "x" amount deep on any given day. Essentially, I schedule different things to be made and each row has that designation, process, dimensions, quantity, etc.
I want to test the contents of the first cell in the array, copy the pertinent data, switch to the proper worksheet, and paste it into the line where it goes. I believe I need to check a few cells to narrow down exactly which group of cells on which sheet they would be copied to.
I am not trained at all as far as programming goes, but I've been practicing for a few years now. What I am just going to start doing is testing the cells in the rows in the array of data and trying to divide it out that way. This just seems like the long way around.
The report that I am trying to build relies on DCOUNTA formulas.
This report has 200 lines and 200 columns approximately. Each of these cells would be a DCOUNTA formula. I have a separate sheet, 'Criteria', where I intend to describe all of my criteria for the DCOUNTA formulas.
My problem is that for each DCOUNTA cell in my report, I have to create 2 rows and 5 columns worth of criteria information... so, for 40,000 cells to populate, I need to create a huge sheet of criteria.
So, while I could do the above, and it would take me some time, more efficient and elegant way to spend my time in order to get this report display DCOUNTA formulas?
For more context, I am trying to present information for 100 individual salespeople, monthly and year to date. For each cell, I need to tell DCOUNTA that the date needs to be comprised in the current period (2 columns), what the name of the salespeople is, and what the nature of the information is, which could be more than 2 additional columns.
I can to some degree autofill most of this, with some OFFSET magic, but its still feels like an ugly way to achieve my objective.
How can I find out the number of files in a folder when coding in VBA? I need to use the number of files that I have as the bound on a loop in the code, and the number of files in the folder can vary so I can't just count them.
View 9 Replies View RelatedIs there a way that I could monitor the number of files in a folder and its contents (including subfolders)?
Folder A: (total count of files)
Subfolder
Folder A.1 no. of files containing boy
Folder A.2 no. of files containing girl
Folder A.3 no. of files containing boys and girls
I've got 80 CSV files that I need to import into an Excel sheet. The filenames are sequential, eg 1APN .... 80APN). I need each chunk of data added after the previous. How can I do this using VB?
View 2 Replies View RelatedI have a workbook that will need data from another which will be located in the same directory. I need to open this workbook however the filename will be imported daily and be different each time (i.e. 010707.xls, 010807.xls, etc.) The only two files residing in the directory will be the active file and the one that needs to be opened. Is there a way to assign a variable for the 010707? Currently I have the following and need to manually modify the file name: Workbooks.Open Filename:="C:My Documents emplate10707.xls"
View 4 Replies View Related is it possible if I have a directory like the following
[Code].....
with .csv files which are placed in arithmetic order 01,02,03 etc to determine if one .csv file is missing and print an error that this file is missing? I think that if I knew how many files I got I would create an array and check one by one the files.. But in case I have a dynamic directory and the number of files is not the same all the time , is this possible?
I have written a procedure that renames excel files within a folder based on certain words being in the title. It works for the files in the first lot of subfolders but I am trying to work out how to get it to rename all files in all subfolders regardless of the level.
I've seen similar things done using recursive subs but I have been trying to convert some of the sample codes I've found online to fit my situation and am not having much luck.
how to manipulate my code into something like a recursive procedure or anything else that will do what I want.
Here is my code.
[Code] .....
a method of accessing all files within a folder to get the number of rows/records and contents of "A2".
The file could ideally look at the current folder it's in and collect the requested data.
Hopefully I can work and develop the code to suit.
Is there a way to rename every file in a folder and subsequent sub-folders to begin with a number in memory? Say my number in memory is 5000, I'd like all the files to be renamed something like this:
5000Picture.jpg
5000bookreview.txt
5000startmanual.img
5000HighFive.mov
-->5000SUBFOLDER01
----->5000SUBFOLDER02
I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.
I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.
For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.
I am using this codes to extract csv in to my workbook, how to extract only the specific no. of rows from a csv instead of all the rows, I would like to extract only the last 5000 rows of data
Code:
Option Explicit
Sub ConvertCSVs()
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim strPath As String
[Code] .........
Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..
View 1 Replies View RelatedIn Column A I have the list of strings that I need to search. I want to count the number of occurrences of these strings inside these two text files: Sample1.txt and Sample2.txt. For example I have the string "DOG". I want to search and count the number of times this string appeared in Sample1.txt, and Sample2.txt
See below:
I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.
Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?
What im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.
The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.
So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".
I made the mistake of creating hyperlinks to files on a server (using the \SERVERNAME convention) without using the "hyperlink base" option.
I've been sufficiently chastened, but the present problem is that I have many such hyperlinks that are now worthless because, when saved, they point to "../../[foldername1]/[foldername2]/[filename]", etc.
Need macro that would replace the first five characters ("../..") of all the hyperlinks on my sheet with //[MYSERVERNAME] ?
So I am attempting to run a macro that contains some if then statements that will change the text in a cell given certain circumstances. I am having an issue specifying the range in that it begins in cell A2 and it goes to the last data cell in the sheet and no farther. The issue with just selecting a whole column is that if the a cell is blank, it fills it in with no and it leaves me with 65,xxx nos in column A. Attached is the workbook with the sheet the code would be executed on and hopefully what the solution would look like. Also i have attached the troublesome macro I have written.
View 7 Replies View RelatedWhats the key in editing macros to know that you are not getting compile errors and how do you know when your in VBA which statements are repetitive? Do you always go by the macro recorder?
View 8 Replies View RelatedI have imported values from another program. Imported values are in 000:00 form (000 for hours and 00 for minutes). If there is less than 100 hours the value is still not treated as [h]:mm. For example:
029:15
How can I erase the first one or two zeros??
Is there a way to copy and paste my notes from Word into the text box once it is embedded in the excel worksheet?
View 9 Replies View RelatedI have two CSV files that I would like to edit through Excel VBA. Both files have too many lines to be shown in Excel though, so I can't just use cell rerences. Every so many lines I want to delete a row and insert a row from one CSV file to the other. I could easily write a loop do this with cell references, but since all the lines aren't shown I can't do that. I have no idea how to reference all the rows in the CSV file with VBA when most of them aren't viewable on the worksheet.
View 2 Replies View RelatedI have 150 or so workbooks. Each of them are in the same format. I need to edit this format. Bolding, Borders ect. I know you can do this with multiple worksheets but how can do do with with multiple workbooks?
View 12 Replies View RelatedI am trying to create an asset management database using excel, and would like to be able to add custom notes to each entry (index) so as time goes on, I can refresh my memory what we have done with a particular item. I have attached the current file I have created.
Essentially, when I use the GREEN drop down menu in the top right, I want it to display the notes/comments that I enter in the rightmost column of the data, as the attached file illustrates.
However, I want to be able to hide that rightmost column and be able to edit the data from the new area (white space) it is being displayed in, however as I have it set up right now, if I edit the cell, it edits the forumla and poof goes all the comments and the formula becomes messed up.
I have a table on sheet 2 consisting of 120 rows and 8 columns, on sheet 1 I have a combo box and have used INDEX to display the values for each row that I select. what I want to be able to do is edit the INDEX data on sheet 1 and then have a button that will replace the old value in the table on sheet 2 with the new value.
View 5 Replies View Related