Save Copy As Same Path And Name

Jul 1, 2008

I want a Macro which will copy my workbook and paste it into a new workbook. Then it will automatically save it under the SAME name as the original and the SaveAs Box must show up (for cosmetic fixes). It is important it asks if it wants to save it under the same name. Example: My file is called "Summary_2008" and when I hit the Macro it will do those things and it will ask me if I want to save it in the SAME directory under the name "Summary_2008" (I will only add a number then).

(The macro must know how to read the original name since I have 7 possible filenames - it depends on which company I choose, that is why it is important it is saved under the same name)

Sub Zamenjaj()
Worksheets.Copy
Dim WS As Worksheet
For Each WS In Worksheets
Zadnja_vrstica = WS.UsedRange.Rows.Count
Zadnji_stolpec = WS.UsedRange.Columns.Count
For rwIndex = 1 To Zadnja_vrstica
For colIndex = 1 To Zadnji_stolpec
With WS.Cells(rwIndex, colIndex)
If WS.Cells(rwIndex, colIndex).HasFormula Then..........................

View 3 Replies


ADVERTISEMENT

Save Workbook Copy To Same Path As Original

Aug 27, 2007

I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:

Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub

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

Save Path Is Incorrect

May 27, 2009

I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?

Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

On Error GoTo ErrCatcher
Sheets(Array("Copy Me", "Copy Me2")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues....................

View 2 Replies View Related

Save .txt To .xls Using Existing Name And Path

Apr 18, 2007

how do i save a text file to xls in code. i want the new .xls file to appear alongside the original .txt file.

View 9 Replies View Related

Take Save Path From Cell

Dec 15, 2009

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 Related

VBA To Save As In Specific Path With Prompt

Oct 6, 2011

I'd like a macro that will:

Save my workbook
Then
Open the SaveAs box with the path already pointing to a specific directory and with the Name and file format already entered (.xls)

This is basically to save time navigating through our folder structure (which is a real mess). I don't want to automatically saveas the file as the target folder will change each month and the last part of the file name will chance (month). So I'd like a bit of flexibility rather than total automation.

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

Save Sheet With Same Path As Source Workbook

Dec 28, 2006

I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following

Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub

to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be

ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value

but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.

View 4 Replies View Related

Default Save As To Workbook Drive & Path

Jan 5, 2008

Excel 2002 SP3

When a file is currently open, and the user selects "Save as", it takes them to the files native directory. (Where it was last saved).

If the user has that same file open, opens another file from a different directory, goes back to the first file and selects "Save as", it takes them to the location where the last file was opened from.

View 9 Replies View Related

Allow User To Pick Save As File Path

Feb 6, 2008

i try to write a code to pop up input box with browse button to select the file path
the code without browse button is workin corectly

Private Sub CommandButton3_Click()
Dim N As Integer
Dim Fname As String
Fname = InputBox("Please enter the file name and path to save the output", "enter file name")
Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy
ActiveWorkbook.SaveAs Filename:=Fname
ActiveWorkbook.Close
End Sub

Need to add browse button to make it easier than writting the file path

View 4 Replies View Related

Can Use Asterisk Wildcard To Save File In Multiple PATH?

May 30, 2014

Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:

Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C

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

Macro To Save XLSM In Defined Path With Year Day And Month Changed

Jan 31, 2014

I need to save a document in the following path: [URL] .......

Only the year month and day sould change when I hit the save button/macro: 2014_01_29

How to build a macro that would save my file as per my above explanation??

If I would run the macro today the excel file should be saved in the path: [URL] .......

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

How To Copy Module(s) To Workbook In Different Path

Jun 21, 2013

Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA.

I need to copy some Module(s) from One WorkBook to another using VBA..

I have used the following code to good effect, however my TargetWb is not at the same path, so I need some modification in this code if that's possible and also this VBA Module which is Copied and Pasted in the Target WOrkbook needs to be Password Protected so that it cannot be tampered.

The code which I'm using currently is:

Code:

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _

[Code]...

Code:

Sub Try()
Call CopyModule(Activeworkbook, "Module1", TargetWb)

on this to pass the TargetWb as a variable which can be generated in the code used for generating the File using a Macro..

End Sub

In the above code The SourceWB would be the ActiveWorkbook and the Target Workbook is the file created using another Macro from several sheets of the Active Workbook and its created in a folder with a specific name and therefore the Path is one more level inside than the Active Workbook.

if the Transferred Module can be Passworrd Protected and Locked for editing..for the end user.

View 9 Replies View Related

Copy File To Specified Folder W/out Path

Mar 4, 2010

Is there a way for excel vba to find a folder named "MyFolder" in Drive C: and copy/paste a file into that folder without knowing the entire path?

Example:

Workbook("Myfile.xlsm").copy

"C:" Pathfile "MyFolder" . paste

View 9 Replies View Related

Copy Formula To New Worksheet Without Path

Apr 5, 2007

I am copying a sheet to paste into a new workbook. The Sheet contains formula and numbers so I figured that a xlPasteFormulas should work. However the number data gets pasted across fine but the formula get pasted along with their path.

I.e. ='Add Detail Client Delivery-Bmth'!C6

is copied an becomes:

='C:Documents and SettingsJPDesktopTest[Weekly Metric Template 04_02_2007 Bournemouth.xls]Add Detail Client Delivery-Main'!$C$6

Here is the basic code i'm using:

Set Nws = Sheets(shtName)

Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & Sitenamefull & ".xls")

Set Ws = WwB.Worksheets(14)

With Ws. Cells.Copy
End With

Nws.Cells.PasteSpecial xlPasteFormulas

View 9 Replies View Related

Save Back Up Copy Online At Each Save

Mar 27, 2008

I have a sheet which I would like to back up online every time it is saved.

I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.

It should be very easy to combine the two: what I tried was:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub

Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.

After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.

My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.

I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).

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

Copy Folders To New Destination Based On Path In Cell

Sep 27, 2013

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

View 9 Replies View Related

Copy Files To New Folder Path Based On Cell List

Mar 14, 2008

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?

View 3 Replies View Related

Macro To Copy Folders To Path Of Open Workbook Based On Cell Values

Aug 3, 2014

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.

View 2 Replies View Related

Open Workbook Where Named Cell Contain Name & Path, Copy Range And Paste Values

May 19, 2009

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.

View 5 Replies View Related

Copy Of Active Workbook Path Stamped Onto Spreadsheet With Date And Time / Edit Check

Feb 1, 2010

1) I need to add an edit check
2) have a copy of Active Workbook Path stamped onto spreadsheet with date and time to create a visual record of where the file has been saved (described after the code below).

1) I need to verify that two cells (S7 and S9) are not blank before running my code below (=IF(OR(S7<>"",S9<>""),RUN CODE,"You must select your Provider or Division before you can save this document")).

- If both of these cells are blank a message box should notify the user that they must select the provider and/or division before they can continue with the save.

- If one or more of these cells are not blank the code below should run.

View 3 Replies View Related







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