Loop Through Directories & Files With Dir Function

Jan 18, 2008

I am trying to do is loop through a series of directories under a target directory. When a directory is found, it looks within that directory and finds any .xls files, loops through those and copies/pastes them to the new target directory. I have managed to get it to work only on the first iteration of the loop through the directories, then it crashes. The error code is "Invalid Procedure Call or Argument." Here's the

If valFilePath = True Then
MsgBox (msg)
Else
'Search for directories within source directory
strDir = Dir(ebsSource & "", vbDirectory)
'If a file has been found
Do While strDir <> ""
If strDir <> "." And strDir <> ".." Then
strFile = Dir(ebsSource & "" & strDir & "" & "*.xls")
Do While strFile <> ""
'Copy .xls files and paste in destination
copySource = ebsSource & "" & strDir & "" & strFile
copyDestination = ebsDestination & "" & strDir & "" & strFile
FileCopy copySource, copyDestination
strFile = Dir
Loop
End If
'Crashes here after looping through the first iteration of Excel files
strDir = Dir
Loop

View 2 Replies


ADVERTISEMENT

Move Files Into Directories Using VBA?

Mar 2, 2009

In my Excel file, I have three columns: A, B and C. Column A holds the folder names. column B holds the files hash names. And in column C, i keep the files real names, i.e:

A1.value = Contract folder

B1.value = 23ffryu567894lkgj090

C1.value = picture1.jpeg

On my hard drive, I have all the folders (column A data) already created inside a folder called directories: E:directories... There is another directory on my drive in which I keep all the hash files: E:infiles

And all I would like to do now, is to write a piece of code to read all three columns row by row and based on the data in that row, moves the hashed file into their proper directories and rename them to their real name. In the example above, the program should read the data found in the first row: A1, B1 and C1 and move the hash file named 23ffryu567894lkgj090 to E:directoriesContract folder and rename the hash file to picture1.jpeg

View 14 Replies View Related

Sort Files Into Desired Directories

Feb 13, 2010

search an entire drive from the root directory and down into all subfolders and copy all files matching various file types below to another drive into organized folders named for the file type. (for an example any .xls files in F: copy to G:xls .xls files) and also create an index in one excel file showing all the files in alphabetic order with its original path.

bmp
txt
xls

View 14 Replies View Related

List Files & Directories In Worksheet

Mar 20, 2008

I found this nifty program on[url]but there is a problem with it and it's down to the types of files I'm trying to list on my PC.

The excel code below lets you select a starting directory and it will then produce a list of files in a sheet. However when it comes across an internet shortcut file it gets rather confused and won't display the name of the shortcut but what the shortcut stands for. This causes an issue when I try and get some details about the file (eg date, size etc.) as a 'permission denied' error can then occur if it links to a file that is currently in use. For some reason I can't attach an example of a shortcut / internet shortcut so please feel free to make one at your end. eg. I have a shortcut called 'ImageJ' which links to a web address [url]. The code below then reports this address instead of the file name 'ImageJ'. Can any one suggest how to amend this coding to handle this issue?

Option Explicit
'Requires a reference to:
' Microsoft Shell Controls and Automation (shell32.dll)

'Uses techniques found here:
[url]

Public objShell As IShellDispatch4

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

View 5 Replies View Related

Open Multipile .xls Files In Multipile Directories

Mar 31, 2009

What I have is four folders that are labeled 2006, 2007, 2008, 2009. In each of these folders are numerous .xls files. "2006.BS.03.xls" is a name of one of the files in the 2006 folder. All the files have the same nameing structure.

When somebody created the template for these files, they inserted a bitmap image instead of a jpg. So the file size is over 4mb. I have changed the image in the template from a bitmap to a jpg and the file size is now 197kb. That is a big change.

So what I am wanting to do replace the bitmap with a jpg in all the .xls, that have a certain naming structure, that are in the four folders.

Attached is the workbook that I have planned to have open and click a command button to do all the work.
I have got this to work on one file, but can't figure out how to get it to do multipile.

View 7 Replies View Related

Loop Through Multiple Files And Call Macros (but Unable To Loop)

May 14, 2014

Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.

Why it stops after one file when using "Application.Run..." to call the macros?

NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.

Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long

[Code]....

