How do I setup MyPath to a cell reference? In this case I need to set MyPath to the cell value of I1
Code that I have that doesn't work...
Sub SaveToFolder()
'Save File to Path
MyPath = I1
ActiveWorkbook.SaveAs Filename:=MyPath & ActiveWorkbook.Name
Windows("PE Console.xls").Activate
End Sub
is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?
I notice that the hyperlink is ....pdf ry.pdf
if I change the position of the file excel there is a problem!
I would like to save es. d:invoichepdf ry.pdf
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
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
I'm trying to copy a file from one directory to another directory. However, the source directory, I'd like to have it so it's created from values within specific cells on a Sheet named "PULL".
When trying the following, I get an error permission denied.
I am creating a training records matrix which will be used by 30 or so locations. each one may want to save in a different location. I would like to save the chosen location as an entry in a cell so this can be accessed by other macros.
View 9 Replies View RelatedI have a worksheet (sheet1 in book1.xlsx) that references another worksheet (sheet2 in book2.xlsx) in a different file. The latter file, however, is stored in a directory that changes. Call it MYPATH for the time being. I'm trying to use a formula (not macros, which aren't allowed in this office) to reference the directory path using the dynamic name.
For example, in the first worksheet, cell A15 contains the word MYPATH. I'd like cell A1 to have a formula like this: ='C:&$A$15&subdir[book2.xlsx]sheet2'!B50
so that if I change the value of cell A15 in book.xlsx to ANEWPATH, the path reference will switch to the book2.xlsx in ANEWPATH, not MYPATH.
I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.
How do I make the formula the same for the whole range as it will substitute the necessary path into itself?
here is an example:
here is a code for VBA:
Range("I2").Select
Selection.FormulaArray = _
"=INDEX('1_Repl'!$C$2:$C$8,MATCH(1,IF('1_Repl'!$A$2:$A$8=H4,IF('1_Repl'!$C$2:$C$80,1)),0))"
Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault
******** ******************** ************************************************************************>Microsoft Excel - TEST_LOOKUP.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI4I5I6I7I8I9=ABCDEFGHIJK11st Spreadsheet 2nd Spreadsheet 2 3Number123 CodeNumberData2 41230.200.2 11230.22Code=1, so 1_Repl.xls 52340.10.10 12340.1 6345000 2345#N/Ahas to refer to 2_Repl.xls 712300.220 3456#N/A 8234000.15 3567#N/Apath has to be dynamic 91230.240.240.24 3678#N/AValue of G2+ "_Repl.xls" 103450.1300 11 1_Repl [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a few files all with the same name for a few different members of staff, so I save them in different paths to keep them separate.
eg
c:documentsdatajo blogsexcelfile.xls
c:documentsdatafred smithexcelfile.xls
c:documentsdatajane jonesexcelfile.xls
cells is the sheet contain the persons name
jo blogs
fred smith
jane jones
I want to be able to use this for others in the organisation and make it a simple as possible
Is there a way to use the cell contents in the path name (without opening the sheet first)
I know I can point to each cell in the other file and excel will use it but there must be a way of making this happen more automated by using the cell contents
The folder which contains the Excel file I'm working on is
C:Usersar3DocumentsKey Performance Indicators
however if I place =INFO("directory") in a cell I get
C:Usersar3Documents
Is there a function where I can get the full path of the workbook in the cell?
How to link an image to a cells value? The linked image is a range of cells and that range is A5:K30
I will take a macro if that is the only way.
linked images path = cell B2
B2 = ("P:path"&TEXT(TODAY(),"yyyy")&"_stuff"&TEXT(TODAY(),"mmm")&IF(OR(TEXT(TODAY(),"mmm")="Nov",
TEXT(TODAY(),"mmm")="Dec"),TEXT(TODAY(),"y")-1,TEXT(TODAY(),"y"))&""&TEXT(TODAY()-1,"ddmmmyy")&" stuff.xlsx")
I am trying to use the same VBA code for different applications using variables.
Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.
sometimes I would want it to equal what was in a1 and sometime I would want what was in a2
a1 cell contains "f:filezilla files
a2 cell contains "f:dmc files
This is the code I started and it gives compile error when ChDir = sourceloc is reached
Public sourceloc
Sub filezillasettings()
Dim sourceloc As String
sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
End Sub
Is there a way to use the open dialog box (like as if you where opening another workbook) and then with the selected item inster the path and file name into a cell.
View 2 Replies View RelatedI have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
I have this code that copies existing folders to a new destination path. How do I change the code so that the From path and To path is based on cell value rather than writing it into the code?
Code:
Sub Copy_Folders()Set Fobj = CreateObject("Scripting.FileSystemObject")
Fobj.CopyFolder "C:UsersHengDocuments1243-01234photos", "C:UsersHengDocumentsCasework1243-01234photos"
End Sub
I think this is possible but cant find anything on it ........
View 9 Replies View RelatedI have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.
I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.
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?
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?
I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies View RelatedGet Path to XLA ...
View 8 Replies View RelatedHow to get the get the path from the GUI in Excel-VBA or select the path
and how to declare it as universal...
How do I assign the path of the workbook I currently have active, to open various other files?
I want to make the path for opening various files dynamic. All the files will be saved in the same folder, but in different locations.
I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.
FileName = Application. GetOpenFilename _
FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know.
how can I refere to the path whaere my addin is located without having to write it
i tried with this, but....................
Sub AddingAddIn()
Dim p
p = Application.Path
AddIns.Add Filename:=("path")
AddIns("HTML").Installed = True
End Sub
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
How can I get the name of a workbook WITHOUT the path when I have the name WITH the path?
To be more specific. When I use "Application. GetOpenFilename" the result is a file name for say "my_book" including the path. With this I can open the file using "Workbooks.Open" and Excel will show me "my_book". But when I want to reference this workbook later on in say "Workbooks(my_book)", then I need the file name without the path. How can I get that one in VBA?
Till now I was using "CurDir" to find out the path after opening the file and just subtracted it from the full file name. This seamed to be fine. But now I had a situation where CurDir gave me a path with abbreviations (~),