I use the following macro to save my worksheet. However, if I click cancel when the save box is open, then I am presented with an error. How do I get it to skip the error and show a message box instead that says "File not Saved!".
View 2 RepliesI have a workbook that I constantly am improving with new macros and VB routines. I sometimes have two versions available for use at the same time. I would like to create a macro or routine that will tell me if the workbook has been saved. A simple cell color change to indicate I've saved would be fine. Also, if I DO save the file and the cell color changes to indicate I have saved, I would like the cell color to change back if any change is made to the workbook. This way I will always know if I need to save or not to keep latest changes.
View 3 Replies View RelatedI do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
Macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date.
View 9 Replies View RelatedI found this for my problem (creating a macro that starts when the file gets saved): Private Sub App_WorkbookBeforeSave()
I tried to use it, but the macro didn't do anything when I saved the file.
I'm using a macro to open an other excel file to get data from it. this goes by Workbooks.Open Filename:=totalopen (totalopen =Filepath & filename earlier defined) Because the opening file can have the same filename, my question; is it possible to open the latest/newest saved file? So also look at the saving date/time of opening excel file.
View 5 Replies View RelatedI have written a macro but unable to make it work. The macro works but not the way I would like it to.
The macro has to save an excel sheet to a separate folder as a pdf format, a message box should pop up confirming the pdf file and then attach the same saved pdf file to an email.
Now the macro saves the pdf file where I need it to be saved, the msg box pop up and then the pdf file opens up on top of the msg box. If I close the pdf file the msg box which was hidden under the file is now shown. When I click "OK" it goes straight to outlook with the email addresses attached but the saved pdf file is not attached.
I've attached the script.
Sub Macro1()
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UserReportsPDF Reports" & Range("Q3").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
[Code] ........
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
how I can have the latest date a file was saved showing on the worksheet? For example, I open a worksheet that was last saved on Sept 22nd, and after updating it today, I save it and want the date to show today's date?
I'm pretty sure I've seen it in some files in my working life, so would appreciate advice on this!
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.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
I have created a template with a form. Now I'm using (the code is in ThisWorkbook)
Sub Workbook_Open()
frmMyForm.show
End Sub
This runs the form also when opening the template or a saved workbook based on the template. I want the form to run only when creating a new workbook based on the template. How do I do?
I have an add-in called GeoDesiX. It is a geocoder/mapping tool that uses Google Maps API. It works fine, even on my machine, but I think it was built using 32-bit, while I have 64-bit Excel. When I open the add-in for the first time, an example workbook is below, it works great.
The problem is, that when I save any file with the map in it, the VBA no longer works, and an error pops up in the below code that says: "Object Reference not set to an instance of an object". This happens at the Err.Raise 65000, "GeodesiX", Result part. It looks like it just cannot find the DLL file, which is the "GeodesiX" in that error part. I was reading that it might be caused by switching between 64 bit and 32 bit, but could not figure out how to do it. I am guessing you need to put Private Function Declare PtrSafe, but I could not figure out syntax on how to get that to work. I understand that you will not be able to use these as it is an add-in, but maybe you just know what I need to do in order for the vba to call to the DLL file.
I have a file that keeps setting it's "Saved" property to False when I change something in another file. I cannot find any external links in the file.
The file used to be "Personal.xls", but I renamed it.
I have a file with code attached and when I try to save it I am told that the file cannot be saved in this format and that I must unprotect the code before saving.
I have no OnEvent macros in the code.
Does anyone have a solution for this problem. The code must be protected, non-protection is not an option.
vba to save data in cells into file name? The vba below works for one cell, but I need info from cell “E11” also with the text MARK between them. myFileName = "2009 CROP BALE CUT OUT FORM – INVOICE " & Format(ActiveSheet.Range("F12").Text) & ".xls"
The final file name should look like this: 2009 CROP BALE CUT OUT FORM - INVOICE 8D4512 MARK CB34.xls
I have an excel file that gets updated every month but i only want one of the tabs to get saved automatically into a txt file when i click on a "finish" button or something like that.
View 4 Replies View RelatedI have a template that when it is opened it will automatically save the file to a specific location with the date in the file name.
Currently my code is
Private Sub Workbook_Open()
ActiveWorkbook.SaveAs "G:Shared"IT_New " & Format(Date, "mm-dd-yy") & ".xls", , , , , , xlShared
End Sub
Does anyone know a way to have this code disabled (either by deleting or making it all a comment) on the saved file (xls) so the speadsheet does not try and overwrite itself when I go back and open a previous days spreadsheet?
I'm not sure how I did it, but I opened up a new Excel file and accidentally saved it over a file I've been working on all afternoon. Is there any way to recover the original file (per the last time I saved it about 20 min ago)?
I looked in the "wastebasket" and only found files that I had specifically DELETED. But I didn't specifically delete this one, instead I saved over it.
I have this excel file that is 17 sheets long and I made a table of contents page so it is easier for people to navigate. I know my hypertext links in my file are correct because it works when I click on the hypertexted cells, my excel file jumps to the respective sheet.
However, when I save the file as a pdf the hypertext links do not work. how to get my table of contexts to jump pages in a saved pdf file?
I need the following sort code to run anytime the file is saved....I can't figure out what to title the sub and where to put it (ie. This workbook or in a module). I am attaching the file for reference. The file will not always be saved using the "save as" option - sometimes it is saved just by clicking the "save" icon
View 3 Replies View RelatedI have an Excell 2003 workbook with 6 worksheets and some VBA programmed macros in it.
With a small amount of data the .xls file size is 150k. Then I tested it with a much larger sample of data, which goes into one of the worksheets, resulting size 3MB. Later on I reverted to reading in the original small amount of data but the file size stubbornly sticks to 400k instead of reverting to 150k.
The old data is definitely not there anymore - I used this code to clear it:
if there is an easy way of showing the active file "Last Saved Date" on a Userform and have it show automatically when the Userform is opened? The Scenario is:
I have a "Main" userform screen and I want to have a text line which says, "File last updated on xx/yy/zzzz ". This must show up automatically when the "main" userform is opened.
I have a template excel file that has links to data in another excel file in the same directory as the template. However, I save the template with a new name in a new folder and the link no longer works. The link changes to the location with the new file and of course the file I am trying to like to is not in that location.
The link shows the full path in the template file so I would think it would keep the full path when saving to a new location but the file doesn't.
How do I make the link address static?
I'm looking for a code which is checking the file name first before opening and operating on it. File name format is filenameyyyymmdd.xls but the problem is files in that directory are saved irregularly (couple times a week) and I need to open the newest saved file.
View 2 Replies View RelatedWhen I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?
View 2 Replies View RelatedI was looking over web for a vba code that will open yesterdays last modified (saved) file in a specific folder.
In these folder is for eac day one file, so it has to open yesterday, not today - yesterday last saved (modified).
I was looking for a way to get external file froperties into a cell!
for example:
Last modified date.
this is what I allready found/tried.
I am trying to insert a link that once clicked will open a file (word, excel, powerpoint, etc. ) that has been saved on my computer.
View 3 Replies View RelatedI keep getting a "type mismatch error" for this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Len(Range("E18:E34")) = 0 Then
MsgBox "You must complete cell E18 thru E34 before you can save this file!"
Cencel = True
End If
I am trying to set a range of cells to ensure they are completed before the file can be saved. I can get it to work with individual cells, but not when I use multiple cells like in the example above.
This Works fine:
If Len(Range("D5")) = 0 Then
MsgBox "You must enter the Impress Amount before you can save this file!"
Cancel = True
End If
I want to create a communication excel sheet. I want it in such a way that if any one of the team member make update and save the file, then the whole team should receive an email that the file has been updated.
View 9 Replies View Related