What's a proper way - in Excel VBA - to get the current folder *without* the preceding folders/path?
For example from folder "C:oracleora81sqlplusdemo" I'd like to retrieve "demo".
Currently I have:
Code:
DirNames = Split(ThisWorkbook.Path, "")
CurrentFolder = = DirNames(UBound(DirNames))
It works, but I suspect something exists specifically for this one.
I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.
1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …
2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?
I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as “c:
eports8052008”, so tomorrow 's folder would be “c:eports8062008”.
All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), “mmddyyyy”), get error message.
I want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
My Excel 2010 spreadsheet contains client data like the below:
A
B
C
D
E
What I'm trying to get from this is a personally addressed email with 2 attachments, one will be standard to all recipients and one will be unique and specific to that recipient. The filename of the unique attachment will contain the reference but will have some other stuff in the filename as well on either side that I will not be able to remove.
In terms of the file locations the unique ones will be in subfolders of the folder holding this workbook and the generic one will be in the same folder as the this workbook.This is some adapted code that solved a similar problem (in Excel 2007 though) on a different website.
VB:
Sub Mail_Report()
Dim OutApp As Object
Dim OutMail As Object [code]....
In that case the file started with the "reference" field but in my case it is in the middle and the formats vary depending on the provider, there will only be a maximum of 5 providers but I would like to avoid 5 different macros if at all possible so I need a search function of some sort.
I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
I am using this code to import excel files into one workbook..I have the path hard coded. Is there a way to get this automatically so if another person is running on there machine it will work..
OR
can you define the path in like cell A1 and have it pull from there?
Sub GetSheets()
Path = "C:UserswharnedDownloads"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Is there a way to find a folders path? I have a folder Dropbox
I want to find the path up to this folder on the current computer. Is there a method to accomplish this?
I am fine if you want to give VBA code so I can put it in my immediate window for now and then I can attempt to add it to existing code later.
So an Example of the possible result would be
Directory is C:MyNameUserDropbox
Or on another computer maybe C:User1ListingFilesDropbox
I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.
I did a number of searches and I can't find exactly what I'm looking for.
I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.
I made an excel document for my boss with the list of the furniture in an flat that we will rent. The document will be signed by the the guest of the apartment and will be used if anything at the end of the renting period is ruined or is missing. It's kind of insurance. The problem is that next to the cell with the list of furniture I made a list of hyperlink connected to the photos of the furniture. Everything is working on my computer where the photos are in a folder that is contained in "documents" but as soon as I try it in another computer placing the folder again in the document folder it doesn't work. It says "the file is missing". I just realized that I need a dynamic path but today I have to handle the excel file and I don't know how to do it and to change the path of every link with just few clicks I have no time to change photo by photo (there are like 300 pictures).
View 1 Replies View RelatedI start an Excel program from a shortcut in different folders. I know that I can find the path of the Excel program in "Parent.Path". But I don't know how to find the path of the folder the shortcut was in. It should be findable because when I try to do something like <Save>, Excel knows the correct folder there.
An example: I open Excel in "Folder 1" using a shortcut to the Excel Program in "Folder 2". Parent.Path tells me "Folder 2". How do I find out what "Folder 1" is from inside Excel?
Is there a way for excel vba to find a folder named "MyFolder" in Drive C: and copy/paste a file into that folder without knowing the entire path?
Example:
Workbook("Myfile.xlsm").copy
"C:" Pathfile "MyFolder" . paste
i am trying to do, i have a spreadsheet with 100+ tabs all with the day of the year. Jan1,Jan2,Jan3...Feb23,Feb24,Feb25... etc etc. I want to pull all of the same values for each of these sheets on a single tab (using vlookups, hlookups and reformatting the information for a macro to input all of this data into a database)
My question is, how can I put a entry form, lets call it a1 and fill in the tab i want the contents of the page to update with? For example, if i input "Jan18" in a1, all formulas on the page look for their source information on A1.
i 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
What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.
View 5 Replies View RelatedI am using some code to loop through a folder and extract some data from a specific sheet.
Now say a user moves the file to another folder etc... The macro will pull up an error, now is there a way i can have it dynamic linked to the spreadsheet so no matter what folder it gets moved to,
My folder path will adjust to that or need to take another route?
information about users selecting a folder at this link. However, how do I change the starting point to a network drive, as opposed to "My Computer"?
For example, we reference our network drives like this:
\OurSite.comSharedFoldersDivisionName
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].....
Essentially, 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?
How to query the current folder location in VBA?
The macro will need to import text from a file (file.out) located in iteration folders (iteration_001, etc) but I need to start by seaching the location that the excel file has been saved in.
I have been looking for a way to enter the folder where the excel file is located into a cell.
I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:
C:docsfolderdataClass D4John WayneProfile.xlsx
Would enter "John Wayne" into cell. The cell is namely the "name" cell.
I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).
I'm running a macro that intially opens an input box where you specify the filepath and name of a .ped file to import into the macro. The default path of H:BOM2.ped may not always be the path I use. It may be just H:.ped or H:Bom1.ped.
HTML INITIAL CHECK
Response = MsgBox(".............do you want to import BOM data?", vbYesNo)
Do Until Response = vbNo
'SELECT RAW FILE
Dim Message, Title, Default, MyValue
Message = "Please Enter PathFile Name for Source File"
Title = "Create CHINA INSPECTION REPORT(s)" ' Set Title.
Default = "H:BOM2.ped" ' Set Default.......
The macro works when I have my user name in the path but when I try to insert an environment it does not work.
View 3 Replies View RelatedI have this formula that opens the current folder where my file is stored:
=HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"Open Current Folder")
This works fine. I'd like it to open the folder one level up from the current folder, but I really don't understand formulas well enough to know if this is even possible.
I recorded a macro to save a workbook to a specific folder using the current file name. However, I've tried to remove the absolute reference to a specific filename but it is not working for me. Am I close?
ActiveWorkbook.SaveAs Filename:= _
"H:Files and DocumentsPROJECTSSR 2 SS Phase Two Component ReliabilityWinTrac Files xt_conversionsexcel_version" & SheetName _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet
[Code] ........
I have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.
Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("D1").Value
Next
[Code]...
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.