Have Code To Block Save But How To Keep Code In Workbook Without Saving The File

Aug 1, 2013

My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True


View 1 Replies


Auto-Save Code Not Saving

Nov 14, 2006

if someone would review the code below and tell me why it does not cycle an autosave more than once. It is supposed to autosave automatically every five minutes but for some reason beyond me it does not. I did not author this but picked it up in a different forum.

Option Explicit
Public vartimer As Variant
Const TimeOut = 1 'set in minutes to whatever amount of time is desired
Sub Timer()
vartimer = Format(Now + TimeSerial(0, TimeOut, 0), "hh:mm:ss")
If vartimer = "" Then Exit Sub
Application .OnTime TimeValue(vartimer), "SaveOpenWorkbooks"
End Sub

View 9 Replies View Related

Delete Code When Saving The File

Jan 23, 2010

I have posted already the problem to delete the code when sending a sheet via email and I have found the solution to the problem.

I tried to do the same when saving the file to specific folder, but i am facing problem.

View 6 Replies View Related

VBA Code To Generate Filename Without Actually Saving File

Jan 23, 2014

I have a file that I want to name based on content in certain cells. I.e. "Q-(text from cell G1)-(text from cell B7)-Date(mm_dd_yy)". I want to be able to specify the location where I save this file each time I try to save it, but I want it to update the file's name if any of the parameters have changed.

I have some code generated that does just about everything right except that it automatically saves a version of the file in the folder where the original is located even though it also brings up the dialog box and allows me to save it in another location.

Basically, I want to fix the code below so that it doesn't save what appears to be a backup file each time. Should I be using some sort of savecopyas?

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Add the quote # to the header
ActiveSheet.PageSetup.RightHeader = "Quote No. " & Range("G1").Value


View 6 Replies View Related

Adding Some Additional Code To Prevent Someone From Saving Workbook?

Aug 14, 2014

Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.


View 2 Replies View Related

Skip Saving The Code Everytime You Open The Workbook

May 8, 2009

I have a workbook with lots of code to automate several things that I do. It does a great job at doing things I need it to do, but it takes foreeeveeerrr to save the workbook. The code has made the workbook a large file, and it takes a few minutes to save it. Is there a way to skip saving the code everytime you open the workbook. I don't ever need to change to code so I don't need to save it every time.

View 3 Replies View Related

VBA Code To Require Fields - Temporarily Save File Email It Then Delete Temp File

Apr 9, 2014

I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.

Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]

Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _

[Code] ......

View 1 Replies View Related

Code To Save File

Nov 14, 2007

I have this code to save a worksheet into a specific directory:

View 13 Replies View Related

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View Related

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

Jun 25, 2014

I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.

Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")


View 2 Replies View Related

VB Code To Save Workbook

Feb 24, 2009

I have a code that saves my worksheet once it closed. At the moment it saves the Active worksheet which is Sheet1

The problem i have is i have a few vlookup formulas that reference hidden worksheets Sheet2 & Sheet3

Would it be possible to modify this code so it saves the entire workbook?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim bk1 As Workbook
Dim bk As Workbook
Dim myfilename As String
myfilename = "C:files" & Range("B10") & " - " & Range("J10") & ".xls"
Set bk = ActiveWorkbook
Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet)
bk1.Worksheets(1).Name = "Sheet1"
bk.Worksheets("Sheet1").Copy _
On Error GoTo 0
bk1.SaveAs Filename:=myfilename, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _

End Sub

View 9 Replies View Related

Using VBA Code To Save A Workbook

May 17, 2006

is it possible to use code to save a workbook? I know this is pretty basic, but for the life of me, I cannot answer this question searching the entire website and forums.

I'm currently creating what could be called a "yearly timesheet" for a small company and trying to automate it as much as possible. I've created several buttons along with input boxes to create the data, now I would like to make two more, one to save the worksheet (preferrably using data from one cell as the workbook name) and a second to send the workbook to the local, default printer. I've come across the topics on stopping Excel from automatically asking the user to save it, etc etc but none along the lines of "this is how you create a button that if clicked, saves the workbook."

View 9 Replies View Related

VBA Code For Webpage Save As MHT File

Feb 12, 2014

I want o write a vba code for below: I want to download and saves a list of webpages (and contents like images, animated GIFs, sounds, videos,...) as MHT Files (1 MHT file for each link from the list). In the below files there's an example of how it should look like.



I am familiar with the URLDownloadToFile function, but it doesn't download the webpage's contents. What I need is something like a URLDownloadToMHT function that downloads the entire webpage and its contents and packs it into a MHT file. The MHT file, obviously, needs to link the content files locally for offline browsing (I don't know exactly how a MHT file works, but what I mean is that src="http://www...file.jpg" should be replaced by src="file.jpg", something like that)

I am also familiar with softwares like WinHTTrack, that do something similar(with HTML files), but I think the downloaded files get too messy and disorganized, that's why I need something simple like MHT files saved in a folder of my choice. I need this Excel file to work in any Windows XP/Windows 7 computer, without dependencies from other files like third-party dll's or any other files.

View 2 Replies View Related

VBA - Code To Save File In New Directory Not Working

Oct 29, 2012

I have some code that copies a worksheet and saves into a new workbook and saves into a specified folder and renames the new workbook.

It is all working fine however it is saving to my desktop and not the specified directory. I know there is a lot similar out there and i have exhausted my google skills trying to find the similar format to the one i am after.


Sub MonthlyReset()
Dim sourceSheet As String
Dim clearR As Range
Dim newFile As String


View 2 Replies View Related

VBA Code So User Cannot Save Workbook

Oct 11, 2013

I have a workbook that is shared between 10 users and this work book is known as Master, what I don't want to happen is for one of the users to over write and save it. So is there a way one not allowing the user to save but allow the user to save as.

View 2 Replies View Related

Save Workbook As CSV Macro Code

May 19, 2008

I want is a Macro that will save the file as .csv, with the same filename and location as the original (just in csv format). A hotkey would be quite handy for this. I tried using the macro recorder and fiddled around with the code a bit but my programming knowledge is quite limited and I ended up with something that doesn't quite work as i'd like. I'm sure this would only take a few seconds to code for an experienced user, which is why I post here. I would post my attempt at doing it myself but i'm on a computer that doesn't have excel at the moment.

View 3 Replies View Related

VBA Code To Save File With Date As Reference To A Cell

Jun 19, 2014

I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.

ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51

But here i seem to missing the location

And I tried this one:

ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

but it keeps popping up as 'expected end of statement' for yyymm?

View 8 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
as a file named after B4n.

View 4 Replies View Related

VBA Code To Select Cell Range And Save As Text / XML File

Jul 19, 2013

I am trying to write the code to;

1) change to a different sheet
2) select a specific cell range
3) save that range as a text / xml file with a filename derived from a cell outside the given range

