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


ADVERTISEMENT

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

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

Possible To Choose Path Of Save As Window?

Apr 1, 2012

I am currently using the following code to lauch the "save as" window.

Application.Dialogs(xlDialogSaveAs).Show

But I would like it to default to a poticular folder not the one of the current file location i.e. C:my documentswork

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

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

Macros Running Twice: SaveAs Macro Stops

Jan 28, 2009

I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.

1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.

2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.

My specific question is about no. 2 and the following are the individual macros and the macro that runs them.

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

Macro To Hide Cut,copy,paste,save,saveas Option In Word

Feb 15, 2007

I want to restrict users to just read or print a word document. Document is very confidential so we dont want them to copy or manipulate the document.

I thought of giving password to restrict to read only option but that still lets users to copy and paste on to a new document.

View 9 Replies View Related

Macro To Choose Between Different Formulas?

May 8, 2013

I have two formula's available for a cell. Its actual value, and a previously forecasted value. The actual value is found using an index formula and the forecasted value is a forecast function. i want to create a macro and link it to a button such that i can change which formula used in the cell accordingly. Is this possible?

View 1 Replies View Related

Macro To Choose First Immediate Date Of Each Individual

Feb 21, 2014

I have a group of huge data as follows. I want to create a macro to just choose the first immediate date of each individual (in another word hide the second rows onwards for each Name). How can i do it?

Name
Travelling date

A
2-3-2014

A
4-4-2014

A
5-7-2014

B
3-3-2014

B
8-9-2014

View 7 Replies View Related

Excel Macro That Prompts To Choose File To Hyperlink

Jun 12, 2014

I would like to write a macro that would insert a hyperlink to another file an would prompt the user to select the location of the file once the macro was run. I am not sure how I would get my macro to prompt the user to define the location (which is located on a network drive).

View 7 Replies View Related

Macro: Allow User To Choose Workbook/File To Open

Sep 8, 2006

I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.

View 4 Replies View Related

Macro - Choose From Dropdown List Then Copy Page To New Sheet

Jan 3, 2013

I am after some VBA code that chooses each value from a drop down list (in cell A1) then copies a separate sheet 1 (sheet 1) into another workbook (can have both workbooks open at the same time or the first step will have to create a workbook) with the name of the added sheet renamed to the value chosen from the list. When the second value is chosen then sheet 1 should be copied into the newly created workbook as well.

Sheet 1 has lookups that are updated when the new value in the drop down list is chosen

There are around 85 values in the drop down list so I'm expecting a workbook with 85 tabs (copy pasted values and formats)

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

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

Macro To Save As Using Path In Worksheet

Apr 2, 2012

The macro is working fine but what I want to change is the path for saving the file. The path that I would like it saved to is in the worksheet "NEW ORDER_" cell "N2"

Sub RENAME2()
Dim strWbKill As String
Dim myName As String
myName = ThisWorkbook.Path & Application.PathSeparator 'copy to same folder that file is in
myName = myName & Application.Cells(3, 7) & "_" 'cell G3 Style

[Code]...

View 5 Replies View Related

Macro That Will Open The File Path

Dec 10, 2008

hi, i need a macro that will open the below file path, and when i select the file i would like the macro to copy the active sheet from the source file being opened from range A2:L10000 and then paste special values to destination CELL A16 of the activesheet of the workbook.

I would like the date format for the below to be generic so it will apply for all months and years.

S:RECSBANKRECNominees200812-2008

View 9 Replies View Related

Macro For Relative File Path

Nov 23, 2006

I have a macro in excel that saves to an external text file, but the excel workbook (and the text file) will be copied to different folders so i need it to alway read the file from the location the worksheet was opened from. I hope that makes sense

Ive tried using ActiveWorkBook.path but that doesnt work, maybe im just using it the wrong way

View 5 Replies View Related

Macro To Save Xls To PDF With Path & Naming Convention

Mar 31, 2014

I am using the below macro to save my workbook with in the correct path and naming convention.

In fact, as I also need a pdf file from that excel file, I tried to change the below macro and replace ".xlsm" by ".pdf"... However acrobat does not like it..

HTML Code: 

Sub PDF()
'created by Gregory Charbonnel
'ActiveWorkbook.SaveAs Filename:="Z:FOLDER & FILE MANAGEMENTREview (ETF)PDF ETFETF_" & Format(Now(), "dd_mm_yyyy") & ".pdf", _
' FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

