I 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.
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 am writing a macro that scrapes information from other workbooks. I wanted to add a hyperlink on each line to the workbook that row's information came from. I tried both the VBA method of adding a hyperlink as well as the formula method. When I click on the link that was created, windows explorer opens up to a folder rather than opening the workbook. I have a feeling I am hitting some folder level limitation. The link is to a network share and here is an example of what it looks like:
\MYCOMPANY.CORPFILESCBOSEALDRIVEDeptROPSPT Billing2007 AdjustmentsMCBDApproved#01305.20075.25.07 - C - NAME.xls
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.......
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.
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies View RelatedI 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 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]...
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.
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.
I am making a chart for displaying the amount in inches that we have in the water tanks.
The idea is to have a setup like this...
I'm building a sales report that has various formulas to work out rebate percentages based on the number of units.
I've written the if statements for these but how to get formula to work out the number of units required to achieve the next highest rebate level.
rebate levels
>=150 units= 1% rebate
>=300 units =2% rebate
>=600 units =3% rebate
column I has the unit totals and in column M I need to have a formula to work out how many units the customer would need to purchase to achieve the next rebate level. Headers are row 1 and data begins in row 2.
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?
How to solve my problem in attached file : Week Product Level Count.xlsx
View 1 Replies View RelatedI am trying to figure out a way to open a folder through VBA. I just want the G: drive to open and then it lets the user navigate any folder within the G Drive and select which ever file they choose to open.
View 1 Replies View Relatedthis is my folder path:
\c06661Documents and SettingsptliDesktopPresentationDatabase Common Driver
\c06661: is my Full Computer Name
Database Common Driver: is my folder name
how can use the VBA code to access this path to access My Folder
A little unrelated to excel as such but helpful to many of us I think. How to set a password to open/ view any folder or directory?
View 2 Replies View Relatedi currently have a pop up message box that reminds me to open up a folder but rather than click ok then go to desktop and open up the folder i would just like to click the ok button then the folder opens automatically.
View 14 Replies View RelatedI was wondering if it is possible to use a macro open a folder in Windows Explorer. Previously someone on this forum taught me how to open an Excel file from a macro, but in this case there will be a new file every week. As the files will all be stored in the same folder, I thought it would be easier to open the folder in the normal Explorer view and select the file manually than to change the macro every week.
I am using Windows XP and Excel 2003. My programmes are run from the C drive, but my documents are stored on the E drive. What I am looking for really is a line that says: open in Explorer E:data
ewprocessed.
I have created a Spreadsheet that does all of the calculations, analysis, graphs, etc. that i need; however every time I wish to use this spreadsheet I need to first open up a spreadsheet with data, copy it and then paste it into the original spreadsheet. While this is not horrible, it is quite tedious to do several thousand times.
Thus, I was wondering if it were possible to create a Macro that would open up all files in a specific folder, then select each one and paste the relevant data into my original spreadsheet. I know this possible for specific files; for example, I have created a Macro that will select files data.xls, dats1.xls, data2.xls, and paste these into the spreadsheet, but I was wondering if there was a way to generalize this so that it will simply open every file regardless of the name or how many files there are in the specific folder.
I have a macro that opens all workbooks in a folder and searches for a few terms returning the appropriate rows, my problem is that someone has decided to protect some of the sheets so when the files are opened you get the password box popup. I have added the (filename, readonly) segment however this has not solved the issue, the macro displays an error stating a workbook is open.
Is there any way to resolve this without unprotecting the sheets?
I have a prompt for the user to select a folder and then assign the path a variable. I would then like the VBA macro to open all files within that folder (no sub folders) that end in .xls.
View 3 Replies View RelatedI want open excel files in a folder with ascending order how can I do this,
Eg:- In C:6-11-28 folder there are files as F1.xls, F2.xls , F3.xls......
I want to open above files and get some data from each excel file
Folder name change as System date
I have an excel 2007 worksheet named "Invoice" that I would like to be able to navigate with using a command button that will open a separate File Folder or sub folder called "Report" that stores all my reports in for viewing.
My excel worksheet named "Invoice" and File Folder or sub folder named "Report" are located in the same sub folder that is named “Marketing Report”.
This report will be used on different computers and I’m sure different location / Path on their computer.
Workbooks.Open Filename:=ThisWorkbook.Path & "Report"
and
Private Sub CommandButton3_Click()
Dim fn As String
ChDir ThisWorkbook.Path
Report = Application.GetOpenFileName()
Workbooks.Open(Report)
End Sub
I also tried to record a macro but would not record going to file folder.
I need to open all the excel file in a sub folder
(example : CCostingAccount*.*.xls)
It mean by run a macro, it will allow me to be open all excel file in the Account folder .
It says that she has to install excel when she tries to open a folder from oracle. I tried to associted to excel but it did not work.
View 2 Replies View RelatedI want code to:
Open EstimatingSheet.xls
SaveAs current workbook as Range B13 &".xls"
Open EstimatingSheet.xls workbook from somewhere on C:
Then close the one with B13.xls