How To Get A Directory
Apr 22, 2009The command Application.GetOpenFilename will open a explorer to be able to choose a file, what is the command to open a explorer but only to choose a directory?. Is there such a command?.
View 7 Replies
ADVERTISEMENT
List All Files From Directory And Sub Directory?
Sep 5, 2013The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.
Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|
[URL]
Prompt User For Directory And Filename; Then Save File In Directory With File Name
Jun 10, 2009I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies View RelatedSave To Different Directory
Feb 2, 2010The Kill function works OK but the ThisWorkbook.SaveAs part does not work for some reason.
Please note that there are 2-spaces between the words: P3 CATS
Search For A Directory Using VBA
Dec 20, 2011For a macro i'm writing I need to search for a directory.
Users enter a projectnumber, this is being used for a search string.
But how can I search for folders/directories in Windows?
I've got the startfolder: 'C:Projects'
Then the project which the user is searching for can be in several subdirectories.
Save To A Directory
May 11, 20061. Is it possible to save additional worksheets (right now it is only copying one worksheet)?
2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")?
Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks!
Private Sub SAPfile_Click()
myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
Worksheets("Output-SAP Plan").Copy
ActiveWorkbook.SaveAs myfile
ActiveWorkbook.Close
MsgBox "SAP file created."
End Sub
Print Directory Using VBA
Jul 6, 2006I've been looking for code to print Excel files from a directory with multiple worksheets. I found something close on this forum, but it prints 2 copies which is fine for pdf, but not for hard copy. I would like just 1 copy.
Sub PrintPDF_PLOTDIR()
Dim PATH As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application. ScreenUpdating = False
PATH = "C:DATA\_PLOTDIR"
FileName = Dir(PATH & "*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=PATH & "" & FileName)
Application.ActivePrinter = "Adobe PDF on Ne00:"
For Each WS In Wkb.Worksheets
ActiveWorkbook.printout Copies:=1, Collate:=True
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Find Parent Directory
Nov 5, 2008Imagine this:
* Main Category #1
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
* Main Category #2
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
Sub Title #5: "Value"
Sub Title #6: "Value"
Sub Title #7: "Value"
Sub Title #8: "Value"
Sub Title #9: "Value".................
Main categories and subtitles are in the same column. "Value"s are in the column to the right.
I want to make a list in the columns to the right. This list will only show something if the "value" is not zero. It will show in the first column right from the original list, the main category to which the subtitle belongs to and the subtitle. Something like this (imagine subtitle #2 from cat #1 and #2 and #3 from cat #2 are not zero):
Add Or Delete Directory In The List
Apr 29, 2014I want to have a excel file with VBA code and it should operate like the following:
It should check the subdirectory's in a certain directory and put this on a excel sheet.
Every time when you start the sheet it should compare the sheet with the sub directory's and make al the changes in the list.
In the end the excel sheet should be a representation of the directory.
I can't get it to work when something is changed or a directory is added or deleted.
[Code] ....
Dialogs SaveAs With Different Directory
Aug 6, 2005An already existing file will be changed by my macro and at the end I want the user to save the file with a different name in a different directory with the option to change the filename or directory. Therefore I use the excel-standard-dialog "save as". But I want the dialog to start in another path as the path the original-file is stored in. But also I use the ChDrive and ChDir Parameters the dialog even starts in the original-file-path. I think this is standard for the dialog and I should maybe use a parameter in the show-arguement? The code is below.
Option Explicit
Sub SaveMyFile()
Dim xFileName, xAnswer
ChDrive "J"
ChDir "J:myfolder"
xFileName = "testme.xls"
xAnswer = Application.Dialogs(xlDialogSaveAs).Show(xFileName)
End Sub
Changing Referenced Directory
Oct 12, 2008I had posted earlier and felt I had solved my problem, but I have not. What I am trying to do is use code to change directory when the save menu is activated.
I want to be able to open from one location and then it defaults to another. I have tried "chdir" , but I can't seem to get it to work. Even when I change the DefaultFilePath, the "save as" location defaults to the location where the file was opened from. I want to default all "saves" in a specific location without using the menu to select.
Dialog To Select A Directory
Oct 17, 2008Is there a dialog box like the one used in GetOpenFilename that can be used to select a directory?
I'm trying to select a directory/folder which will change each month but the macro selects the files from inside the folder once it's specified. I don't need the name of the current path; the path name needs to be able to be selected.
SaveAs To Same Directory But Different File Name
Feb 4, 2009By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").
View 4 Replies View RelatedDirectory Names On Different Computers
Aug 3, 2009I have been limited lately to only working on this project at work because I have added a few lines here and there throughout the workbook that make a reference to the directory the file is located in. for example:
View 2 Replies View RelatedStatic Data For Directory
Oct 3, 2011I have multiple VBA modules that all refer to directories on the local machine and across the network.
I am currently defining the directories in each module.
How can I define them once and then refer to them in any module?
Set Word_Template_Name = "Sanitary Survey Template.dotx"
Set Excel_Template_Name = "Sanitary Survey Blank.xlsm"
Set WQCDdir = "c:CO Sanitary Survey"
Set WQCD_Source_Dir_File = "...wqcProgramsSDWEngineeringTemplates"
This would ensure that any directory changes could be made once rather than 5 times.
Manipulate Code To Add New Directory
May 21, 2012Here is the code:
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
Dim d As Range
Range("B:B").Clear
lRow = Cells(Rows.Count, "E").End(xlUp).Row
If lRow < 8 Then Exit Sub
[Code] ...........
The red is the part that I tried to manipulate to add the new directory, the problem I can see is that the new directory contains folders with the following name "WO#____" as i can see the # sign is probably throwing the program off because it can't open up the link to that folder in order to look for files. The program as it stands still works fine with looking up the first directory. I also know we had created a function to solve the sign problem for directory 1 file names. How can i use that function to directory 2.
Add Folders To All Directories Within Another Directory?
Nov 29, 2012I 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.
Looping Through Each File In Directory?
Mar 5, 2013How can i loop through each excel file in a directory.
View 1 Replies View RelatedUsing Current Directory In Save Name?
May 15, 2013I am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!
Here is where I have got to so far but fails on save,
Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub
File List From Directory
Jul 1, 2013I have a spreadsheet, where the corresponding invoice number is located in column I.
All the necessary invoices have been scanned in as pdf's in folder Y:FinanceSalesInvoices.
The question is, can a code be written to say in column Z, that a hyperlink be created to that particular invoice. For example, in cell I2, it would have Invoice1, then in cell Z2, it would have a hyperlink of Y:FinanceSalesInvoicesInvoice1.pdf.
Looping Files In Directory - VBA
Aug 22, 2013I wrote the following routine to loop files in folder. I have 4 .xls files in my test folder. This code is only grabbing 1 then exiting. I have used the Dir() loop before with no problems.
Code:
Private Sub GetDataMultipleFiles(ByVal importExt As String, ByVal xFileNum As Integer, ByVal importLoc As String)
Dim Sep As String, myFile As String
Sep = Application.PathSeparator
myFile = Dir(importLoc & Sep & "*" & importExt & "*")
Do While Len(myFile) > 0
[Code] ..........
Hyperlink Dynamic Directory
May 27, 2014I have a word 2010 file with some hyperlinks to some Excel files and Excel cells. All these files are within a a single folder that I shall send to another pc. I don't know how to make the hyperlink directory path the same as the word file except for the file name.
View 1 Replies View RelatedOpen A File Without The Directory Name
Jul 26, 2007Can i open a workbook without knowing the directory? more importantly without showing the directory?
I want open the file "EstimatingSheet" where ever it is.
Find File In Directory
Jul 18, 2008I have a workbook that is produced by some other application that names the file "FAMMToday_#.xls"
Where # increases from 1 to 2 to 3 to 4 to 5 and so forth until all the analyst are done with adjustments. so the directory can look like but doesnt always go to five and nor is it limited to five.
"FAMMToday_1.xls"
"FAMMToday_2.xls"
"FAMMToday_3.xls"
"FAMMToday_4.xls"
"FAMMToday_5.xls"
Is there a way to pull the latest file?
To Get File’s Directory (Address)
Dec 7, 2008If I know the name of the file how can I get that file’s directory (Address) with macro or function?
View 9 Replies View RelatedActive Directory Authentication Using VBA
Dec 11, 2008From an Excel workbook userform, I want to capture a logon name and password, and then authenticate against Active Directory.
The only information I want from the process is whether the user name/password combination is valid in AD.
I know how to do all the Excel stuff (userform, etc.), and I know how to get information from AD using an administrator binding. What I am missing is being able to verify whether the Excel workbook user is the AD user that they claim to be.
Lots Of XLS Files In A Directory
Feb 6, 2009Lots of XLS files in a directory (C: emp). Each book has only a sheet1 with the data.
I have another workbook that i need to use to overwrite certain values in one column from each book.
The workbook I have has a column A and B, ID and value.
In each file inside the directory, A3 to last row used in A had each ID. If the id matches to an id in the workbook with the new values, I want to take the value from B in the override table and put it in C in the file. Look at each ID in each file against each ID in the override, replace and close.
Create Directory Tree
Dec 9, 2009I've been trying my best to create a directory structure using VBA and Excel, and have run out of patience on how best to do this. My goal is to create a directory tree containing 2 levels. The 1st level (Main Folders) will be named from data in Column A of an Excel Spreadsheet, the 2nd level (Subfolders) will be created from data in Columns B, C and D of the spreadsheet. I've been using the following code with success, but don't know how to create subdirectories:
Sub StartHere()
Dim rCell As Range, rRng As Range
Set rRng = Sheet1.Range("A1:A20")
For Each rCell In rRng.Cells
CreateFolders rCell.Value, "C: est"
Next rCell
End Sub
Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String)
Dim sTemp As String
'Make sure the base folder is ready to have a sub folder
'tacked on to the end
If Right(sBaseFolder, 1) <> "" Then
sBaseFolder = sBaseFolder & ""
End If............
List Of .xls Files In Directory
May 26, 2006Hey is there any way to get a list of filenames into VBA from a specific directory?
for example all files in directory c:my documentsexcel?
Finding Files In A Directory
Jul 18, 2006I have the following
Sub test()
Set fs = Application.FileSearch
With fs
.LookIn = "\directory"
.Filename = "3000333"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
It looks into a specific directory, and tells me how many files beginning with 3000333 there are in that folder. I have over a thousand files in the folder, 13 of which begins with 3000333. However, according to this code, it only finds 10. After searching for various different strings, I've found out that it doesn't search for any file that has a modified date later than 6/2/2006. However, if i just use .Filename = "3000333" , that is, search for all the files in the directory, I get all the files, even those that were modified later than 6/2/2006 that I could not previously find.