I have several separate Excel files that are all formatted in the same way.
I want all this data, excluding the header rows, from those separate Excel files, to appear in a new/designated Excel file. - I don't want to keep copying and pasting.
I also want the data, once extracted/copied/exported, to be formatted according the formatting style on the designated Excel file.
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.
Is it possible to protect an excel file such that it will open up only on designated computers (identified by the computer name or some unique hardware identification like MAC address etc)?I was wondering if the VB editor can be used to do the same.
Let me put my requirement in detail:
I have an excel file "123" created in one computer (named=A). On this computer this file can be opened by anyone.I write a code such a way that, this particular file when copied on to other computers say (B,C & D) would open up as usual. But on computer E or any other computer, it should not open.
I cannot use password protect feature on the file as "n" number of users will be accessing this file on those designated computers. I was finding few of the clients copying the files on their personal drives or email without proper consent.If its possible, I would like to employ the same on few of my word (.doc) files as well.
I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.
Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.
VB:
Sub Create_Subfiles()
Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String
[Code] .....
I need to create time sheets for about 30 contract workers.
Each time sheet needs to have their name as well the date for sunday of that week inserted into the form. (once the date for sunday is entered into the sheet, the rest of the dates for the week will be extrapolated out).
Then, once the data is inserted for a single worker, I need that file to be saved using the workers name in the file name.
The script would then open a new time sheet template, insert the second workers name and the date for sunday and save the file. Repeate for next 30 workers.
I have an excel spreadsheet laid out as:
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.
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
Below is the data.
10010
10011
10011
[Code] ....
I have forms by differnet departments each day. The files are saved as the department's name then date Byrd 82708.xsl. I need a command button that will pull data from three cells in each of these forms. The master list will not be in the same folder as the deparment forms, the cells are E20, f20, f25. I have a text box were the user will input the date, by this date I would like all forms with this date in that folder to have their data pulled from those three cells and returned in master list.
View 5 Replies View RelatedMy project requires working with 3 different files, in my example here: Delivery Status, Warehouse, and Clients. Delivery Status is the master file where the information is gathered at first. Then when the product arrives, from the column "AI" I choose the Destination which it could be a warehouse or client. So far the code that I have can paste the information in different sheets within the File Warehouse, but can not make the difference between the File warehouse and the file Clients.
In the Delivery Status file I can choose from the Column AI whether the destination is a warehouse or a client and I need Excel to paste the information in the proper file. Have in mind that I have a lot of sheets in both files. I am attaching an example files with the code.
I have about 100 files with using date as file name (ie. 08.20.07.xls, 08.21.07.xls, 08.22.07.xls....etc) Each file contain exact same # of fields (Columns) but varying number of rows. I would like to have a macro in my "Consolidated.xls" file to go through each file and put them into a single sheet with the first column as date field (source file name)
Example:
08.20.07.xls contains
First Name Last Name DOB
John Doe 11/1/77
Jane Doe 12/1/78
""
""
""................
I would like to populate data from 1st File(Name:-Master Data) to the 2nd File(Name:-Calculator) using validation list in the 2nd file in such a way that once the Incentive Calculation is done for any store the same data should get populated to the 3rd File(Nameay Out) automatically without manual copying & pasting.
1)Master Data File(1st File):-Contains all the relevant raw data for incentive calculation.
2)Calculator File(2nd File):-Contains a drop-down validation in cell D4(in the sheet Named:-Hyper) and this drop-down contains the list of the stores starting from H001 to H032. I have got the Sumproduct formula to link Master Data File & Calculator File via this Drop-Down list which is yielding the Incentive Amount.
3)PayOut File(3rd File):-This is the where the storewise Incentive amount should finally be stored. I need help in keeping the respective storewise Incentive amount to respective locations even though different store is selected in the Drop Down Validation in the Calculator File.
I will share a small data pertaining to 2nd File(Name:-Calculator) & 3rd File(Nameay-Out) to further explain of what result is expected.
The following figure shows the results of H012 store(when selected via the Drop-Down validation):-
File Name:-Calculator.xls(Sheet Name:-Hyper)
Drop-Down List(=D4)Contains the Store names & links the Calculator File & Master Data File.
H012(D4)
Particulars(B7)Month1(C7)FMCG Sales Target12884356FMCG Sales Achievement17748294
C8=SUMPRODUCT(--('[Master Data.XLS]FMCG'!$D$6:$D$221=$D$4),'[Master Data.XLS]FMCG'!$H$6:$H$221)
C9=SUMPRODUCT(--('[Master Data.XLS]FMCG'!$D$6:$D$221=$D$4),'[Master Data.XLS]FMCG'!$I$6:$I$221)
The Above Sales & Target achievement for store H012(for FMCG Department) yields the Incentive amount in the same Calculator File(Sheet Name:-Hyper) as follows:-
Department(B17)Designation(C17)Incentive Details(D17)Month1(E17)FMCGFloor ManagerIncentive4641FMCGSupervisorIncentive3370FMCGStaffIncentive2276
Now finally the data from the above Incentive Calculation should get populated to the 3rd file named Payout(Sheet name:-Sheet1) as follows:-
Month1Store Name(A2)Staff Incentive(B2)Supervisor Incentive(C2)Floor Manager Incentive(D2)H012227633704641H013 H014
H015
Now suppose If I select H013 from the drop-down cell in Calculator file the incentive amount should get calculated and the same should get transferred to the Pay-Out Sheet below the H012 store incentive amount without changing or altering the H012 incentive calculation and so on for H014/H015 etc.
I have about a thousand Excel timesheets that all contain 'hours worked' data in a column. Each row contains the area of the project they have worked on and therefore the amount of time they have spent on it. The timesheets also contain the person's name and a w/e date.
I want to sequentially work through each timesheet held locally in a single folder and copy the person's name, w/e date and the hours held in the column into a single new spreadsheet. I need to transpose the data so each amount of time spent on an activity ends up in a column.
I have attempted to record a macro for this but each time I try and run it after the intitial run, it moves to a completely different cell or set of cells to the ones I have directed it and consequently there is no data copied to the new sheet.
I believe this is the first problem..! The second is working through a high number of spreadsheets held in a single locattion but whilst browsing this site I saw the "Excel VBA Loop Through a Folder of Excel Workbooks" page and think this should work fine.
Trying to learn the basics of how to pull data, from the same formatted excel spreadsheets, combined into one consolidated spreadsheet. I created a few examples below of what I am trying to do. Eventually, I want to be gathering data from over 200 spreadsheets at one time. I believe that a MACRO is needed to do such a thing. I am not sure of the complexity of the MACRO, so hopefully I will be able to mimic whatever needs to be done in my actual file. I have almost ZERO experience with MACROS, so if this is way above entry level MACRO.
Below I have 3 spreadsheets, and I want to collect the data from those 3 spreadsheets into the consolidated spreadsheet.
I would need only the first row data to be copied into a master file in column A (row A from each file to column A, column B.... etc - basically transpose value into master file)
View 1 Replies View RelatedI have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with:
Sub importdata()
' importdata Macro
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:Documents and SettingsMy DocumentsTender Document(1).xls;DefaultDir=C:Documents a" _
[Code]....
I'm a relatively competent VBA user to a macro recorder and basic editing of custom code level but fall short with writing custom code and don't have much used or proven code I can copy from.
Problem: I'd like to automate a process whereby consolidating certain information within many data files (possibly up to 500) into a single tab within a master file.
At the moment there are only a few data files which are manually consolidated by way of manual links but going forward it will increase significantly, hence the need to automate.
I need to consolidate the following 5 cells from each data file A1, A2, A5, A7, A8 (vertical) into a single row within the master file across 5 columns (horizontal), i.e. each data file will populate 1 row in the master file, one below the other. If there are 500 data files there will be 500 rows of data in the master file.
The data files will be saved in a central location on our server and the master file will sit outside this folder, possibly in a subfolder.
Something which would be handy is a link in the master file to each data file, i.e. if I click on a data row in the master file it would jump to the source data file.
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with: .....
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies View RelatedNeed a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
I need to import thousand of txt files into 1 worksheet keeping the file names as data. Each txt file has 2 columns :
1 0.65914
2 0.65945
3 0.86062
... ...
and each txt file represents recordings made at specific time and date (e.g 0158.DSG_RAWD_HMS_21_ 0_ 0__DMY_29_ 2_12_pulses). I would like to have 2 columns: 1) with the time taken from the name of the txt file (e.g. 21:00:00) and 2) the associated recording. Something like this:
21:00:00 0.65914
21:00:00 0.65945
21:00:00 0.86062
and so on for each txt file and all the recordings piling up in 1 spreadsheet. I have tried to run few codes in VBA, but I have no knowledge of it and none of the code worked. I am using Excel 2010.
I have approx 11 files in one folder and one master file with same format. 11 files are split user wise and user inuputting the remarks against the invoice in coloum Y and Z in their respective files. I want macro/forumul to collect all the remarks coloum from all users to master files against the respective invoice no.
user file format :
file name temp-1.xlsx
A B Y Z
USER
INVOICE
Remark
Follow-up Date
[Code]......
I have the following macro that imports data from several input files and rearranges it in a master file.I want to change it so that I can use it in each of the input files. Therefore, it should look in the input file for Spreadsheet "XYZ" and rearrange it in Spreadsheet "Data".
[Code].....
I am trying to open set of excel files to fetch data starting from a master excel file where the links are given using a loop. Some of files given as links are either absent or the link is wrong.
* What is the syntax to find if the link is correct/present
* What is the syntax to find if the file is present in specified location
* What is the syntax to find if the file is password protected to open
* I use error handler to resume the next statement if OPEN statement fails.It works fine inside the loop only for first two files.
For example if the third link is errorneous the error handler fails and RUN TIME ERROR occurs.
i have attached a sample for you to see what i am after.
I am after the following:
1. Export each column to a text file
2. Need the results from rows 2 - 30 in the text file.
3. Want it to save the files as row 1.
I have multiple files in a folder.
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.
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
I am looking for the VBA code to copy worksheets (with formatting) and save to a specific folder. The steps I am need to follow are:
Create a copy of the first worksheet
Save it to a specified folder and name it with the worksheet name
Repeat with all worksheets until the end of the workbook
Hi everyone,
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?
For example:
3 separate files > File1, File2, File3
Merged file, sheet1
File1
File2
File3
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
G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc
The files contain the following data:
DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc
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:
FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc
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
[Code]...
I am trying to separate a filename from a filepath in Excel VBA 2003.
I am using the Application. GetOpenFilename command to get the user to select the correct file.
However, this also seems to record the filepath.
I am trying to separate the filename out of this so that each file( name) activates a different sub procedure.
I am storing the result of the getopenfilename as a Variant.
At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.