I have a macro which works well. All it does is save a file to a specified location on my network. However, after a file is saved there is no prompt telling the user that the file was saved successfully leaving them wondering whether the macro actually worked.
VB:
Public Sub SaveAs() ThisFile = Range("X2").Value
ActiveWorkbook.SaveAs Filename:="legdcAudits" & ThisFile & ".xlsm", FileFormat:=52
End Sub
Excel 2010, Win 7. I have attached a .xlsm file to an email. The contained macros run perfectly, and one of them is "public" so it shows up in "Developer - Macros", and "Customize Quick Access Toolbar" lists.
If the recipient clicks and drags the attachment to a folder, or copy/pastes the attachment, it works fine. (Macros are enabled, etc.)
However, if the recipient right clicks on the email attachment, and selects "SAVE AS" from the item list, the file saves, has the right name and extension, looks ok (the icon has the exclamation point, etc), and it is the correct size, but it simply will not load. You can double click, do a file open, etc. but it will not load. It's a hidden workbook, but if you "open it" and Alt-F11 to show the VBA editor, it isn't there!
It's not stopping the project - we simply tell them to click/drag, etc. but I totally do NOT understand why that happens.
When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file. These other people can open this template (e.g. double-click, but not file-open). But when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?
If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's. Whatever did I make a file with macro's for? Is this a bug or is it done on purpose?
I am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.
Sub Macro1()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim sRange4 As String
Dim sFullPath As String
[code]....
Whenever updating an Excel file and saving it as a new file, a "Confirm Save As" window appears. The default is "NO" (No is Highlighted). Conversely, in MS Word, the default is "Yes" (and, YES is highlighted). I would like to change the default from NO to YES in Excel.
View 1 Replies View RelatedI have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?
View 3 Replies View RelatedI'm using Excel 2000 swedish version, that is we use comma to seperate decimals not dot. I have a macro that saves a sheet as .txt file (tab delimited). However in the sheet (journal voucher) I need to enter a couple of numbers all with 2 decimal, that is 5 is 5,00 and so on. But then, when I save the sheet to a .txt file using a macro all the commas are saved as dots and since I'm importing the file to SAP it will not accept the dots. Is there some way ( changes in macro) I can prevent the commas from becoming dots when I save the .txt file?
The save macro is the commomly used
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings& strText & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
I 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 macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
View 9 Replies View RelatedI presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?
Here is the current
Sub SaveIt() ...
i have a macro button designed to wipe all user entered data into a workbook. I want it so you cant accidently press (as you cannot undo the operation). Anyway for making a dialog box appear asking if you are sure to wipe date, with a yes or not option.. no will obviously abort the macro.
if possible, another box to come up asking if you want to save the file first with a different file name (comes up with a save as box).
this is the code so far:
Sub MonthCleaner()
'
' MonthCleaner Macro
' Macro recorded 17/10/2005
'
Application.ScreenUpdating = False
Range("A10:G10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("H10").Select .........
I have a button. When pressed, it runs a macro. Instead, after pressing the button, I would like the user to confirm to run the macro and display some info about it. This is to make sure the user does not accidentally run the wrong macro (button). I just need a prompt box, with a line info, and the two choices YES and NO. If NO is selected, nothing happens. If YES is selected, macro continues.
View 6 Replies View RelatedI have an excel form where users can input modifications by selecting a range and then pressing a command button to input a value in the selection.
My goal is: to restrict the location where a user can select a range of cell and then execute the macro . The cells where the user can use the macro are merged and in my code, I used the address as show in the address bar in excel for their location. If the user select a cell that is not permitted, a message box is displays and exits the code.
If (Selection.Address <> Range("AE7").Address) _
Or (Selection.Address <> Range("BM7").Address) Then
Msgbox ("Wrong location") Else
For some reason it doesn't work since the message box appears even if I am within the permitted locations.
Note: AE7 is the first cell of a merge, same as with BM7
I'm using Excel 2002 on and XP machine. I am creating a spreadsheet to track a work plan.
Column A: Name
Column B: Date
Column C: Time
Column D: YES
Column E: No
Column F: New Date
Column G: New Time
What I'm looking to do is input click able buttons in Columns D: Yes and Column E: No.
When a user clicks on YES, a pop up window should appear with the data in columns A,B,C.
The p0p-up should contain CONFIRM and CANCEL buttons.
Asking the user if they want to confirm the date and Time.
If CONFIRM is clicked Excel should then send an email to me and the user to say that the work schedule has been confirmed.
If CANCEL is clicked the pop-up window closes.
Secondly if the user clicks NO, a pop-up window should open with two free text boxes. One for Proposed date and One for Proposed time. And again 2 buttons for Confirm and Cancel.
If Confirm is clicked then Excel should record the data entered in the free text boxes into Column G and Column H and send emails to me and the user with the Proposed date and Time Data.
If CANCEL is clicked the pop-up window closes.
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
[Code] ......
Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).
CSV files are something like that (I need only first 4 columns):
ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"
and my Worksheet is like that (With a command Button):
I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...
ABCDEF1NameMSISDN DatedBlankBlank Location2
3
4
5
6
7
1. I can't remember it, but I know there is a command so that the view hold and that the selected tabs and cells does not show and the same view remains until the "unhold" is called in the macro. THEN the view is updated. What is this command again? Could not find it...
2. The macro needs to delete tabs at the click of a button (easy part) but for each deletion, a message appear:
Data may exist in the sheet selected for deletion. To permanently delete this data, press Delete. Is there a way to prevent the message from popping out when the macro is executed?
After I use a script, as follows (data must be present in C1) to continue, how do I display a message box "SUCCESS" after an successful save.
Sub SaveAsCell()
Dim strName As String, SaveAsFileName As String
strName = Sheet1. Range("C1")
If strName <> "" Then
If Right(strName, 4) <> ".xls" Then strName = strName & ".xls"
SaveAsFileName = Application.GetSaveAsFilename(InitialFileName:=strName, FileFilter:="(*.xls), *.xls") ..............
I've found afew examples off google searches, but my attempts to edit them arent working...
i want a macro to copy a range, say a1:e5, and paste the values and fonts/boarders/etc (ie no formulas) into a new workbook
the ones i found copy a whole sheet, i just want a portion....
I would like to 'save as' my current open file with a new name equal to the value in cell A1 of the active sheet, and save it to C:Apps. I would also like to save it is a pdf with the same name and to the same location. Would that be possible in the same macro?
View 4 Replies View RelatedI have a macro recorded that saves the workbook to my desktop and then saves an additional copy to a shared drive. Right now it wants to save my file as the same name every time and ask to replace the existing copy.
What I would like to tell it to do is make the save as name reference cell D10. Unfortunatley, I don't know how to tell it to do that in visual basic.
Is there a macro that automatically saves a backup of your spreadsheet every week?
View 3 Replies View RelatedI would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.
View 4 Replies View RelatedWould I be able to save a Macro button in a CSV File since every time I open spreadsheet it disappears and my system only accepts CSV.
View 7 Replies View RelatedSo I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.
Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.
The code I wrote to save a file with the last row counter and month counter looks as follows (this was working on my Mac)
ActiveWorkbook.SaveAs FileName:= _
"Macintosh HD:Users:myratriegaardt:Desktop:Quotes" & M & ":Quote" & LastRow & ":QuoteHG" & LastRow & ".xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
I converted it to put on Windows,
ActiveWorkbook.SaveAs FileName:= _
"C:UsersCape CratingDesktopQuotes" & M & "Quote" & LastRow & "QuoteHG" & LastRow & ".xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
This however gives an error.
The following macro is returning a 1004 runtime error.
FName1 is a book title PHOTOSHOP MADE EASY which is trimmed to remove blank spaces at the end.
FName2 is the date =Today() formatted dd/mm/yy.
When the 1004 error appears the filepath shown looks like this ...
Macro to save the worksheet every 5min
A macros is needed to:
1. save the worksheet under the same name
(& variation: under diff name - e.g. quotes_HHMM.xls ,
where HH is hour and MM is minutes of the time the file is saved)
2. every 5 minutes (the file is opened at 9AM and closed at 6PM manually)
I have a file, where the financial data is streamed in from Reuters.