View 4 Replies View Related

Loop Through Files In A Folder

Mar 11, 2014

I would like to create a Macro which does the following task. I have a Workbook Master.xlsx with a worksheet "source". The path of Master.xlsx is C:Test

In the Directory C:Testprojects i have about 50 files which all contain the String "Forecast" in their filename. These Forecast Workbooks need an update in the Worksheet "actuals"

So, i need a Macro that copies WS "source" from WB "Master" to WS actual in all WB's Forecast.

View 5 Replies View Related

Loop Through Files And A Run A Macro

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

Loop Through Files On Share

May 30, 2008

I have the following code, and it is working well for one file. I'm trying to figure out how to do a loop to go through each region folder and update, save, and close each file just like the code below is doing for one file.

There are 10 region folders
Each region folder contains 8 or fewer files.

Please see the sample code below for the sample file location.

Sub TrendByRegion()

Application.EnableCancelKey = xlDisabled

Set wbOpen = Workbooks.Open(Filename:= _
"S:High Level ShareReportsReports by RegionPivot Table Files
Region 1Catalog File.xls")

wbOpen.RefreshAll

ActiveWorkbook.Save

ActiveWorkbook.Close

End Sub

View 9 Replies View Related

Using Sendkeys And Loop To Download Files

Jul 7, 2012

I have created some code that directs me to a web page, and i use send keys to navigate my way to the input field. In this input field I am trying to put a cell value in a range use send keys to download the file, and the repeat the process for the next cell.

Here is my code so far, it does everything right up until pasting the copied cell into the input section of the web page - the issue is it is pasting nothing

VB:
Shell "C:Program FilesMozilla Firefoxfirefox.exe " & "https://website.com", vbMaximizedFocus
Application.Wait Now + TimeValue("00:00:05")
SendKeys "{TAB}"
' Input username

[Code] .....

View 9 Replies View Related

Loop Throough All Files In Folder

Apr 28, 2009

I did search but I couldn't find what I want. I have a load of .csv files in a folder. I want to loop through all files, run the macro below and close the .csv file without saving. My macro copied data onto another workbook and saves, but I don't know how to loop through all files within a folder.

I need some code that will open each file in the folder, run the macro below and close it again. I don't know how to put code tags in so I have put the code in a .txt.

View 3 Replies View Related

Macro Run As A Loop On All Files In A Given Folder

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

Loop Through Files, Using Filename As String

May 8, 2008

I have some code that I use to convert files, and it works well, but I would like to make a couple of changes to it. If I run it now it requires user input for every file that is processed, using the input box. Instead of this I would like to use the filename minus the extension as this input.

The next step is selecting the file using the . Instead of this I would like the user to browse for the folder containing .csv files, and select the folder, then use the macro to loop through all files in the folder.

View 9 Replies View Related

Loop Through Files In Dated Folders

Nov 30, 2008

each folder is named by date, for example a folder name may be 04-01-2008. Within each folder there is one xls file named daily.xls.
(this never changes)
In date order from the folder name, I need to open each daily.xls run my code, then close daily.xls and then open the next one etc until there are no more.

example path is C:4-01-2008daily.xls

View 9 Replies View Related

Loop Through Folder, Run Macro On All Files

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

Loop Through All Files In Subdirectories Of A Directory

Feb 26, 2010

I am having a bit of trouble with a bit of code. I have a bunch of code that I want to run on all excel files that live one level down from my main directory.

I have a general directory (orders) which contains a variable amount of subfolders each month (in picture below, 'Dan', 'Frank', 'Steve'), and I would like to look through all the excel files in 'Dan', 'Frank', 'Steve', but not in the main directory (orders), or any subdirectories within 'Dan', 'Frank', 'Steve'.

I am able to loop through all the excel files in the subdirectories individually if I hardcode the directory names, but as they are variable,

View 9 Replies View Related

CreateTextFile In Loop Not Creating Files

Dec 7, 2006

I have a problem with the code below. When i place the statement "MsgBox ("Done at " & Tester_array(N))", i am able to get the files i need. However, when i remove this statement, no files can be retrieved. Why is that so? Can somebody advise ?

Option Explicit
Sub File_Transfer()
Dim N As Integer
Dim fs As FileSystemObject
Dim A As TextStream
Dim Lotid As Variant
Dim Testerid As Variant
Dim dRetVal As Variant
Dim Tester_array(1 To 15) As String
Tester_array(1) = "aaa"
Tester_array(2) = "bbb"
Tester_array(3) = "ccc"
Tester_array(4) = "ddd".......................

View 2 Replies View Related

Loop Through All Files Rename And Save In Another Folder

Feb 27, 2013

I have excel files in a folder, I need a macro to loop through all files, rename them and save them in another folder. the file's name is available in a cell in each file, I am able to get the file name as text (I had to dig a little to get the text as it was between ""). Well I am stuck right now because the macro is doing what it's supposed to do for one excel file only and does not continue with the rest of the available files for a reason.

VB:
Sub RenameAllFiles()
Dim Bk As Variant
Dim n As Integer
Dim NewName As String

[Code] .....

View 8 Replies View Related

Excel 2011 :: Loop Through All CSV Files In A Folder

Nov 23, 2011

I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...

Code:
Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string

[Code]....

For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.

Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?

View 6 Replies View Related

Loop To Open Selected Files From Listbox

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

Loop Through List And Delete Files From Certain Folder

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

Loop Through Files, Match & Return Data

Oct 26, 2006

I found a use for the “Looping Through Workbooks” code (recommended by Dave in my last thread - thanks Dave).

I have a number of small files in "C:Files" that I’d like to loop through and find a match with data from a Master (in another folder) workbook as follows.

1. Match columns ”C” of each of the files with column ”H” of the Master.
2. For each matching cells, insert data from column “I” of the Master (“ Date” field) to into col “D” for each row of the individual files (about 300 rows)
3. Save & Close the workbook and loop to the next one in that folder

All that's needed here is code to search, match & paste, to insert into Dave's "Looping Through Files" code.

View 9 Replies View Related

Loop Through Multiple Files To Create List Of Tab Names

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

VBA Macro To Loop Through Folders And Select Files With Certain Criteria

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

Loop Through Set Of Files And Check If File Doesn't Exist?

Feb 11, 2014

My problem is that I want to loop through a directory that contains csv files. The directory is dynamic so everytime could be a different number of files inside. I want the loop to go through each file and check if that file exists. If it doesn't to print a message that this specific files doesn't exist. Until now I got the following code:

[Code] .........

I guess I should somehow place the counter j inside the Dir path in order to check if everytime time the file[j] exists?

View 6 Replies View Related

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 View Related

Convert Horizontal Data To Vertical - Loop Through Files In Folder

Apr 9, 2014

I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"

i.e. file 1 - sheet1 looks more or less like this:

1 2 3
4 5 6

same with the other files.

what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector

i.e

1
2
3
4
5
6 from file 1 and then
1
2
3
4
5
6 from file 2

I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:

<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
1
2
3
4
5
6

This is the code I have so far:

Sub Consolidate()

Dim a As Workbook
Dim b As Workbook
Dim x, y(), i&, j&, k&
Dim myPath As String
Dim filename As String
Dim wb As Workbook

[Code] .........

View 11 Replies View Related

Loop To Import Text Files One By One Until Cancel Selected By User

Jan 30, 2014

I have a macro that works perfectly to import a text file and parse it. Now i need it to do multiple ones. I import the txt file to a new workbook as i filter for certain data only and if found i copy that to the current workbook. I want to do the same just for many txt files:

[Code] .......

View 4 Replies View Related

VBA - Attach Multiple Files To Email Using Folder Path Loop?

Feb 7, 2014

I have two pieces of code that each work, but I am struggling to combine the two.

I started with Ron de Bruin's code to attach multiple files to an email and then found more code that will loop through a folder to attach multiple files to an email.

Essentially, I would like the structure of Ron de Bruin's code, with the ability to have folder paths in the cell range, rather than file names.

Here is the code that works to loop through a folder, but it only works when I have the paths listed in one column, not in a range (e.g. columns C-Z)

Code:
Sub Send_Indv_Files()
Dim OutApp As Object
Dim OutMail As Object

[Code].....

View 4 Replies View Related

Nested Loop Not Working; Pulls Data From Multiple Files Into One Table

Aug 8, 2006

I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.

Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved