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.
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.
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.
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...........................
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"))
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] .....
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?
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] .........
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].....
I m trying to delete all files in a specific folder?
View 5 Replies View Relatedi have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies View Relatedi would like to be able to move files from one folder to another then delete the original files.
I dont think im passsing the variables to the functions corectly.
Is there a code that will search a pre-determined folder for all .xls files containing the word "Temp", and deleting those files?
Example:
In the root of drive M, I have several Excel files. Anytime one of the original tracking logs is opened, a temp file of that log is automatically created. I would like to automatically search the drive and delete all files containin the word "Temp".
I've attached a picture of the directory tree, in case that will help. The file will always be named "PO Response Tracking - Temp#######.xls", with ###### representing a series of either 5 or 6 numbers.
Is there a way list load all the files names in a folder in to a listbox in a userform
View 2 Replies View RelatedI have a folder C:My Documents that contains excel & Pdf files. Is it possible to list all the files by name contained in this folder in a excel spreadsheet i.e one filename one cell?
View 4 Replies View RelatedI want to run a macro to list the names of all files in a folder in a worksheet. I want the file names along with extensions.
View 4 Replies View RelatedI have a problem with getting the list of files in subfolders. this code is writing the names of files in a folder to column "A". But it is just "target" folder. But i need to get the name of all files not just under the target folder but also subfolders of target and even subfolders of subfolders of target
VB:
Dim FSO As Object
Dim SourceFolder As Object
Dim FileItem As Object [code]....
Here is a macro I have found and manipulated for my particular case. The problem is that I want to narrow the search criteria by just pdfs and only those starting with Segment which i know in coding would be something like "Segment" & "*" & ".pfd" or something to those terms but the code uses objects which I'm not too familiar with.
Code:
Sub ListAllFile()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ws = Sheets("Simple Cut Lists (2)")
[Code] ........
How do i set objFile to just pick up my criteria?
I have a folder containing many Excel files. Each is unique, and I wish to characterise them in a separate contents file in Excel. To do so, it would be very useful to populate a worksheet with the names of the files in the folder.
View 4 Replies View RelatedWe use Excel 2003 and 2007 to track labor data for a field service company. The field tech's complete daily forms and the office staff copy/paste this data into weekly billing worksheets. They use naming conventions to show the jobs and dates in the file name (Daily Labor - Job1 - 031308.xls and Weekly Labor - week ending 3-16.xls. What I would like to do is link the daily data to the weekly forms as they come in. Is there some way to do this when the job names and the dates are always different? Can I tell Excel to expect data files based on the naming conventions as in the Weekly is named "Weekly Labor - Week Ending 3-16.xls so data files named LaborJob1310.csv, laborjob1311.csv would automatically update the weekly sheet? I can put these in a folder and link them, but I don't know how to provide an easy way to do this for the upcoming week.
View 4 Replies View RelatedI have a question about creating a custom drop down menu. In AutoCAD, I can create custom drop down menus that loads each time AutoCAD loads and i was wondering if excel have something similar. This menu would be added to the default menu list: "File_Edit_View_Insert_Format_Tools_Data...etc". I have searched online but i keep getting things about how to make drop down menus in cells but nothing about adding a main drop down menu.
What I want to do is create a menu with hyper links to all of the excel files we use quite often here at work.
I have already done this by just creating a sheet with a list of hyper links on it but would like to put it in a drop down menu instead if its possible.
I have the code below which I need to change so it lists the file names down the column on the active worksheet starting at Range("A110").
At the moment it insterts a new worksheet and list the file names starting at A2, also I do not need "The files found in " & objFolder.Name & "are:"
what the code under 'clean up' is doing.
VB:
Sub ListAllFile()
Dim objFSO As Object
Dim objFolder As Object
[Code].....
The following code is something that was developed to find files with certain criteria that matches references in cells A1 & A2. The coding is as follows:
Code:
Option Explicit
Option Compare Text 'for Case-Sensitive matching change Text to Binary
Sub List_Matches()
Dim sPattern As String, sPath As String, sJob As String
Dim sMainDir As String, sCommonSub As String
Dim c As Range, lRow As Long
[Code] .......
The red is the area of the code i am having problems with. The original code was going to look only in one directory, now I wanted to add a second directory with reference located in E5. Now as is the program works fine looking at directory 1 or E4, but the problem i feel is that for E5 all the folder name are named "WO#_____ - ______". I believe the # sign is not allowing it to open that folder and search for the info. In the code there is a function for solving the # sign problem in a file name, but how could i either modify the code or the function to do the same for the folder name and view the # sign as a valid file name to open?
Suppose i have a directory on my HD with 100 images. Now i want to show them all in one column (each in 1 cell),
View 3 Replies View RelatedI tried to write some VBA code to loop through all the files in a folder and return the name of the file. (In my current example, all the files are excel workbooks)
Here is the code I have used:
Sub Load_List()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "G:CFOMiddle OfficeDannyFine Tunning for JP"
.FileType = msoFileTypeExcelWorkbooks
However, when I tried to run the code, it give me the error message saying: the defined type of the variable are not been defined. Seems to me, that VBA dont have the variable type as Folder, or File.
creating a macro, I have a list of CSV files in a folder called balances, i need a macro that will look into each CSV file and grab all p'folios which have a negative balance in Col D and line them up as per example shown below. The extracted data needs to be copied to file named "Workings" S:RecsalancesWorkings
All data in CSV file looks like this, where header row is row 1 starting at A1
CSV data
CSV filenames all start with ddmm so e.g. 0111
End DatePfolioA/c CodeA/c Native at End1/11/20081GRVMAAUDCUST 1/11/2008AFSBGEAUDCUST 1/11/2008AFSMBEAUDCUST 1/11/2008AFSPEEAUDCUST 1/11/2008AFSTRNAUDCUST 1/11/2008AMPFSVAUDCUST 16,565,587.811/11/2008GIORIQAUDCUST 155,642.251/11/2008GIORISAUDCUST 1/11/2008PINESIAUDCUST 5,654,632.861/11/2008WACCGIAUDCUST 7,457.561/11/2008WCTARIAUDCUST -3,923.721/11/2008WCTARWAUDCUST 1/11/2008WCTASIAUDCUST -73,456.201/11/2008WCTBGFAUDCUST 1,662.651/11/2008WCTDAIAUDCUST 1/11/2008WCTFUNAUDCUST 27,665,698.291/11/2008WCTGMIAUDCUST 31.251/11/2008WCTROWAUDCUST 6.961/11/2008WCTTHSAUDCUST 22,030.01
Macro to line up data as per below
P'folioDateWCTARIWCTASIWSTFDGWTDTADWTFFI3WTGTFAWTRLGD1/11/2008-3,923.72-73,456.20-4.18-136,496.19-562,967.29-2,319.40-15.762/11/2008-3,923.72-73,456.20-4.18-136,496.19-562,967.29
I am unable to install a disk catalog and have been trying with no success to try and get something in excel that will look at a directory and display the contents of that folder with the file attributes etc
View 2 Replies View RelatedSorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
View 2 Replies View RelatedEssentially, the Engineering Dept has given me 550 AutoCad files I have dumped into a folder located in this path: C:/DrawingsDump
From these 550 DWG files I need to manually filter and separate 260 drawings that I actually need to use and move them to a folder located in this path: C:/DrawingsFiltered . This manual filtering process takes a lot of time and is tedious work and it has got to be done everytime Engineering changes drawings because they are not kind enough to provide me a delta list.
In Excel 2003 I have setup a list of all 260 drawings I am interested in and that I call my filtered list. Is it possible to create a script that will enable Excel 2003 to compare my filtered list to all of the files inside C:/DrawingsDump, and then copy only those files that match to my Excel Filtered list and then paste only matching files into the C:/DrawingsFiltered folder?
If Excel 2003 can't handle this, is there a third party application that will let me perform a file management function like this with minimum startup time?
I was wondering if anyone had any code to loop through every file in a folder and list the file name along with every sheet name in that file? I'm using Excel 2007.
View 4 Replies View Related