Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Spaces In Path And Filename


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?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
To FIND The Filename In A Path
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 Replies!   View Related
Trim Full Path To Filename Only
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 Replies!   View Related
Get Filename And Path Of Workbook The Macro Is Running In
Is there a way to find the full name and path of the workbook the macro is running in?

View Replies!   View Related
Define Path & Filename As Variable
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 Replies!   View Related
Amending The Filename In Cell("filename")
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 Replies!   View Related
Trim Full File Name & Path To File Path Only
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 Replies!   View Related
Use ComboBox Value As Filename
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 Replies!   View Related
Filename As Cell Value
Is there a way to take filename and place value in a cell

View Replies!   View Related
Save The Filename
I have a problem with the following code

View Replies!   View Related
Get Filename With Formula
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 Replies!   View Related
Filename Retriever
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 Replies!   View Related
Add The Date To A Filename
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 Replies!   View Related
Displaying A Filename In TextBox
I want to use this code to display only the filename + extension in the TextBox1. It currently shows the full path

View Replies!   View Related
Date Appended To Filename
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 Replies!   View Related
Alternatives To CELL (filename)
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 Replies!   View Related
Creating Filename From Cells
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 Replies!   View Related
Formula To Get The Date From Filename
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 Replies!   View Related
Change Filename To A Name In A Cell
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 Replies!   View Related
Add Date To Filename
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 Replies!   View Related
Filename = Cell Contents
I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.

View Replies!   View Related
Create Unique Filename
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 Replies!   View Related
Kill Filename Command
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 Replies!   View Related
Variable In Filename Reference
I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?

For example: ...

View Replies!   View Related
Close Filename With Wildcard
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 Replies!   View Related
Find Filename In Folder
I have created a separate SAVE button that when the user hits that button, it saves the file in a network drive using the current date as part of the save-file name. Ie: Sheet20070408.xls, Sheet20070409.xls, Sheet20070410.xls, and Sheet20070411.xls.

I am currently working out all the "idiot proof" safety features. My code looks something like this:

oldfilename = ActiveWorkbook.FullName
newfilename = "C:NetworkFolder" & format(date, "yyyymmdd") & ".xls"
ActiveWorkbook.SaveAs Filename:=newfilename
ActiveWorkbook.SaveAs Filename:=oldfilename

Why am I saving it twice? When the user clicks SAVE, I want to save over the copy of the current sheet they use, even if they save it on their desktop, and then save it to the network drive. This way, if they keep a second copy in an easy to find location, it will save the data to that sheet as well. Just in case they forget to save on the current sheet. I need to be sure each copy of the file has the most up-to-date information, as well as save a copy for retention.

My question is this: Can I search a folder for an existing file name? If the user goes into the network drive to open an old file for retention and they click SAVE, I don't want to accidentally overwrite a file in the retention folder.

View Replies!   View Related
Filename As Cell Contents
How can I have the filename stored in a certain cell of the worksheet?

View Replies!   View Related
Error-prooofing Get Open Filename
I am trying to skip over any errors associated with the GetOpenFilename window, specifically where the Cancel button is selected, or no files selected. In the code below, I get an error "Object required" at the line shown every time this is run, even when the file path is valid and correct, and then the msgbox appears as a result of the On Error statement .

myExcelFilePath is dim Variant

On Error Resume Next
myExcelFilePath = Application.GetOpenFilename("Excel files (*.xls), *.xls", , "locate Report.xls....")
If myExcelFilePath Is Empty Or myExcelFilePath Is Null Then


View Replies!   View Related
Saving Filename From Cell Text
When the user goes to File, Save As, I would like the filename to be populated from the contents of cells B2 and B3, i.e. 11/18/09-23. Is this possible? Also in the same code, can I specify that I want the destination folder to be K:Sheets?

View Replies!   View Related
Split String Variable And Use Some In Filename
I have a filname called ex: "Statusreport xxyyzz.xls"

I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.

How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.


View Replies!   View Related
Copy A File By Specifying A Partial Filename
I'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.

Each of these files has a filename such as:

RAH_MRL_TOP_40_(ddmmyy_TIME)

with time being the time that the report was run.

Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.

Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.

View Replies!   View Related
Hyperlinks With # In Filename Wont Open
Is there any solution to the fact that hyperlinks in excel with a # in filename wont open?

View Replies!   View Related
Error 53 At Open Filename For Output
I try to make a macro for saving an Excel sheet to a .CSV file with "@" as delimiter.

At the line "Open fname For Output As #fnum", the Error 53, "no file found" occurs.

The Excel-help is saying that if the file is not present, a new file will be made.

This error occurs now and then, How to proceed?

Full macro:

Sub SaveAsCSV()
Path = "O:actueel"
Fname1 = Path & Range("E2").Value & ".csv"
fname = Application.GetSaveAsFilename(Fname1, "CSV bestand (*.csv), *.csv", , "")
If fname = False Then
MsgBox "Macro Geannuleerd"
Exit Sub
End If

View Replies!   View Related
Loop Through Files, Using Filename As String
I have some code that I use to convert files, and it works well, but I would like to make a couple of changes to it. If I run it now it requires user input for every file that is processed, using the input box. Instead of this I would like to use the filename minus the extension as this input.

The next step is selecting the file using the . Instead of this I would like the user to browse for the folder containing .csv files, and select the folder, then use the macro to loop through all files in the folder.

View Replies!   View Related
Workbook To Check It Filename Once Opened
I am trying to ensure that m work book keeps the same name even when I email it to others.

I have been told on this forum that it is possible for a workbook to check its own name once opened?

I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.

The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.

I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example

I also need this to open if it is an archive

if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.

View Replies!   View Related
PDF Macro With Auto Filename
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 Replies!   View Related
VBA – Including Date In Filename
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 Replies!   View Related
Export To HTML Filename And Filepath
I need a macro to export a range of cells to .html. The Save as Web Page works fine for me except a few thing that I need to automate. The macro I use now looks like this (recorded):

View Replies!   View Related
Add Filename To Merged Cells
I have the following VBA script that merges 2 cells together, but at the end of the merge I need it to add the filename and path of the active workbook.....

Sub Merge()
Dim Cell_e, Cell_Sheet1 As Variant
Dim R, C As Integer
'This Macro is add the Test condtion and the precondtions together in 1 cell, before they are exported to TD

Sheet2.Select
R = 11
C = 9
Do Until Sheet1.Cells(R, 9).Value = ""
Cells(R, 9).Select
Cell_Sheet1 = Sheet1.Cells(R, 9).Value
Cell_e = Sheet2.Cells(R, 7).Value
Sheet2.Cells(R, 7) = "Test Condtion: " & Cell_Sheet1 & Chr(10) & Chr(10) & "Test Preconditions " & Cell_e
R = R + 1
Loop

End Sub

View Replies!   View Related
Pass Filename To String Variable
Have a file lets say is named

sample.xls

Does anyone know how to retrieve the name of the file in VB?

just the name and put it in a string?

View Replies!   View Related
Cell Function With FileName Changes Sheet Name
Excell 2003

I put = cell("filename") in cell A1 on every sheet to display the full pathname of that sheet.

it seems that the path does not always display the correct sheet name- rather it seems
that when changing from one sheet to another the path is not always updated.

Is there a switch that needs to be activated to ensure that the path is updated correctly when changing sheets on the same worksheet?

View Replies!   View Related
Filesearch Filename Wildcard Before Specified Keywords
I've gotten the Filesearch function to work in VBA when using a wildcard after the keywords like this: "SomeFile*.xls".

But I can't get it to work with keywords after the wildcard character like this: "*someFile.xls"

