SaveAs VBscript File Path

May 10, 2008

I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.

What I believe is the pertinent part of the code that needs adjusting is:

"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "

How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies

View 9 Replies


ADVERTISEMENT

Select A Path For ActiveWorkbook.SaveAs

Oct 13, 2009

I have a macro that creates and saves an XML file with a specific file name. Is there a way to modify this code in such a way that the user will be able to specify the path?

Set wb = Workbooks.Add

NewFileName = "Export For " _
& "Cust " & Customer_Data.Range("custName") _
& ", LOB " & RequestLOB _
& ", Type " & RequestType _
& ", Site " & RequestSite

On Error GoTo DoNotReplaceExistingFile

ActiveWorkbook.SaveAs Filename:=NewFileName & ".xml", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

View 9 Replies View Related

SaveAs Macro: Choose Path Name

Nov 21, 2006

I have the following
Private Sub cmdSave_Click()

SaveRow
On Error Goto ErrorHandler
ThisFile = Sheets("Data"). Range("B2").Value
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsMy Documents" & ThisFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks.Close
ErrorHandler:
MsgBox ("Error locating file. " + Err.Description)

End Sub

This saves a workbook with the File Name based on a cell in a worksheet in the above Path Name.

Is there a way that I can change this code to ask for the Path Name with the File Name, from the worksheet, as a default.

View 9 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

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

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

SaveAs To Same Directory But Different File Name

Feb 4, 2009

By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").

View 4 Replies View Related

SaveAs With String Extraction For File Name

Jun 16, 2014

I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.

Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant

Application.EnableEvents = False
Application.ScreenUpdating = False

[Code] .......

These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.

So as an example an excel workbook report generated in the folder:

"D:DropboxWorkVerification Reports!Test"

Would be saved as "!AirCheck AutoTest Report - !Test.xls"

or a report in the folder:

"D:DropboxWorkVerification ReportsHoliday Inn Surfside"

would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"

Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".

View 7 Replies View Related

Macro To SaveAs And Rename A File

Oct 15, 2009

I think my code is close but I keep getting an error "Run-time error '1004': Application-defined or object-defined error"

I am trying to rename the active workbook by appending yesterday's date onto the filename, using the Save As.

I might be going about this all wrong anyway. My user needs the macro to save, rename, and close the renamed copy of the workbook but to leave the original workbook open. I'm trying to do it in steps so I might be doing this the hard way. However, my code to Save As is below:

Sub SaveAsRename()

Dim CurrentPath As String
Dim CurrentFileName As String
Dim NewFileName As String
Dim Today As Date

Today = Int(Range("A3") - 1) 'Cell A3 contains the =Today() formula
CurrentPath = ThisWorkbook.Path
CurrentFileName = "QU Backhaul Dispatch "
NewFileName = CurrentFileName & Today & ".xlsm"

ActiveWorkbook.SaveAs Filename:=CurrentPath & "" & NewFileName

End Sub

My Locals window has all of the correct values right up to the ActiveWorkbook.SaveAs which is where it fails.

View 9 Replies View Related

Stop Saving File As Another Name. Prevent SaveAs

Dec 13, 2006

I already have a macro that on opening the workbook checks to see if the workbook has been renamed outside excel in windows and if so closes the workbook. I need a macro to intercept the save as command and prevent the user from saving the workbook as another name. I would however still like the user to be able to save. I would also like them to be able to save the workbook in another directory but only with the same workbook name.

View 4 Replies View Related

MACRO Saveas Keeping Current File Open

Nov 13, 2008

I have the following Macro that I run assigned to a Button.

Sub AskAndDo()
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD")
End If
End If
Application.Quit
End Sub.......

View 9 Replies View Related

SaveAs Worksheet To Another File / Folder While Keeping Original Open / Active?

Apr 15, 2014

I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.

My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV

My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.

View 3 Replies View Related

Automatically Open File In Specific File Path When Another Sheet Is Opened

Mar 20, 2014

I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls

So opening x.xls will automatically open y.xls

I've tried this in the workbook code area but it doesn't do anything.

[Code] .....

View 1 Replies View Related

Import CSV File Using Cell As File Name & Active Workbook Path

Nov 3, 2009

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.....................

View 5 Replies View Related

Save File As Text Using Cells For Path & File Name

Dec 20, 2009

How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names

pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................

View 2 Replies View Related

User Input For File & Path For File Copy

Jan 26, 2008

Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?

Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub

View 4 Replies View Related

Getting File-path From Selected Input File

Jan 29, 2014

