Saving An Unopened Workbook In A New Folder
Feb 14, 2007
I would like to copy a workbook from one folder to another without opening the original. Obviously, tempname.SaveCopyAs doesn't work, can you suggest an alternative?
Sub gettemplate()
Dim tempname, tempcopy As String
tempname = Application. GetOpenFilename 'Choose the path of the original workbook
tempcopy = Application.GetSaveAsFilename 'Choose the path where the copy will be saved
tempname.SaveCopyAs tempcopy 'Want to save the original workbook into the copy file path
Workbooks.Open tempname 'Open the copy
End Sub
View 4 Replies
ADVERTISEMENT
Nov 30, 2009
I'm using a macro to try to pull data from a worksheet but it only works when its opened. I found a macro that is supposed to get data from an unopened worksheet but I don't understand VBA very well so is there anyone here that could tell me how to merge my macro with this one to allow me to access the unopened worksheet?
This is the website for retrieve data from unopened worksheets:
[url]
And this is my macro I'm trying to use:
Function HyperLinkText(pRange As Range) As String
Dim ST1 As String
Dim ST2 As String
View 9 Replies
View Related
Oct 14, 2012
Im looking to insert a formula in vba code that inserts the formula from a closed workbook to the active cell. At the moment i have this as an example of what i need:
VB:
ActiveCell.FormulaR1C1 = "='[SRS 5FFBEP.xlsm]7%'!RC"
However the file part in square brackets [SRS 5FFBEP.xlsm] and tab 7% i would like to replace with a link to a cell reference that contains the file path as this needs to change and update so i would end up something like this:
VB:
ActiveCell.FormulaR1C1 = "=('" & Workbooks(Worksheets("SRS Files").Range("d29")).Worksheets("SRS Import").Range("c10") & "'!RC)
Note that cell Worksheets("SRS Files").Range("d29") contains the full file path (in text format) and Worksheets("SRS Import").Range("c10") is the link to the tab that reads the 7% (in text format).
As it stands the first set of code works fine however i need it to be able to change as previously stated. So what i need is to replace the file name and tab with cell references
View 1 Replies
View Related
Jan 12, 2009
I have this code
View 10 Replies
View Related
Jan 10, 2012
I have a macro that saves the workbook after it has finsihed the rest of its formatting. I am finding it is saving in the wrong folder. I looked at my code and it is clearly states 'my documents' where I want it to save. Yet it keeps saving on a random folder on a shared drive.
View 8 Replies
View Related
Mar 29, 2008
I'm sure this will be an easy question, but I've been searching for awhile and can't seem to find an answer. I'm trying to write code in VBA that will check if a directory within c:documents and settings...my documents exists, and then save a file to that directory. The problem is, I'm having trouble finding the my documents directory. Is there some object or property that I could use to find the my documents path? Note that the worksheet that I'll be working with is in a separate directory, and therefore neither activeworkbook.path nor thisworkbook.path would be an option.
View 3 Replies
View Related
Apr 21, 2014
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies
View Related
Oct 26, 2012
I currently have a application that people fill out on a userform. When they click on a command button at the end it puts all that information on a sheet i made and then we print it off. My objective is when it saves it to the sheet it also save a copy of that paticular sheet in a seperate location such as H:/applications. Also the copy should be renamed to the information thats in a specific cell Such as j4 the have this text added after that "Application"..
View 4 Replies
View Related
Mar 3, 2013
I'm programing a VBA macro in Outlook 2007. I have added a button to the quick bar so when i open an email and push that button, a Excel file opens and i can input some data. Thing is, i need that if the opened email have some kind of attachment, when i push the button i want this attachement to be saved into a specific folder. Is that posssible?
View 2 Replies
View Related
Mar 21, 2009
I would like excel to create the folder C:/Bill if it doesn't already exist when I click save/save as option and save the workbook in it with a filename that is a combination of text/values in cells A1 and A2 on Sheet1. Better if the save as dialog box appears with this option selected so that I have to just click Save in the save as dialog box to save it in "C:/Bill".
View 9 Replies
View Related
May 22, 2009
I have a macro which saves file to a specific folder. Problem is I can save the file only in my local drive C: but not on a server for some reason.
Here is the
At home in my home computer this code worked when C: was selected.
ChDir ("c:")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
Save the file in specific place
But when I substituted C: path with my server path it doesnt work! Have a look. Its the exact same code except my path is different.
ChDir ("\Lnf001Lnf1vol1SharedOP_ENGWork Order")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
View 9 Replies
View Related
Jun 11, 2009
I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.
In fact, I need them to select two folders, one for this month and the other for last month.
I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.
View 9 Replies
View Related
May 30, 2014
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
View 3 Replies
View Related
Feb 21, 2014
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
View 4 Replies
View Related
Jun 17, 2014
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
View 2 Replies
View Related
Mar 20, 2007
I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).
View 2 Replies
View Related
Dec 4, 2008
I have an Excel doc with ~20 tabs. Each type represents 1 type of activities (soccer, tennis, etc). The size of the file (with the 20 tabs) is pretty significant (>1mb), which jams our email traffic.
Is it possible to:
- keep this workbook with the 20 tabs when open by the user;
- let the user select and fill in whatever tab he needs (just one at a time);
- have a button for the user to click that will save only this tab with a different filename (therefore, when sent by email, the file should be pretty small).
View 9 Replies
View Related
Nov 27, 2012
I'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
View 4 Replies
View Related
Jun 8, 2009
As I've learned to develop Excel apps with vba over the years I've noticed that, if the Excel app starts to get too large (over 1MB) saving it will often cause the application to crash for no apparent reason - the result being that I lose all the vba code I entered before the last save - with the worst case being that, on occasion I have lost all of the vba code in an applications!!!
So, I've learned to back up often using a third-party utility that I use to save my applications, which works wonders in allowing me to save my application without it crashing. The application is called Code Cleaner 97.exe. It was designed around Excel 97 (which I still use) but if possible I'd like to get an updated version for Excel 2003 and Excel 2007, which I also use.
Does anyone know of a more recent version of this utility, or another similar utility that allows large Excel applications to be properly saved, etc?
Code Cleaner 97.exe was a freebie that was available through one of the old Compuserve Excel forums. It was a very popular utility and was suggested as a means to save one's Excel apps without the app getting trashed, etc.
I'm not expecting a freebie solution so I'll gladly pay for something that allows me to save my large Excel apps without them crashing, etc.
View 2 Replies
View Related
Apr 5, 2010
Is it possible to rename a new workbook without saving it. What I do is I create a new workbook but I don't want to save it:
Sheets("Layout").Copy
Now Sheets("Layout") is standing in a NEW workbook called Map1.xls.
I want to rename Map1.xls to Invoice.xls without saving the workbook.
Then I do:
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close SaveChanges:=False
View 9 Replies
View Related
Oct 26, 2011
Ok, I'm trying to learn how to create and save a new workbook using VBA...having some issues with this error:
Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed.
The line is highlighted in red.
Code:
Sub Copy_ActiveSheet_1()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
[Code] .........
View 7 Replies
View Related
Dec 24, 2007
I Want A Code That Will Save My Workbook To A Specific File (different Form The One It Is Currently In) Using The Name That I Have Typed In Cell D13 In The Workbook
View 9 Replies
View Related
Dec 20, 2008
I am using this code in my ThisWorkbook, but it keeps coming back as an error for
ActiveWorkbook.SaveAs Filename:=ThisFile
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Saves file as what is in cell "C7" to specified network location - variant below
Dim Location, ThisFile As String
Location = "\Office-pcpublicCustomers"
ThisFile = Range("C7") & Format(Now(), "dd/mm/yy")
ThisFile = Location + ThisFile
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
View 9 Replies
View Related
Feb 6, 2009
When the button is pressed, It opens a Mesage Box giving an alert to the User about what is He going to do...if he clik no the macro stops, if the user click yes then it opens an InputBox ....and the name the user writes there will be the name of the new book...
If working good if the user press OK ...my problem comes If the User press CANCEL in the InputBox...The excel try to Save the file as FALSE...and I don´t know how to avoid it.
Here is the code.
Sub Botón1_AlHacerClic()
X = MsgBox("Warning Bla bla ".Are you sure ?", vbYesNo, Title:="IMPORTANTE")
Naa = Application.InputBox("Write the file name Here", "New File Name")
If X = vbYes Then
ChDir "C:UsersROBERTODocumentsBASES DE DATOS"
ActiveWorkbook.SaveAs Filename:=Naa, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
End Sub
View 9 Replies
View Related
Dec 14, 2009
i have a workbook which is mainly use in construction building design... i want users to input ther respective data in that workbook to calculate and design only, but i dont want the user to save the changes they made...
View 9 Replies
View Related
Mar 5, 2010
How do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below
HTML Sub Time()
Call Save
Application.OnTime Now + TimeValue("00:00:45"), "Time"
End Sub
HTML Sub Save()
ActiveWorkbook.Save
End Sub
View 9 Replies
View Related
Jul 5, 2006
I'm wondering if it is possible to prevent a user saving a workbook. If opened as a read-only, excel throws up an option to save with an alternative name. I'm wondering if I can put some code in the Workbook_BeforeSave event that prevents saving of the document unless the application.username is myself.
View 2 Replies
View Related
Mar 14, 2013
All I want to do is save a copy of one sheet called "Patient Stock Form", change the name to "Patient Order", and put it into an email. But my code is putting the entire workbook into the email. Here's the code:
VB:
Sub EMAILFORM()
Dim oApp As Object ' Outlook.Application
Dim oEmail As Object ' MailItem
[Code]....
View 8 Replies
View Related
Oct 29, 2009
Is it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
View 5 Replies
View Related
Jan 25, 2008
I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
View 9 Replies
View Related
