Loop Macro To Open/copy List Of Files
Oct 24, 2007
I am trying to write a macro to open a specific file (i.e site 1) and copy a range and paste that data into another workbook (Comparisons).
I can manage to write the macro to do this one by one, but ideally I would like to write some sort of loop macro that will go down a specific range of files (column A), listing the copy range (column B), and where to paste (column C) until it hits a blank row.
That way I do not have to keep re-programming my comparison spreadsheet every time a new site opens.
View 9 Replies
ADVERTISEMENT
Jan 8, 2010
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".
View 9 Replies
View Related
Oct 14, 2013
I've got a listbox (ListBox1) and it lists 5 files. names are One, Two, Three, Four, Five (all .xls) and they're saved in C:/MyFolder
What i want to do, when a user clicks a button, for a code to only open the files that are selected. So for example, the user selects Three.xls and Five.xls - the code would need to ignore the others and open the selected files.
View 2 Replies
View Related
Aug 29, 2006
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.
View 5 Replies
View Related
Jul 2, 2014
Say I have a list in column A
Image1.jpg
Image2.jpg
Image3.jpg
Image4.jpg
Image5.jpg
In VBA, how can I loop through the list and delete files from a certain folder. The list will vary in length from time to time.
View 9 Replies
View Related
May 4, 2011
I want to open files from an excel list.
Sub Openfile()
Dim wkbOne as Workbook
Set wkbOne = Application.Workbooks.Open(Filename:=Worksheets("Sheet1").Range("A1") & Worksheets("Sheet1").Range("B1")
'where "A1" is the path where the file located and "B1" is the file name.'
End Sub
When I run this it will work, but if I change to below it won't open all file in range, what's wrong with this?
Sub Openfile()
Dim wkbOne as Workbook
Set wkbOne = Application.Workbooks.Open(Filename:=Worksheets("Sheet1").Range("A1:A10") & Worksheets("Sheet1").Range("B1:B10")
End Sub
View 9 Replies
View Related
Jun 30, 2008
I've got a string named FileList that contains the full paths of each file I need to open. It doesn't work, but this is where I'm at for the moment -
For Each File In FileList
Workbooks.Open
Next File
What do I need to put after Workbooks.Open?
View 9 Replies
View Related
Jul 26, 2013
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....
View 2 Replies
View Related
Feb 13, 2009
This code is supposed to open a series of files through a list of hyperlinks, run a macro then save and close. But at the minute it says it cannot find the Macro and error's.
The macro is in each file named and is named 'RefreshStats', if I open a file seperately I can run the macro fine, but it can't find it through this looping code.
Another thing I found though, is that if I put a blank Module in the main workbook (that contains the list) and call this macro 'RefreshStats' the looping works fine but based on the blank macro. So this code seems to want to look in the workbook its in for the macro.
View 4 Replies
View Related
Jan 2, 2007
I want to know if its possible to write a macro that will prompt me to open a document, then once open it will copy data and paste it into the working spreadsheet. I have a mini macro that cuts and pastes data elsewhere in the sheet but at present I am manually opening a file then copying all and then pasteing the data which is rather slow.
View 9 Replies
View Related
Nov 11, 2009
I’m wondering if this is possible, if I have a folder with say 30 excel spreadsheets (.xls) all named differently (number of files will always change), can I easily write something in VBA to Open all the spreadsheets and copy each sheet over to an existing Excel spreadsheet? For example, have a ‘template’ spreadsheet where the VBA would exist, then have the first sheet, (Sheet1) of each 30 sheets be copied back over to the template.xls? Even better, could I rename each Sheet1 to the name of the file before copying it over? This would basically be the first step in my process of getting the spreadsheet made.
I found this code in a similar question, so how can I rename the Sheet to the opened file name, then copy that over to template.xls?
View 13 Replies
View Related
Sep 15, 2007
Simply trying to copy data from one worksheet to another. The source sheet is an excel file exported from an Access table. I recorded the macro using the recorder in Excel because I am no programmer, but when I try to run the code, I get this error:
"Code execution has been interrupted" ...and the Range I tried to select in the source file is highlighted by the Editor.
Is the source file protected somehow?
My ______________________________________________________________________________
Private Sub Workbook_Open()
ChDir "P:Databasesdownloads"
Workbooks.Open Filename:="P:Databasesdownloadsheadersflat.xls", Origin:= _
xlWindows
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("ExcelTool.xls").Activate
Range("A1").Select
ActiveSheet.Paste
View 9 Replies
View Related
Aug 4, 2009
I want to loop through all files in a directory and run a macro named "Main" in each file. The file that contain the loop-macro will stay in the same directory as the files I loop through.
View 4 Replies
View Related
Mar 22, 2008
macro to run as a loop on all files in a given folder but allow for the option to Browse for the folder I want this to run in?
Sub CleanUp()
Dim i#, rng As Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False
For i = Worksheets.Count To 1 Step -1
If Worksheets.Count = 1 Then GoTo e
Set rng = Intersect(Worksheets(i).[11:11,13:13,23:23,25:25], _
Worksheets(i).UsedRange)
With rng
.Replace What:=Chr(32), Replacement:="", LookAt:=xlPart
.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End With...........................
View 10 Replies
View Related
Mar 5, 2009
I am trying to process 60+ data files. I've recorded/written macros that do all of the processing, and now I would like to write a loop that will go through a folder with the data files (.txt, tab delimitted), and run the code on each file. I am using the Mac Version of Excel 2004. I have tried to adapt some code that I found in the forums, but after fixing a few errors, now nothing is happening when I run the code. The code is below. I have it in a module in the VBA editor.
length of the code; I'm assuming the problem is in the very beginning or end (the code I added for the loop), as the code in the middle that does the actual processing works.
Sub ProcessData()
Dim strDocPath As String
Dim strCurrentFile As String
strDocPath = "Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:"
strCurrentFile = Dir(strDocPath & MacID("TEXT"))
View 9 Replies
View Related
Oct 18, 2008
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
View 5 Replies
View Related
Apr 30, 2007
If i have a folder with multiple csv files in it, all with different names can i get a macro to open all these files.
I know that i can if they are always the same name each time i run the macro but what i would like to do is to change the name of the csv files daily.
The reason is because i could have upto 50 different files a day.
So basically i'd like to click a button and open all the contents of the folder regardless of the name of the files inside.
View 9 Replies
View Related
Jul 28, 2007
How can I code a Macro in Excel to search in a preset directory and pull out, aka open/run a certain Adobe Acrobat .pdf file automatically? Is this even possible to code in Excel Macro or is it out of its scope of what Excel VB code can do?
Right now I have a very repetitive task where depending on the value of a certain field in Excel I have to search for the right/corresponding .pdf file and open it up do work in the .pdf as well as the Excel worksheet... I have to do like hundreds of this. So is there a quick way to code in Excel that a certain value will open a certain file named .pdf acrobat file? Can other programs/files be called upon and ran/started/opened from within Excel Macro?
View 2 Replies
View Related
Feb 15, 2008
I have a requirement to create a master spreadsheet using a macro to copy set cells from several other spreadsheets and paste them into the master one in a list for accounting purposes.
So far the macro reads in the spreadsheet filename(s) and copy/pastes the cells to the master one - I need to be able to paste the values onto subsequent rows for each input sheet which is where my problems lies. Currently the row just pasted in is overwritten over by the following one, I need to go to the next row down but am stuck on how to do it.
Sub EmployeeExp()
'
' EmployeeExp Macro
' Macro recorded 12/02/2008 by eugene.cross
'
Dim i As Integer
Dim f As String
I've attached an example master output sheet to show what I want to produce, albeit this has only one row!
View 7 Replies
View Related
Jul 19, 2012
Please look at the code below...need this to open and run FIXId macro, save and close then loop through all files in the specified folder.
Code:
Sub CorrectID()
Dim Wb As Workbook, sFile As String, sPath As String
Dim itm As Variant
Dim strFileNames As String
sPath = "C:UsersXXXDocumentsFlash Repots2012"
sFile = Dir("C:UsersXXXDocumentsFlash Repots2012" & "*.xlsx")
[Code] ....
View 7 Replies
View Related
Oct 16, 2006
Here is what I'm doing:
-Open .xls file
-Run a macro (it works correctly when run by itself but I'm trying to speed things up)
-Save
-Close
-Open next file, and so on (I'll do this on no more than 200 files at a time all in the same folder)
Here is the code I am using: ....
View 9 Replies
View Related
Oct 19, 2007
I use this same macro on 18 excel spreadsheets. The spread sheet retrieves in data then massages the data then saves the spread sheet depending on cell values.This macro runs on file open. When a file is run one at a time the macro runs perfect.
This is the problem: I created a macro to open all 18 files one a time ,this macro Opens the file then the Macro (listed below) runs then closed the file. When this happens the macro will only save every other file. And I get a Text box message do I want to save this file. I have changed order of when the files run which has no effect.
Sub Auto_Open()
Sheets("Raw data from qmf").Select
If ActiveSheet.Range("W1").Text = "Done" Then Exit Sub
Application.Goto Reference:="R2C1"
Selection.QueryTable.Refresh BackgroundQuery:=False
'If no data Save and Exit sub
If ActiveSheet.Range("A2") = "" Then Application.Run "BuildPath"
If ActiveSheet.Range("A2") = "" Then Exit Sub
ActiveSheet.QueryTables(1).Delete
Sheets("Sub Total by Ticket").Select
Application.Goto Reference:="R2C1"....................
View 2 Replies
View Related
Feb 6, 2014
I'm looking to create a macro that does the following:
- Loops through a folder structure
- Opens a specific subfolder within each folder based on name (i.e. "*Financials*")
- Opens a specific file within that subfolder "*Financials*" based on both most recently modified a naming criteria (i.e. "*Model*")
- Performs an action on that file (a macro created elsewhere that I assume I can call here)
- Closes that file, and moves on to the next file matching the criteria above
As a self-diagnosed VBA novice, I'm having trouble adapting code found in various forums for my specific project.
I've found examples that address aspects of my desired macro or do similar things, but I can't figure out how to put them together.
Open most recent file:
[Code] .....
Loop through folders and subfolders and print all files:
[Code] ....
View 1 Replies
View Related
Aug 12, 2014
I have several workbooks in folder C:Parts &Service where the data needs to be updated with downloaded files in C:extract
I have a macro that allows me to open up a workbook in c:Parts & SVC Sales. One opened I then need to select the appropriate file in C:extract
VB:
Sub Update_Workbooks()
ChDir ("C:Parts & SVC Sales")
Application.DisplayAlerts = False
[Code].....
The are two types of workbooks in C:Parts & SVC Sales "Parts Sales" and "service Sales" . The branch name is at the beginning of the file name
Instead of opening up each file individually in C:Parts & SVC Sales and selecting the appropriate csv file in C:/extract using the Update_Macro, I would like the macro to open up all the files in the directory C:Parts & SVC Sales and update each of these with the appropriate csv file in C:extract
The name and description type must match the csv files and then updated eg Br1 Parts Sales to be updated with csv file Br1 Salesperson 01-07-2014 (the date in the file is not important for match the parts file -the branch name for eg BR1 and "salesperson is) i.e Br1 Parts Sales must select BR1 Salesperson 01-07-2014
BR1 Service Sales Must be updated with Br1 Service order repair register.csv (branch name for eg Br1 in this instance name and Service order repair register (pertain to Service Sales) is important)
Br2 Parts Sales to be updated with Br2 Salesperson
Br2 Service Sales must be updated with Br2 Service order repair register etc
[URL]
View 1 Replies
View Related
May 16, 2014
I have a folder which has a set of files (Files may be anything like excel,pdf,word..etc).My rrequirement is: When i run the macro the first file to last file should open and wait for 5 sec and then close..I mean first time the first file should open and wait for 5 sec and close..Next 2 nd files shoul open and close after 5 sec..next third files should open and close after 5 sec...Like wise till last file..Is this possible to achive through macro code..
View 7 Replies
View Related
May 5, 2009
I have the following macro that is linked to a button.
There is also a function that is called to check to see if the file is already open.
These macros & functions work properly.
Basically, the Case statements check the extension of the file to be opened and then calls the correct macro to open that particular file.
I have shown the macro that is called by the Case statement that has the extension .xls . . . Excel
I want to keep these Case statements as I always want Excel files opened in new instances of Excel.
What I need is when the existing case statements do not find an extension that is listed, I want code added to this macro that can open any type of file, such as files with other types of extensions or without extensions.
My son suggested that I should try to get a macro that could open the file using the RUN command found under the Start Button. Sounds good to me.
View 9 Replies
View Related
May 12, 2014
I'm trying to set up a macro so that: in column C I will see the current (old) links being used by the workbookin column D I will input the new links I want to use in the workbookthe macro will open all those files in column Dthe macro will then replace the current (old) links with those listed in column D
I have the following so far, but something doesn't seem to be working - it gets as far as opening the first new file but doesn't open any others and doesn't change any links (although I've tested the part to change links separately and this seemed to work ok).
Sub linked_sheets()
Dim LinkedBooks As Variant
Dim i As Long
LinkedBooks = ThisWorkbook.LinkSources()
For i = LBound(LinkedBooks) To UBound(LinkedBooks)
Cells(i + 3, 3) = LinkedBooks(i)
[Code] ..........
View 1 Replies
View Related
May 22, 2006
i've got the code below, and if you notice the directory and file line, I want a loop that'll do what the macro below does, but also for files j1k2-j1k200. I'm hoping there's a macro that'll be able to do this, as I don't want to have to copy, paste and edit the same macro below 200 times!
Application. ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:Uni StuffStocks_J20j1ka.xls", False, False)
Sheets("Sheet1").Select
Range("B6:S6").Select
Selection.Replace What:="]w1", Replacement:="]w2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
wb.Close True ' close the source workbook saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
View 5 Replies
View Related
May 6, 2013
I am trying to create a macro that will open certain files based on the contents of a cell. There are three possible files that I will want to open.
File1.xlsx
File2.xlsx
File3.xlsx
If the contents of cell Q2 = 10, 20, 30, 40 or 50 then open "File1.xlsx"
If the contents of cell Q2 = 60, 70 or 80 then open "File2.xlsx"
If the contents od cell Q2 = 90, 100, or 110 then open "File3.xlsx"
If the contents of Q2 do not equal any of the possibilities listed then I would like an error box to show with the option to end or debug the code.
View 3 Replies
View Related
Jul 11, 2014
i was wonder is there was any way I could go to the following website:
[URL]
and copy paste it into an excel sheet. What i would need is to get this page and the previous 7 days by changing the date "20140711" to "20140710" and so on. Each orevious day needs to be copy pasted to a different sheet.
As a new day occurs, I would like to delete the page 7 days ago and add the new day on a new sheet.
View 9 Replies
View Related