getting full file path from selected input file?

I have a macro with text box and browse button.

User can click on browse button and it will allow them to select only text files.

Now for further processing I need to get the file path and file name in 2 different variables.

Like if user selected C:/Temp/Test.txt

Variable 1 should have C:/Temp
Varialble 2 should have Test.txt

View 2 Replies View Related

Copy File Name And Contents Of That File In A Specified Path?

Feb 5, 2014

I just found a code to copy file path and file contents. However it is copying folder path and folder contents.here is my requirement.I will specify a path, macro has to copy that file name in a particular cell, then it has to copy all its contents.
ex:

file name 1 has 3 sub files in it, file name 2 has 2 sub files in it--

column AColumn B
file name1file contents
file name1file contents
file name1file contents
file name2file contents
file name2file contents

And also i have extend this macro to write a index function. i will tell u about this. but first i need above thing I researched but I didn't find.

I need file name in a column then i need file contents in b column.

if a file has 10 file contents in a file then that file name should come 10 times in a column, and in b column all its contents.

View 14 Replies View Related

Split File Name From File Path

Feb 16, 2008

Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).

For example (Quater 1 is the document name)
R:ManagementJohn2008SalesSectionAQuater1

I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.

For example
R:ManagementJohn2008SalesSectionA

View 9 Replies View Related

Separate File Name From File Path

Jun 12, 2008

I am trying to separate a filename from a filepath in Excel VBA 2003.

I am using the Application. GetOpenFilename command to get the user to select the correct file.
However, this also seems to record the filepath.

I am trying to separate the filename out of this so that each file( name) activates a different sub procedure.
I am storing the result of the getopenfilename as a Variant.

At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.

View 7 Replies View Related

Get A File Path Using Vba

Jun 3, 2004

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.

View 9 Replies View Related

Use The Path Of My File

Mar 23, 2005

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

View 9 Replies View Related

Get Rid Of Path In File Name

Apr 26, 2007

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 (~),

View 9 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

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 Related

Variable For File Name Or Path Name?

Jun 25, 2014

How could i get the below code to work with a variable as the file name?

Or could I use something like "thisfile" to determine where to import to.

[Code] .....

View 5 Replies View Related

Relative File Path VBA?

Feb 28, 2014

I have a WinForm, which I'm calling via VBA's Shell command:

Code:
Sub Button1_Click()
Shell "C:TemplateFuncitons.exe " + ActiveWorkbook.FullName, 1
End Sub

I made this module an Excel Add-in and added it as a button in the quick access toolbar. Now, the idea is for the WinForm.exe and the Excel.xla to always be in the same directory (go hand in hand). I want to make a relative path for the .exe which will be the path to the folder where the .xla is + the string "TemplateFunctions.exe" in the end.

View 5 Replies View Related

Extract File Name From Path

Jan 10, 2008

I am having trouble with a macro of mine. Essentially it opens a file, based on user input (Full path is stored in variable FilePath), but then I want to select that workbook, and therefore need just the .xls filename. I figured I would create a new variable, FileName, to pull out the filename.

How can I edit the string in my macro to extract just the filename.xls from the full path?

View 9 Replies View Related

Return File Path

Dec 2, 2008

I have a UserForm with a button that, when clicked, opens up a File Directory Browser that the user can select the file of their choice.

The code is below :

Private Sub DirectoryBrowser_Click()
Dim fn
fn = Application.GetOpenFilename 'can add parameters. See help for details.
If fn = False Then
MsgBox "Nothing Chosen"
Else
MsgBox "You chose " & fn
'now that you have the name, you can open it or do something else
End If
Workbooks.Open fn
End Sub

What I would like is the filepath (of the file they choose) to appear as the displayed caption on another button or label.

View 9 Replies View Related

File/Path Error

Nov 19, 2009

We have built quite a large Excel based program that contains quite a lot of coding. the program seems to work fine the majority of the time but every so often we recieve an error message that causes excel to not save, or to crash and shut down.

The message that comes up is "File/Path Error"

Now I've just done some hunting around online and some links I have found speak of declairing the document location correctly in the coding. I was wondering could this be the issue at hand?

Anytime the document needs to save, there is a cell with the directory path and file path declared as a variable (Actually as Dim String) within the coding. Is this the best way to declare a file path?

Also, the problem seems intermittent. We Compile the VBA coding before saving, we also use a Clean Project add-in within Excel. These processes usually help but every so often the problem comes back.

View 9 Replies View Related

Run A .bat File From A Macro (using A Relative Path)

Dec 3, 2008

How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved