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
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
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
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
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.
In every month I have to send report to each sales person provided one sales person should not get info of another sales person. Now I am doing it by making file after sort using copy and paste which is time consuming. Is there any way that I can make individual file for each sales person in shortcut way.
View 1 Replies View RelatedI'm trying to sort all .xls files in a folder by modified date. I want most recent at top because then I will be cycling through and using the most recent version of files that have similar names.
Excel 2007.
The code I have so far is this:
Code:
Sub autolink()
Dim folderPath As String
Dim FileName As String
Dim wb As Workbook
[Code]....
But I need something before it sort the folder by the modified (or better creation date) because there are several files with similar names. I know know, I could put a date in the file name and look for that but I don't control the naming of the files.
I need to use VBA to test if a certain directory exists and if not ten create it.
I do this with:
[CODE]Sub MakeDir()
Dim DirName As String
Dim FileName As String
Dim fnameshort As String
FileName = "C:TestA1ATest.xls"
fnameshort = Right(FileName, Len(FileName) - InStrRev(FileName, ""))
DirName = Left(FileName, Len(FileName) - (Len(FileName) - InStrRev(FileName, "")))
If Dir(DirName) = "" Then
MsgBox "Does not exist"
'Dir to be created using MkDIr
End if
End Sub/CODE]
My problem is that I need the script to loop back until it finds a folder that exists, and then make the respective subfolders.
Example:
C:TestA1A should be created, but currently only C:TestA exists.
VBA should then test for the full path, and when it finds out, that it does not exist, it should strip off one folder, test for that and so forth.
When it finds an existing folder, it should then make the respective subfolder, and then the subfolder to that...
I am trying to automate some of the work I used to do as I have moved to another position, specifically trying to automate file management. I am using this code to create new files for each client
Code:
Public Sub CheckDir(fold1 As String)
If Dir(fold1, vbDirectory) = "" Then
MkDir fold1
Else
End If
End Sub
which works great, but you still have to do one client at a time. Basically I'm using this code to create new client files, but each year we will need to add a directory and sub directories for every client in a certain directory. What I would like to do is code a macro that would add say a "2013" folder with all appropriate sub directories to every client folder within the folder that contains all of the client folders The folder hierarchy should look like this
Code:
'F:Clients
'F:ClientsClientName
'F:ClientsClientName2013
'F:ClientsClientName2013RandomOtherFolders
I assume it would use a For/Each, but I'm not sure how to go about it.
I have a folder structure like ex this c:my folders. Under this folder i have several folders and subfolders. I would like to use a macro to run through all this folders an sub folders and change the name of the first letters of all folders.
Ex
LOP-100-APPL
LOP-200-ORG
I would like to change LOP to PLP instead. I'm lazy and looking for a method for not doing this job manually. This should be done from the excel workbook.
I am trying to access and copy some data from the excel file which is located in different folder each time I run the new macro. The file got the same name but only the folder name is different each time.
e.g File is called "testfile", but I have a list of subfolders something like that
date_time (folder name)
15-02-2008_2122 (folder name)
15-03-2008_2189 (folder name)
Each of the above subfolders contain "testfile" with new data and they all are located in the same drive and same mainfolder. Note my Macro file is located in a different drive.
I want when I run the macro all I have to do is to enter Date and macro will automatically pick up the right folder and the right file ("testfile") inside that folder and do all the required task.
I would like to create a list of folder/directories in the current directory. The only code I've been able to find lists all files in the current directory (not folders).
View 3 Replies View RelatedIs there any way to reference a directory that is up one level from the ActiveWorkbook.Path?
I am trying to accomplish three goals:
1. Maintain a Master Database in a root directory and pull data from a subdirectory - EASY:
VB : FilePath = ActiveWorkbook.Path & "sub-DirectoryName"
2. Maintain a Minor Database located in a sub-Directory, Named by Month, and pull data from the Master Database located in the Root Directory (up one level). Which I cannot find the right syntax for - ".." does not work.
3. Maintain a Minor Database in a sub-Directory, Named by Month, and pull data from a different Minor Database in a Different sub-Directory (e.g. up one level to root directory, then down one level to "January"). Which I also cannot find the right syntax for.
The Databases are going to be moved around A LOT, so I have to use relative paths.
Create sub directories from Cell Values
I Need a VB macro to Create sub directories unless sub directory already exist. My file will be Save 5 Level deep. Each Sub Directory will be cell value in a Worksheet .
Example C:CategoryYearSeriesMonthBimonthlyFile Name.xls
I will be using this in over 100 different files so I want to be Generic as possible
I have a CSV file and would like to create directories on my hard drive and the names of those directories will need to be pulled out from this csv file: [B1:B86].
For example I have the following data in my csv file:
B1: folder1
B2: folder2
.
.
.
And would like to create folders on my hard drive so that folder names will be: folder1, folder2, ...
I have a fluid document that I have to save for historical reasons in 2 separate directories. My save Macro works I was just wondering if anyone see's a better way of accomplishing saving to two separate directories then closing the document. I used the record function to gernerate most of the code.
Sub SAVEANDEXITTHESLATE()
Dim date1
Dim date2
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application.ScreenUpdating = False
date1 = Now()
date2 = Format(date1, "mmm d yyyy hh mm")
ActiveWorkbook.SAVE
Application.DisplayAlerts = False
ChDir "c:Documents and SettingsmeDesktop"
ActiveWorkbook.SaveAs Filename:= _...................
I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.
I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.
Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.
Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!.......
Is there an easy way to populate a combobox with a list of directories on the hard drive?
I want to create a way for a user to specify a file without having to type in the full address by hand.
I would like to start in the c:/ root and list the directories in one combobox and the excel files in another combobox. Then if they select say "desktop" it displayes the directories in "c:/desktop" and the excel files contained there in. Then if they click test it does directories in "c:/desktop/test" and so on.
I have a master workbook that has been set up to mirror the structure of a single worksheet in various other workbooks saved in different directory locations. I need some VBA code to retreive specific data from a specified worksheet in multiple workbooks which are saved in different directories and then copy the data to the master workbook, listing each data set one after another. I do not want to open any of the source workbooks to acheive this.
I attach two example workbooks to better explain:
The code has to look in various sub directories to find the relevant workbooks, (Source1) then find the specified worksheet, (Stock) and copy only rows that have data from column B to O. The data needs to be copied to the master workbook, (master) from all the source workbooks as a list with no space.
How in excel do I get a no. to appear in a column dependant on a reference in a different cell. For example If i type March in a cell I want the number 1 to appear in the March column of a table.?
View 9 Replies View RelatedNot sure what formula to use for this set-up:
Col1 - Name_Last
Col2 - Name_First
Col3 - Ref_Name
The desired effect is under column 3, you have "Name_First Name_Last". Attached sample file.
I have two sections to this spreadsheet: 1) the entity section; 2) the order section.
I have created a macro to insert rows in both these sections. The macro appears to work fine for the first section but not for the second section. The problem I cannot solve is in respect of cells D9:D10. The macro is as follows:
I'm using Excel 2007 and s/s is 325501 rows deep. It consists of series of ranges between 4 and 30 rows deep.
What I want to do is locate the next appearance of a name and copy its accompanying number.
Doing this manully is not feasible, given the large size of the s/s .
I enclose a small attachment showing what I am trying to achieve. For those who don't like opening attachments the wording in it is :
The desired objective is to place in column Q the next appearing number in column L of the name in column C.
The VLOOKUP formula in column Q presents the desired number but (problem!) presents a zero when next appearance = blank.
When this happens I want the formula/code to repeatedly lookup the next appearance until it finds a number.
Examples of where next numbers appear are given here in column R.
In AA1 i input the date based on the lookup.
I want to filter Column 7 (Col G) to what ever is in AA1.
Say i had the date 02/05/2012. My filtered range should only be dated 02/05/2012 but its not filtering to those dates
Code:
With Sheets("UNPRODUCTIVE")
With .Range("AA1")
.Formula = "=IFERROR(LOOKUP(2,1/(TEMPLATE!S12:S40""""),TEMPLATE!B12:B40),"""")"
.Value = .Value
.NumberFormat = "DD/MM/YYYY"
End With
On Error Resume Next
.Range("A1:T1").AutoFilter Field:=7, Criteria1:=.Range("AA1").Value 'filter with only the dates the macro is run for
On Error GoTo 0
End With
I am working in excel. I have a column with about 30 rows. After computation only a particular row will have an alpha-numeric value, this will be any one row never more than one, all the remaining rows will be completely blank.
My question is, how do I represent this alphanumeric value which can come in any row to a particular cell.
I have a column in an excel worksheet that contains either desired text or zeros.
What I'd like to be able to do would be to sort this information, (via some VBA code) eliminating the zeros whilst maintaining the original order of the desired text.
ie, as an example
original column : 0, 0, cat, 0, 0 dog, bear.... which I'd like to show in the next column as : cat, dog, bear
I have a file having 9000 rows with S.No. 1,2,3,4 etc. ,in ascending order.It has downloaded from available system in the company.
Can it be possible the file be in the format of desired sheet ( attach file ) through macro working or excel formulaes which is best suitable for the achieving?
For more clarification, refer the attach file of download sheet and Desired Sheet.
I'm trying to concatenate quite a few cells together. One of the items being included in the concatenation is a date, however when I do this the output is the numerical version of the date that Excel uses to mathematically calculate with. My question is how do I do a cell concatenation with the desired output showing the date, not the numerical equivalent used by Excel? Book1.xls attached is what I'm talking about.
View 2 Replies View RelatedI am using a refEdit control on a userform to get a range from the user (they use the refEdit control to click on the desired range). My problem is that the user typically has 3 or 4 different workbooks open and if one of them is maximized withing Excel, the user is unable to get to them to select the range (only the maximized workbook is displayed and usable). Is there a way around this?
View 4 Replies View Relatedfunction that add to any cell that doesn't reach the disired amount.
If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it.
For example: For arguements sake the penalty is $150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show $150.00 and for every cell selected that falls short of 14 should add an additional $150.00 to 1D.