View Replies!   View Related
Linking To Workbooks Whose Filename Is In A Cell
One folder on the drive contains excel files, which are created daily from a template. I use these files, the 20 most recent days' worth, to create an analysis file. This new file is organized in rows, starting with the date. The files I am using are all saved in the form "Stats yyyy mm-dd.xls" The first line of each row of the analysis spreadsheet starts with the date, in the format "yyyy mm-dd". Is it possible to create a macro, or in another way, for the analysis spreadsheet to automate the reading-in of data from these different files?

View Replies!   View Related
GetOpenFilename Partial Filename Filter
I am using the GetOpenFilename method to allow the user to select a file to open:

vaFiler = Application.GetOpenFilename _
(FileFilter:="Adobe PDF Files (*.pdf),*.pdf", _
title:=Description, MultiSelect:=False)

What I would like to do is 'pre-load' the filename the user can select. They will be picking a file to attach to a part as a datasheet. Almost every single datasheet in the library has the part nuber as part of it's filename, so to make it a little quicker (and more accurate), I want pre-load the filename selection window with *partnr*.pdf. If nothing shows up, the user can delete filename I have entered for them and see all files in that directory.

View Replies!   View Related
Needed Function To Display Filename In A Cell
I am running excel 2003 and I am looking for a function that will display the filename or part of the file name in a cell. I know you can do this as part of a header or footer but is there anyway to have an individual cell display the file name of the document?

View Replies!   View Related
Link Filename Of Workbook As Text In A Cell....
I have a workbook that gets saved many times with many different filenames.

All I want to do is have cell A1 display as text the name of the file.

eg similar to the date function, there should be a = filename function .

View Replies!   View Related
Create New Workbook And Save Filename From Cell Value
Im trying to create a macro that when i click the button a new workbook is created and saved as the value from C5 cell.

View Replies!   View Related
Copy Files Based On Part Filename
I have code that takes full cell with filename, looking for it in certain directory and copying to another directory.

Sheets("Unique").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

For Each rng In Selection
If rng.Offset(0, 4).Value <> "" And InStr(1, rng.Offset(0, 4).Value, "http") = 0 Then
FileCopy SourcePath & rng.Offset(0, 4).Value, ThisWorkbook.Path & "" & DestPath & "" & rng.Offset(0, 4).Value
End If
Next rng

I want to change the code that it will copy based on number. Any suggestions?

Example:

I have range of filenames.
10989478.MEC0388A-L0_RoHS_globemotors_1.pdf
10989495.Keystone_M1.4cat. zip
10989559.MEC0388A-L0_RoHS_qualtek.pdf
I want to copy files starting with 10989478, 10989495, 10989559. I mean ignore the part after "dot".

View Replies!   View Related
Countif- Created A Filename, (merging Cells Together)
i can do this using countif, however here's my problem

I have created a filename, (merging cells together)

What i would like to to is have the countif, use the created name, look in this file, for the criteria i set

I can do this manually, but there's a hell of alot of manual intervention involved & i would like to automate

If i was to do this manually my formula would be

=COUNTIF('G:RawProduct ManagementJen DelaneyVendor ReportsLexmarkWeekly Stock And Sales2007WK 22[WeeklyInventory-ISATradingLtd-UK-22-2007.xls]Sheet1'!$F:$F,B3)

I have automatically generated the part of

'G:RawProduct ManagementJen DelaneyVendor ReportsLexmarkWeekly Stock And Sales2007WK 22

Using various formulas, the problem lies when i try and integrate this into the countif function, it wont accept?

Basically i have a list of 6 part number, for which i need to check 10 weeks of data, 3 files for each week,

View Replies!   View Related
Print Header With Filename Without .xls Extension
is it possible to choose $[file] in custom header area but only have the file name, before the xls extension print?

Also, is it possible to have the file name print in all caps? (or would the file have to be named in all caps?)

View Replies!   View Related
Make Formula Pick Up Filename From A Different Cell...
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32

There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved