Macro To Save As "xlsx" |
Macro To Save As "xlsx" - Excel |
|
Sub SaveAs()
Dim FName As String
Dim FPath As String
FName = ActiveWorkbook.Sheets("DATA INPUT").Range("C2")
FPath = "C:\Documents and Settings\User\My Documents"
ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName & ".xlsx"
End Sub
It will allow it to be saved as "xls" and "xlsm" but not "xlsx.
Thanks in advanced
Similar Topics
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
This macro is amazingly straight-forward:
Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.
Any help appreciated..
I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -
Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub
The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!
I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required.
Cheers.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")
This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.
Any help would be greatly appreciated.
THANKS
How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?
Example User's path: 'C:\Documents and Settings\jfarc\Desktop'
Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.
Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.
I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path
Any help is appreciated. Here is an excerpt of the code:
Workbooks.Open Filename:="C:\Profile.xls"
Sheets("SELECTION").Select
Range("F3").Value = SNR
' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
ActiveWorkbook.Connections("Connection").Refresh
ActiveWorkbook.Connections("Connection14").Refresh
ActiveWorkbook.Connections("Query from C_Profile").Refresh
ActiveWorkbook.Connections("Query from C_Profile1").Refresh
I'm new to macros and would appreciate any help this board can offer. Thanks...
I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.
Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.
I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).
I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!
Thanks,
Jason
----------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'this disables the save function on the XLS
MsgBox "****Save is Disabled****"
' Following line will prevent all saving
Cancel = True
' Following line will prevent the Save As Dialog box from showing
If SaveAsUI Then SaveAsUI = False
End Sub
When I try to replace all commas (with nothing or with another character), I get the error message "The formula you typed contains an error."
I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.
I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.
Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?
Thanks so much for your help!
Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:
using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.
Anyone have any idea?
Cheers
Jon
Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
Example:
A1: 50
A2: 10
B1: 60
B2: 20
A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60
When I click save, it will change the display value to 80.
I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.
Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.
I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.
Any ideas how to fix this?
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks
something by the same name and now everything is gone.
PLEASE help!!!
I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.
What I want is for my code to execute as soon as the data connection refresh has finished.
When I use the statement
Code:
ActiveWorkbook.RefreshAll Application.Run "Project1.xlsm!ConvertDates"
The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.
Is there some way I can tell excel to wait till the refresh has finished and then execute the code?
Any help would be hugely appreciated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
Can anyone help with this, because I can't figure it out....