I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??
View 3 RepliesIm trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.
The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!
How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.
VB:
Sub openandcopy()
Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Application.GetOpenFilename)
[Code] .....
I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False
[Code].....
I have made a userform that works as a switchboard, and when you click on a button it should run a code that runs a batch file.
but for some reason the code does not work, can someone show me where i am going wrong...
Private Sub CommandButton1_Click()
Shell "c:dataexcel oolkit downloadscombine files.bat"
End Sub
I currently have 3 batch files that I would like to be able to update using VBA instead of manually adding the information to each one. My goal is to be able to use my excel file and when I add new lines I could then run the script to save and/or update each of the batch files. I would like to be able to add the new lines I added in excel to the end of the batch files. I use the batch files for automation purposes but I do not like the fact that I have to open each one and added the same lines three times. This is why I want to be able to update my excel file and then add those new lines to the batch file. An example of how to accomplish this task using VBA would be awesome.
View 2 Replies View RelatedI'm using 2007 Excel and I have a list of 200 JPG files on column A which mirror actual file names of files I have located in C:photos.
My objective is to use Excel 2007 to batch rename all the files located in C:photos from the names listed in column "A" to the names listed in column "B" that I have created.
a VBA script that will accomplish this task?
I am trying to rename a lot of files within 1 folder. The Spreadsheet Column A contains all the old file names within a folder and Column B has the new file name. (1)My code is below but I continue getting an error that the file isn't found. (2) I would like to start with row 2 because row 1 will contain the folder path as a result of another macro.
[Code].....
After working on this script for a while, I have come up with this.
Code:
Sub CopyAndRenameImages()
Dim fs As Object
Dim oldPath As String, newPath As String
Dim LastRow As Long
Const PW As String = "Password"
[Code] ..........
Handler:
MsgBox oldPath & " cannot be found."
Resume Next
End
End Sub
But for some reason it is always pulling an error on the last item in the list, even if the file is there.
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
Im trying to find a way to automate the download of several csv webpages into a folder on my desktop. The webpage url's use the following format:
[URL]
would it be more efficient to use some kind of batch script?
Is there a macro to batch save many xls files to text (prn) files?
View 9 Replies View RelatedIs their a way of creating a batch file which will open an Excel workbook and run a single macro?
View 9 Replies View RelatedI wanted to run a batch file which does something like,
Dir /b >Test.csv
Start Test.csv
And then runs an Excel macro once the workbook is open.
I looked into this and discovered that it’s not really that possible to run an excel macro through batch file but other options were to use ‘Workbook_Open’ etc.
I thought this might cause problems because Test.csv doesn’t contain a macro to auto open and because csv’s usually don’t like macros.
I do however have personal.xlsb which opens every time if that helps.
I have a list of petroleum accumulations with three types of data: Reservoir depth, Net Pay and Gas-oil ratio. for each accumulation, the number of reservoirs within each can vary. So I may have a accumulation with only one set of those stats, or an accumulation with 20.
I need the program to go down an alphabetized list (8000 entries long) and after every unique accumulation name average the values of the stats(which are in three separate columns) then put it in one row perhaps beginning in Column M or another sheet. So that I have a list of accumulations with the average of those stats, one entry per accumulation name.
I have a workbook that contains worksheets. They are listed as follows:
Sheet 1ABCDEFG
In cells A1 - A49 I have text. What I would like to do is to have a macro that I can run that will basically copy and save new workbooks with sheets A - G copied over and have the new workbook saved with the file name that I have denoted in cells A1 - A49 on Sheet 1. Also, the macro would ask me where I want to save the new Workbooks.
For example, if this were Sheet 1, Column A then the cells below would be the saved name of the new workbooks and the new workbooks would have Sheets A - G in themRed
Blue
Purple
Black
White
Yellow
Orange
Green
Gray
Brown
One more piece of information, the file that is being copied and saved is large (~80MB). If there is a macro that would allow me to simply "save as' the workbook and the Saved Workbooks would be named using the data in Sheet 1, that would work as well in case copying, pasting, then saving may take more time
I've 2 excel files open. The first with my data, and a second with a huge range. I need to work with a macro that used vlookup to get a value from the second file in the first.
My code:
VB:
Sub mysub()
Dim i As Integer
Dim y As Integer
Dim match As String
Dim no_match As Integer
Dim rng As Range
'Set rng = Workbooks("wb2").Worksheets("Sheet1").Range("$A$1:$B$2704")
[Code] .....
I need to know if it's possible to log the process of a series of macros
either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)
For example:
Sub MyMacro1 ()
code
End Sub
Sub MyMacro2 ()
code
End Sub
If this process was logged, then the log file would look something like:
Date Time : Sub MyMacro1
Date Time : End Sub
Date Time : Sub MyMacro2
Date Time : End Sub
But it would be a in text just like a log file.
Is this possible with Excel ?
The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.
I have a macro that duplicates a excel file into how ever many files I need... so If I need 150 copies of the the file, it will make it 150 times.
However, each file the first tab called "Clients" has information that I want protected so users can't alter it. I can't protect it before I run the duplication because the data is added by a group of people and I need it unprotected until they add the information.
What I am looking for is a macro that I can click and run, allows me to select multiple files, then it will automatically Protect Sheet 1 "Clients" on all the files I select so I don't have to open all 150 files after data is entered and Protect sheet 1 manually.
I have twenty individual txt files. I want excel to open each, sort them according to certain parameteres, and then i want excel to populate all 20 of these into 1 workbook with 20different worksheets. Each worksheet tab to be called by either the same name as the txt file or a name established using a form. The twenty files can always be called the same name, and rarely will i will add a new file or will I remove a file. But sometimes, i may only want to run a couple files....
View 9 Replies View RelatedI have this macro to go to a specific folder and open up all of the files in the folder and merge them into a worksheet.
I want to change it so the user can select the files to be merged.
Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
[Code]...
how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.
I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).
What I'd like is:
*open Subject_01
*run script (some calculations, copy/pasting, my script for this is working fine)
*save file under a different name (i.e. Subject_01_processed.xlsx)
*close file
*again for Subject_02, Subject_03, etc.
Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.
I would like my macro to go to a certain folder- in this case N:Aexeo ClientsJabre2008Excel Diet Run, open each workbook therein ( to this end I have found the first part of the code below on another thread) and perform the ExcelDietMacro (also below). I am missing something though as nothing is happening, would anyone know from a quick glance what is wrong? Should I have these as 2 seperate Sub End Subs or combined into one? I know that the Excel Diet is correct thanks to the original designer and Rory on the forum for getting it adjusted to my needs.
Also each workbook within the folder will have the same password to open, is it possible to insert some code in the macro to do this automatically?
Sub LoopFiles()
Dim MyFileName, MyPath As String
Dim MyBook As Workbook
MyPath = "N:Aexeo ClientsJabre2008Excel Diet Run"
MyFileName = Dir(MyPath & "*.xls")
Do Until MyFileName = ""
Workbooks.Open MyPath & MyFileName
Set MyBook = ActiveWorkbook
Application.Run "ExcelDietMacro"
MyBook.Save
MyBook.Close
MyFileName = Dir
Loop
End Sub
Sub ExcelDietMacro()
'
' ExcelDietMacro Macro.........................
I have 400ish departmental budget files in a folder that I need to upload to a Sharepoint doc library. The files in the folder will have to exactly match the files we already have set up in sharepoint in order to upload them to the sharepoint site. As it stands now there is only one consistency between the sharepoint files and the budget files in the folder, which is the department number (which every file in both begins with).
I'm thinking some sort of macro will let me insert a sheet into each of the files in the budget folder, which would have a formula that pulls the department number from a certain sheet in the file, and produce the file name I want into a certain cell. Then I would like a macro that goes into each file and renames it based on that cell. I'd like to know if this is possible and if there may be a better way to do it.
I wanted to know If there a way to hide the process of executing macrob (vba)?
I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?
Range("A3:V3").Select
Selection.Cut
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("W2").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=-1
Rows("3:3").Select
Selection.Delete Shift:=xlUp
Excel 2010, Windows 7
I have a whole ton of *.dbf files (98 to be exact), that are from ArcGIS shapefiles. These date1_date2.dbf files have point names, and values, as shown below.
NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
I'm trying to figure out how to transpose each of the dbf files and save them as a text file (tab deliminated). My original plan involved saving them all as text files, then using a convoluted Unix script (I'm what you call a "dirty programmer") to transpose them and then combine them all into a single file. Since there are so many files (and I plan to do this in the future as well), I thought if I could at least automate the text file creation, I'd be ahead of the game. Even better would be a macro that will transpose the data for EACH file, then create a 99 (98 files + header row) row, 5 column text file. Creating the text files (1 text file for each dbf file) automatically .
All the *.dbf files will have exactly the same # rows/columns (2 columns, 6 rows)
This is what I've tried so far.
1)open one of the dbf files in Excel
2)record my marco (using the Personal Macro Workbook option - so it will be available in any workbook)
2a) transpose data (and paste it right below the original data so that the new data is in rows 7 and 8 of the same file)
2b) save as text file (tab deliminated)
2c) stop recording, and end up with this:
NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
NAMEPoint 1Point 2Point 3Point 4Point 5
RASTERVALU-9999.00000000-9999.00000000-9999.00000000-4.93072701-8.90071201
So now I want to apply the macro I just created to all open workbooks (after I bring in all the bdf files), but the macro isn't listed when I click the "view macros" button. All I want to do is apply the steps I recorded in the macro to all open workbooks.
I am currently attempting to use an excel macro to allow me to send a list of files to one person. I have managed to create a dynamic list where one sheet of the workbook has the list of files and checkboxes, and as certain ones are selected they appear in a list on the 'front sheet' of my workbook, which has the button to send the email.
My front sheet is set up in a way that in cell B3 I have the desired email address, B4 contains the subject and from B5 to B30 I have the list of selected attachments (although the length of the list obviously varies depending on the amount of files selected, when all are selected the list extends to B30).
I have the below vba code that allows me to create an email with the address and subject and attaches the file address from cell B5, but I would like to attach all the files in the list and am struggling to change the code to do so.
Code:
Sub CreateMail()
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngAttach As Range
Set objOutlook = CreateObject("Outlook.Application")
[Code] ........
I can add more by creating new ranges (rngAttach1, rngAttach2, rngAttach3 etc) and setting these to their respective locations but if, for example, only two files are selected, the list only covers cells B5 and B6, so when the macro looks up the file directory in B7 (rngAttach3) it returns an error because obviously there is nothing for it to find.
I have attempted to compile a loop and change the code to the version shown below but am having no luck.
Code:
Sub CreateMail()
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Variant
Dim rngAttach As String
Dim iLoop As Long
[Code] ....
I receive a daily bath of 6 files (for now lets calls them 1.csv, 2.csv 3.csv etc...)
I have to manually open these select all the data and then paste them into a single worksheet in a different xls file (called master.xls).
I am trying to figure out some vba that will open each .csv file, copy the data and append to the end of worksheet 1 in master.xls. Ideally i would also like it to paste the name of the .csv it has copied the data to in column A of master.xls
Also, the .csv files will not always contain data, occasionally some will be blank.
Both .csv and master.xls will be stored in the same folder.
As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....
I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?
Or even tell me which sub routine it is up to? (but % would probably be better)
And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...
I am trying to speed up this macro, ive already tried turning screenupdating and calculation off, but it still takes forever, and I dont understand why.
The code is basically searching for a given string in column E. There are about 9000 lines to my sheet, which shouldnt take more than a 30 seconds to a minute to complete.
Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual
For MY_ROWS = Range("E12000").End(xlUp).Row To 1 Step -1
If Range("E" & MY_ROWS).Value UserForm1.ComboBox1.Value Then
Range("A" & MY_ROWS & ":E" & MY_ROWS).Delete (xlUp)
End If
Next MY_ROWS
Also anyone know how to add another condition to make the macro stop the delete process at lets say Range("a1:e8")?