I guess that I should use the print option which gives me that when I record the macro. how to implement my naming convention + path in that macro.

HTML Code: 

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

View 2 Replies View Related

Remove External Path From Macro Assignments?

Jul 15, 2014

I need to remove the external paths in my macro assignments. I only use shapes. (no active x/form controls)

I tried that, but it won't work for me: [URL]

example of an assignment: 'G:10_excel oolshowPPIupdate.xlam'!button_last12 => should be button_last12 after i run the desired macro

View 4 Replies View Related

Check File Path Of Before Running Macro

Mar 26, 2008

I have a spreadsheet with a macro that saves a backup s/s to a particular folder, but the problem I have need the macro to check the filepath is that of the original s/s before running.

Its so that when people take copies of the main s/s and save them to their desktop I dont want the copies being saved in the backup folder

macro is below

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ActiveWorkbook.ReadOnly Then Exit Sub

If MsgBox("Do You Want To Save A Backup?", vbYesNo) = vbNo Then Exit Sub

Dim fso As Object
Dim objFiles As Object
Dim myWkBk As String
Dim myFName As String
Dim BkUpDir As String
Dim CountFiles As Integer
Dim mydate As Double

BkUpDir = "k:singstatappealsappeals trackerBackups"

Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(BkUpDir).Files

On Error Resume Next
'the actual counting of files takes place here.
If Err.Number 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If


If CountFiles > 4 Then
MsgBox "You have " & CountFiles & " saved workbooks. Perhaps you should delete some?"
End If

mydate = Format(Now(), "ddmmyy")
myWkBk = ActiveWorkbook.Name
myFName = ActiveWorkbook.FullName

fso.CopyFile myFName, BkUpDir & mydate & myWkBk

End Sub

View 9 Replies View Related

Open Workbook Macro With Variable Path

Sep 15, 2006

I am having trouble getting a macro to open a specific file from an open workbook in the same folder based on the current path of the open file. The application is this... I have two files in the same folder. The first is for data entry and it contains a macro button that will open the second which is a template to which the data in the first will be copied. I can easily do this with a fixed path to the second file, however, I would like to be able to move or copy the folder containing these files to other locations or computers without having to change the path each time. I would like to base the path to the second file minus the file name on the first (already open file) path. Assuming that the first file containing the desired macro button is already open, here is one of many of my attempts...

Workbooks.Open " ActiveWorkbook.PathJob Tracker -.xls"

Assuming the second file is the active workbook, I achieved a similar path assignment when saving the file...

ActiveWorkbook.SaveAs "Application.Path/Jobs Active/" & "Job Tracker -" & Range(" '[Job Tracker -.xls]Specifications'!$A$6").Value

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

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."

Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete

End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................

View 5 Replies View Related

Save File Using Cells For Path & Name Macro Code

Mar 19, 2008

I want to create a macro that saves my file this way:

in a folder named after B2
and
as a file named after B4n.

View 4 Replies View Related

Macro To Remove External Reference Path From Buttons

Jun 11, 2008

I have a little boo-boo I need to fix. I have a customer that has about 100 workbooks. I needed to add some functions to them so I made up a new sheet of forms and buttons as well as a series of new macros to bring into these workbooks. I knew I couldn't import the macros, so I added the module containing those by hand to all workbooks. I then imported a master copy of the new sheet from a master file i use to hold my working macros. The problem is the buttons on the new sheeets in each workbook now references the master workbook for the macro name, even though each workbook has the same macro of the same name in itself.

My question is, can I make a macro that will edit all the buttons on a specific named sheet (maintenance is the sheet name) in a workbook and take out the external reference.

For example, instead of the assigned macro pointing to c:master.xls!macro1 i just want it to call macro1. And yes, I can go into them and do it by hand. Do I want to manually edit 100 workbooks for a dozen buttons each? No. I want to automate it if I can. If anyone can help I would greatly appreciate it. It would save me a bunch of time.

View 9 Replies View Related

Macro To Append Multiple Xls File In Single Workbook From Specified Path

Jan 25, 2010

I need a macro to do the following

1.Get list of .xls files from specified folder.

2.Append the files in new workbook in same folder.

Ex:
D:/users/excel/Sample_sheet1.xls
D:/users/excel/Sample_sheet2.xls
D:/users/excel/Sample_sheet3.xls

I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.

Actually my macro as below

View 10 Replies View Related

Macro To Save As File To Current Daily Folder, Path Changes

Aug 6, 2008

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.

View 9 Replies View Related







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