Spaces In Path And Filename

May 9, 2006

I have a problem with paths and filenames with spaces.
If the the excel file containing the macro file is opened from explorer rather than from within excel, the path is incorrect. Therefore I use the following

Dim varPath As String
varPath = ThisWorkbook.Path
ChDir varPath

I can then later call either varPath or the VBA GetCurDir and it will be correctly set.

However, when I call a Shell function as in the example:

Dim varPath As String
Dim fPath As String
varPath = ThisWorkbook.Path
ChDir varPath
fPath = varPath & " emp"
MsgBox fPath & "test.bat"
Shell fPath & "test.bat"

I get the message box: e:local data tables emp est.bat
I get the error: Runtime Error 5, Invalid procedure call or argument.

Without having to rename the path structure so that it doesn't have spaces (which isn't always possible), how can I get this to work?

ADVERTISEMENT

To FIND The Filename In A Path

Dec 20, 2007

I'm trying to FIND the filename in a path such as:
F:userdjleeCSCAV Remarks.png

My path is different for each record.

I tried =RIGHT(C1,FIND("",C1)), but it does not recognize the "".

View 9 Replies View Related

Define Path & Filename As Variable

Feb 16, 2007

Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1

I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"

However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.

View 2 Replies View Related

Trim Full Path To Filename Only

Jan 31, 2008

I have path data (subfolder depth varies) that I need to trim to filename only.

Example:
/folder1/folder2/filename1.eps
/folder1/folder2/folder3/filename2.eps
/folder1/folder2/folder3/folder4/filename3.eps

Need to end up with:
filename1.eps
filename2.eps
filename3.eps

View 6 Replies View Related

Find Filename In Path Regardless Of Number Of Subfolders

Jan 29, 2014

I have a list of files in column A:

/folder/subfolder1/subfolder2/subfolder3/file1.txt
/folder/subfolder1/subfolder2/file2.txt
/folder/subfolder1/file3.txt
/folder/file4.txt

in column B I would like to see just the filenames:

file1.txt
file2.txt
file3.txt
file4.txt

How can I achieve this? On the internet I found this forumule which looks promising but unfortunately finds the first slash (instead of the last) and prints what's to the right of that slash:

=RIGHT(C2; LEN(C2) - SEARCH("/";C2))

View 3 Replies View Related

Hyperlink To Display Filename Not Full Path

Apr 5, 2012

I have the following code and I it works except it gives the full file path rather than just the name of the file.

I have been racking my brain for ages to try and figure how to just get the filename to show but I have been unsuccessful.

Code:
Private Sub CheckBox1_Click()
Range("K43").Select
' Set up list of file filters

[Code].....

View 9 Replies View Related

Get Filename And Path Of Workbook The Macro Is Running In

Apr 17, 2007

Is there a way to find the full name and path of the workbook the macro is running in?

View 4 Replies View Related

Save Hyperlink Path For File With Absolute Path And Non-Relative?

Mar 4, 2013

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

View 1 Replies View Related

Plan Path And Actual Path Distance Calculation?

Feb 11, 2014

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.

View 1 Replies View Related

Macro To Separate File Name And Path From Complete Path

Oct 3, 2012

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

View 2 Replies View Related

Amending The Filename In Cell("filename")

Jul 7, 2006

I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?

View 3 Replies View Related

List Of Values With Spaces And Make New List Without Spaces

May 20, 2013

I have a list in Column A" Job List" (the amount of entries will change based on job openings). There are some cells that are blank; randomly throughout. I need to create a new Column B "Current Jobs List" with no spaces. I have to do this weekly and each time I update my "Current Jobs List" from the new data in the "Job List" without any spaces.(I do not want to just do a filter) .

View 6 Replies View Related

Trim Full File Name & Path To File Path Only

Sep 27, 2006

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?

View 4 Replies View Related

Get Date From The Filename

Jun 18, 2014

I have a filename 'filename-12-14-2014' where i need to extract date information from the filename and insert a new heading in the file 'date' in the first column and write the extracted date till the end (until data exists). I can write the new column name but i could not write date in all the fields. code i wrote is below

this is for extracting date from the filename. the 4th lines throws me the error?

(1) LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
(2) With Range("A2:A" & LastRow)
(3) x = Split(s, "- ", 2)
(4) .Value = Left(Mid(x(1), InStr(x(1), "-") + 1), InStr(Mid(x(1), InStr(x(1), "-") + 1), ".") - 1)
(5) .NumberFormat = "mm/dd/yy"

for adding the date in the first column. This works fine

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
s = ActiveWorkbook.Name
Range("A2").Select

View 7 Replies View Related

Extract Filename Using VBA

Jul 21, 2014

I have excelfilename starts like abc - usa sales- 1-1-2014. have to extract usa from the filename and insert as a column in excel with columnname 'country'.

View 1 Replies View Related

Add Date To Filename

Jun 3, 2009

I picked up a bit of code to create a CSV with a | delimiter. so that I could save the file with a date added onto the filename. i.e. myfile060309.csv.

View 4 Replies View Related

Save The Filename

Sep 11, 2009

I have a problem with the following code

View 2 Replies View Related

Getting Date From Filename

Nov 13, 2013

I have filename as filenamex - filenamey-11-13-2013. I want to get only the date from the filename.

View 3 Replies View Related

Use ComboBox Value As Filename

Feb 4, 2008

I'm trying to use the value of a combo box as the filename when saving as. The code i have so far is:

Dim fName As String

fName = FileNameTXBX.Value

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlCSV, _
CreateBackup:=False

View 9 Replies View Related

Add The Date To A Filename

Aug 28, 2009

I need to add a date to the existing filename it is being saved as. Currenntly the filename is saved as follows:

x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
y = Cells(a, 12)
Workbooks.Add ("C:Documents and SettingsDesktopTemplate.xlsx")
ActiveWorkbook.SaveAs FileName:=y
Next a

I want the filename to stay the same as above but add "-r-"a number entered into a textbox representing the revision and then the date the file was saved in the following format dd/mm/yy.

The form containing the text box has been scripted and works fine. The string entered in the textbox currently resides in the Active sheet being saved in cell "E4".

Below is a completed Filename. Note the RED is what i need to add.

2138-035-PW-COM01-RCS-001-r-1-28/Aug/09

View 9 Replies View Related

Filename As Cell Value

Oct 11, 2009

Is there a way to take filename and place value in a cell

View 9 Replies View Related

Filename Retriever

Jun 28, 2006

I've made a simple filename retriever. Click on the button to list the filenames under the specified path. I would like it to go deeper into subfolders. At the moment it only drills down one level. Need to make it able to drill down to all the levels present and list out the filenames.

View 4 Replies View Related

Get Filename With Formula

Aug 7, 2008

I am trying to use the formula below to get the filename of the spreadsheet, it seems to work fine but when you open a second sheet the first sheet will pick up the name of the second. =MID(CELL("filename"), FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1). These spreadsheets will then be uploaded to another application and having 2 with the same name could cause issues. Obviously the answer is to only open one at a time, but this is always subject to human error!

View 3 Replies View Related

Extract Date From Filename For New Tab

May 9, 2014

Every week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm

Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below

VB:

Dim newsh As String
newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'")
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = newsh

The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.

View 6 Replies View Related

Alternatives To CELL (filename)

Apr 7, 2009

CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.

What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?

View 2 Replies View Related

Formula To Get The Date From Filename

Jun 25, 2009

I have a folder full of sales reports, all of the files are given a consistant format e.g. 09_wk50. I have a macro that do various calculations in each book. I now need a calculation based on the report date.

I am able to get the file name in the sheet so that I am just left with the value above (09_wk50) but I need to be able to get an actual date from that value.

I need to be able to get the monday of the week given. for the example given i would want the date 13/04/2009 because of week 50 in 2009, the 13th is the Monday.

View 4 Replies View Related

Using Variable Filename In VLOOKUP

Mar 26, 2014

I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.

This is the original code where it asks for the user to select the sheet every time.

[Code].....

This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):

[Code] ...........

I tried to enter the Full Costs sheet name into the code like so:

[Code] ......

However this produces the following:

[Code] .....

The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.

View 3 Replies View Related

Filename = Cell Contents

Dec 11, 2008

I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.

View 2 Replies View Related

Displaying A Filename In TextBox

Apr 16, 2009

I want to use this code to display only the filename + extension in the TextBox1. It currently shows the full path

View 8 Replies View Related

Date Appended To Filename

Oct 29, 2009

I searched this forum to find a macro that will do what I'm describing below. There seems to be quite a number of macros doing almost what i want, but most require a macro to be run, filename to be entered, etc...

I want the macro to automatically append the current date to the filename when it is saved. It must save it in the same directory where the file was opened from, and the name must be exactly the same as before, except that it must remove the previous date appended to the filename, if there was one appended.

I don't want to run the macro for this to happen, the normal File Save command must do the trick...

Thus c:my documentsTest.xls will become c:my documentsTest 29102009.xls; and
c:my documentsTest27102009.xls will become c:my documentsTest 29102009.xls

View 9 Replies View Related

Create Unique Filename

Dec 11, 2009

The code below pops up two input boxes asking the user to enter the location number and the vendor number, and then saves the worksheet as a text file to the desktop based on their input:

View 5 Replies View Related

Change Filename To A Name In A Cell

Jan 3, 2010

GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet. DETAILS: The cell with the new file name is always D8. The cell is blank initially (although it doesn't have to be). OTHER INFO: OS= WIN 7 Excel= 2007
EXAMPLE: If I input "Mike T" into cell D8, the name of the file will be Mike T.xls

View 5 Replies View Related

Creating Filename From Cells

Jun 8, 2009

I have a worksheet that I would like where you can just enter the date, then it builds a filename based on that.

Example. Filename I would like to load information from is
"C:EXMay 2009May 21,2009.xls"
The sheet is "Data" and the cell/row is A1

The Filename could be entered simply as
'C:ExMay 2009[May21,2009.xls]Data'!$A$1

But what I would like to do, is be able to generate a weeks worth of filenames from the date I entered.

I have one cell with the root directory (lets say A1)
A1 = C:EX
A2 = May
A3 = 21
A4 = 2009

So in cell A5 I would like to combine them all to load the information from that filename.

I tried to do something along the lines of

A5 = A1 + A2 + " " &A4 (etc etc), but that wasn't working at all.
The main reason for breaking them up is so that I could just increment the date to get a weeks worth of files.

View 3 Replies View Related

Using VBA To Enter Filename On A Webpage

Jul 22, 2010

I have a simple text box I need to fill in with my email which I have managed to do very simply with this code;

Code:

Dim IE As InternetExplorer
Dim sIEWindowName As String
Set IE = New InternetExplorer
IE.Visible = True

[Code]...

The layout looks like this with my email.value entered in.

I'm using Access 2007, but that is irrelevant for the VBA.

My problem is I can't enter the filename as easily. I have to use SENDKEYS to TAB over then a bunch of other tacky SENDKEY commands to load my file. Even though it works I don't feel it's reliable and would rather just fill the file name in a bit easier.

I used IE developer to show before and after using the BROWSE button to load the file location as seen in pic 1 and pic 2.

The code below has been my best attempt. It runs without error but it does not give me the result I need which is a file location for the application to pull.

Code:

IE.Document.form1.FileName.Value = "C:New FolderaFileToUpload.txt"

View 5 Replies View Related

Internet Hyperlinks - Filename Changes

Nov 7, 2013

I have a question with regards to a file on the internet hyperlinks, in which the file name (not the path) is subject to change. eg. [URL] to [URL]. is there a way to set the hyperlink in that if the filename changes slightly like the example that the hyperlink will still work?

View 3 Replies View Related

Insert Filename From A Cell?

Dec 10, 2013

Is it possible to insert the filename of another excel file based on the information in a cell within your current file?

I have a file named 12-07.xlsx, which has a sheet Data 12-07, and that sheet has information all through it. I want to populate a cell in a new file with inform from the 12-07.xlsx cell B3.

I know the Formula is ='[12-07.xlsx]Data 12-07'!$B$3

Is there a way that I can use a cell within the current file, say cell B1 (which has a value of 12-07 - which is the same as the file I want) to populate the filename? Something like

='["=B1".xlxs]Data 12-07'!$B$3 (whereas "=B1" would be 12-07)

I know that will not work as is, but is this concept doable?

View 3 Replies View Related

VBA – Including Date In Filename

Mar 22, 2007

I’m trying to save a new workbook with the current date included at the end of the filename, in the format yyyy-mm-dd.

I took a guess at the following but it doesn’t work - the filename comes out as 'Report39163' Can anyone help please?

ActiveWorkbook.SaveAs Filename:="\UsersDataTemp" & "Report" & Format(Date, yyyy-mm-dd) & ".xls")

View 9 Replies View Related

PDF Macro With Auto Filename

Jun 3, 2008

The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:

' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.

Filename = "C:Folder1SubFolder1" & ActiveSheet.Range("InvNbr").Value & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application. The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False

' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here....

View 9 Replies View Related

Kill Filename Command

Jan 17, 2010

I've got a sheet that I need to convert certain rows into CSV for a payroll package. I'm using excel 2003 on XP.

I have worked out a macro to save it as a csv file with a different name. The only way I managed to do this was by creating an xls file with the data I wanted and then saving it as a csv file with a different name. But now I need to delete the intermediate xls file (called pre CSV in this example). I have the following code which seemed to work first time but is now giving me the error "Runtime error 53 - File not found" and highlights the. What am I doing wrong?

Sub Direct() ....

View 9 Replies View Related

Close Filename With Wildcard

Jun 22, 2006

I'm trying to close files which satisfy the criteria that the filename contains the word "monthlyreport". I tried the code below what it doesn't work at all. I'm also hoping in the future I can add on other criteria, e.g. contains the word 'monthly report' or starts with 's', maybe another If in the code? I'm really not sure..

Sub Close()
Dim wbMonth As Workbook
If wbMonth. Name Like "*monthlyreport*" Then
wbMonth.Close False

End If
End Sub

View 5 Replies View Related

Filename As Cell Contents

Sep 19, 2006

How can I have the filename stored in a certain cell of the worksheet?

View 4 Replies View Related

ADVERTISEMENT