how can I creat a directory and then save the same file into that directory. At the moment Im using -
ThisFile = Range("T3").Value
ActiveWorkbook.SaveAs FileName:=ThisFile
...which works great, but before I do this, I want to create a folder with the name the name/value in T4, then ofcourse save the file into that folder ....
How can you add the date from a sheet say A1 which is a long date and make a directory which the date incorporated into the new directory name?
View 2 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'm looking for a macro which within a sheet named "foldernamedump" will list in a column the folder names within a directory I specify. I have seen a couple of sample codes but I just cant seem to get them working at all so I think its best to start from scratch and the work i get supplied here is always perfect. I also want the macro to clear the contents of the sheet before it loads again just to ensure there is no old data within the sheet.
View 5 Replies View Relatedi am wanting to automaticaly remove a file from a directory when this directory is populated and move to another directory that is secure, how would i do this? just say directory 1(where it will be removed from) is s:/ddc/reports
moved too-- s:/ddc/test
test folder will be secure
I need some VBA code to cycle through each and every folder and file in a given root directory and write the file name string to a cell. (does not need to look at the content of sub-folders only the names of all objects sitting directly under the given root.)
View 4 Replies View RelatedI would like to list all files in a directory (display the full path) and make hyperlinks to all listed files at the same time.
I came accross the code below which works well for files in a directory:
Sub HyperlinksToDirectory()
' puts hyperlinks to each of the files in a directory of your choice
' into the active sheet starting at the active cell
Dim stDir As String
Dim stFile As String
Dim R As Range
Set R = ActiveCell
stDir = InputBox("Directory?", , Default:=CurDir())
stFile = Dir(stDir & "*.*")
Do Until stFile = ""
R.Hyperlinks.Add R, stDir & "" & stFile, , , stFile
Set R = R.Offset(1)
stFile = Dir()
Loop
R.CurrentRegion.Sort key1:=R, order1:=xlAscending, header:=xlNo
End Sub
I'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............
Is it possible to create a VBA script that will list the filenames of files in a set directory? I have about 100 directories, each containing 1000 files and they need to go into an excel spreadsheet (each file on a new row)so they can be audited... ;(
View 3 Replies View RelatedThis should be so easy, but I know I am missing something. I know that you can only create one directory on one statement line at a time with MkDir. in the "var_dir_path" resides a part number that changes dynamically.
Private Sub CreateFolderFromCellPath2_Click()...
I have Workbook named"Test_Template" in below specified path this is not the active workbook.
It is saved in strFName = ThisWorkbook.Path & "Backup" & "Test_Template.xlsx"
I need to create a copy of the same workbook named as "Copy_Test_Template.xlsx" in the same folder(Backup), same specified path on button click.
[Code] .....
But this creates a "Copy_Test_Template.xlsx outside the Backup folder, i want it in the back up folder.
I'm trying to create a folder on the desktop and save the activeworkbook in that folder. Since other users will use this I dont know the exact path. However I'm using this function below to tell the script where to find the path. Everything was working fine till I started to make the folder can someone point out what I may be doing wrong?
View 5 Replies View RelatedI'd like to create a macro which will
-go to a given folder
-open each of the workbooks in the folder, one at a time
-after opening the first wkbk, go to a specific cell address on the first tab of the wkbk
-paste in some values and formulas
-save the workbook
-open the next workbook, and repeat as above, for all workbooks in the folder
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
I have the following code (that I borrowed) and was using it to export some worksheets to a new workbook. It worked fine for about 4 times, now it says it can't find the file path and stops on the "MkDir FolderName" line.
View 2 Replies View RelatedIs there a way of creating a new excel workbook using a macro that is stored as an Add-In.
The new workbook is to be created in the same folder. If I use the following command in macro then it saves the file in the Add-In folder.
i run this macro but it fails
Can anbody tell me why please?
And how can i correct it? .....
I am wondering if it is possible to get excel to Create a new folder from a cell value? I am entring data into cells Eg A1-F1, and then the next line of data is in A2-F2 and so on. What I'd like is when I enter data (the date) in the F column then it will create a folder with the value of H2 (=A2&B2&D2&E2)
View 14 Replies View Relatedcreate macro for the below scenario I need to search whether "Sep-13" folder is available in my desktop. If folder is there then i need to open the folder else i need to creat a folder in the name "Sep-13" and open the folder.
View 4 Replies View RelatedI'm trying to create a link to a folder and to insert it in a cell. The folder name is: "C:DISCO CTEMPLATES #1". I tried to copy that text and paste it into the cell. Excel recognizes that as a hyperlink but when I click the cell a message appears: "Cannot open the selected file" Could the problem be because of the "#" symbol?
View 2 Replies View RelatedI don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox?
I have made a userform that I use to collect a persons first name & surname.
I then created a folder in a directory manually (e.g Z:spnzphotos)
I then scan their photo and place it into the folder.
Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
I'm having a little trouble making this code work correctly. This macro, Once finished, is connected to a (Save) command button. Here is what is suppose to happen, First thing to remember is another sheet (Sheet1) has been populated with various information which is used as a receipt. The information came from sheet2. I need to copy sheet1 just as it is, format included, create a new empty sheet, paste the information into the new sheet, name the new sheet with the values from cells in sheet2, E3, D3, and the date formatted as the month and year only. Then I need to create a folder in the my documents folder named from the values from sheet2, E3, D3, and todays date with the current time. If the folder already exists, then I just need it to change to that folder, and save the newly created worksheet (as named above) into that folder. After the save is executed, I need to delete that new sheet from the current workbook. Then I need to return to sheet2 to the last active cell and run the last few lines of code which collects the value of the cell 1 to the right of the active cell and transfers it to the cell 1 to the left of the active cell.
VB:
Public Sub Button11_Click()
Dim wShell, fso, strFldr, xdir As String
Dim newbal As String
[Code].....
Can a macro create a folder in my computer and use the value in cell A1 as the folder name? This is where I want the folder to be created C:UsersUserDesktop.
View 8 Replies View RelatedI need to check if a folder exists and if not then create it. not sure how to go about this
View 2 Replies View RelatedI am totally new to both EXcel 2007 and Programming however i would really like to create a main folder using coloum A and then a subfolder within the main folder just created using coloum B.
I have looked on line and found some code but when i put/assign the code to a button it never appears to work.
I want to write a piece of code where each day i run this code
I want add a workbook
Copy range ("A1:C5") to this new workbook
Save new workbook in a particular folder with the name customer data and todays date and save as CSV file
I run this this code every day so it should only add todays date at the end for filename
If for some reason I run this code again in the same day..
I want to have a msgbox stTing you have already ran this code today and exit sub
I have code that references a cell with a file path in it. If the filepath is not present I would like the folder and file to be created.
Sub Create_Path()
Dim strfolder As String
Dim filename As String
strfolder = Range("n17")
filename = Range("n16")
If Len(Dir(strfolder, vbDirectory)) = 0 Then
MkDir strfolder
End Sub
I have used this code but am getting an error at the "Mkdir strfolder" section. What do I need to change to create the file if no folder is found.
Cell N17 has the filepath
Cell N16 has what I would like to name the file as
I would like a windows folder to be created on a server from cell Cbut to be filtered by the Data in B.
dwights folder on teh server to create '124 elm street' from the in C1
james folder to the server to create the folder '15 killfops' street'from C5
\server empdwight
\server empjames .....
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
View 3 Replies View RelatedI have the following code which saves a certain sheet to a new workbook and then emails it to a range of cells. The directory the file is saved in is from a cell, and changes depending on certain cells in the workbook. I would like to make it so if the folder it is saving to does not exist, it will create it. If this isn't possible with this set up, I would just like it to display an error message if the folder is not present.
VB:
Sub Save_Report()
Dim wbName As String
Sheets("TELELINK & ITINERARY FORM").Copy
[Code].....