Here is where I have got to so far, but it fails

Private Sub Export_Click()
Sheets("Parsed Data").Select
ThisFile = Range("B1").Value
ActiveWindow.SmallScroll Down:=-15
SaveAs Filename:=ThisFile, _
Application.WindowState = xlMinimized

End Sub

View 2 Replies View Related

Workbook Save Not Working With Basic Code Included?

Feb 26, 2014

I am attempting to save a workbook when it opens, but when the code is run, it is not saving. For the purposes of finding the problem with the code, I have simplified it down to the following:

Private Sub Workbook_Open()
Range("C3") = "HEY"[code]......

View 8 Replies View Related

VBA Block Of Code

Apr 13, 2009

I have been out or writing code for a long time and my experience prior to that is very limited.

Rows "H" and "I" may contain the same words, "H" will be only one word, "I" will contain a string of words w/ the first word matching the word in "H", eg.
H6 = "Alien" (w/out "")
I6 = "Alien W. A.V.E (w/out "")

I would like to put a block of code that evaluates H6 to I6 and if they match then delete the first word and the space in I6.

This would be done over the range of H6:I and the last cell populated in "I".

View 9 Replies View Related

Excel 2010 :: VBA Code To Save File As Semi Colon Delimited Format

Jun 3, 2014

I am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.

View 1 Replies View Related

Get File Name From Workbook Housing Code

Dec 11, 2007

I have developed a database that is ported to other computer systems. My problem is that I have to hard-code the filename into the VBA code to identify the correct Application Object. I found that if I have activated another spreadsheet and run the code, it performs the actions on the wrong spreadsheet.

So my question is, is there a way to get the VBA code to automatically associate itself with the file in which it resides? That way, when I move the file to another computer, it knows what .xls file to use as the "Application Object".

here is what I have now (I replaced the actual paths with {x pathname}):

fname = "FCS Hardware Inventory - Cat Rev E - Ver 2.3.xls"
'fname = "FCS Hardware Inventory - Includes IVA Cat Rev E and JCPC - ver 2.3.xls"
'fname = "FCS Hardware Inventory - Cat Rev E - Ver 2.3 - for distribution.xls"

Set xlapp = GetObject("{1st pathname}FCS Hardware Databases and Catalogs" & fname)
'Set xlApp = GetObject("{2nd pathname}" & fname)
'Set xlapp = GetObject("{3rd pathname}" & fname)

Set xlapp = xlapp.Application

When I copy the file to another location, I manually uncomment the correct fname and xlApp name... a real pain, especially if I share this with a large community. I don't want to have to teach everyone how to do this every time!

View 3 Replies View Related

Error Code Selecting Block Of Data From Worksheet?

May 3, 2014

I am trying to select a block of data from a remote worksheet but I keep on getting the following error

Run time error '1004':

Application-defined or object-defined error.

I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"

View 7 Replies View Related

Saving File With Save As Dialog Box

Dec 14, 2006

I want to be able to have the Save As dialog box come up, but with a File Name already chosen, so that the user can save it as this File Name in any location he/she wishes. I can get the Save As dialog box coming up with my code, but I don't know how to automatically enter the File Name

View 4 Replies View Related

Vba Code To Update All Pivot Table In Workbook But File Size Is100 Mb?

Mar 18, 2014

,i have file with 100 mb size.

a) i need to open the workbook and

b)Refresh all pivot tables in all sheet.

c)Also there are many formulas in the workbook that needs to be updated as well.

if i do it manualy i takes more time.......for opening and refreshing and i could see status bar running for excel calculation for updating all forumulas.

View 1 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb


View 4 Replies View Related

Excel 2002 :: Does Saving File As Unicode Save It In UTF-8 Or UTF-16 Format

Jan 14, 2011

what format is used when you save an excel file as "unicode." I am using excel 2002 on XP.

I have been asked to provide a UTF-8 formatted unicode file for use by another program but I am not sure which formatting excel uses.

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

Code For Saving As A Template

Feb 17, 2008

I have some code and then
ChDir "W:MET Logbook"
ActiveWorkbook.SaveAs Filename:= _
"Micro Evaluation " _
& Format(Date, "(mm-dd) ") & Format(Time, "hh.mm.ss AM/PM") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
And I want this to save my workbook in the different location as a template. Only it saves the workbook as a regular excel workbook instead. Any help with what I can change to save it as a template?

View 9 Replies View Related

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