I have a very strange error at present. I am saving a sheet as a file using VBA from within Excel and when I run the program from the environment of my flash drive it works without a problem but when I run the same code on my main PC it causes an error :
Run time error 1004
Method SaveAs error ... and it causes Excel to crash and then recover the file.
The code that causes this is shown below : ....
When loading my workbook, my userform loads perfect, but when I go to hit the "next" button. It crashes with the error:
"Path/File access error."
The next button code, quite simple: ....
Is there a way too get the SaveAs box come up with a marco? When you go too file and save, a box comes up and lets you choose where and what you save. I would like too have it come up with a command button if its possible.
View 3 Replies View Related
Sub CrearCSV()
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("Prueba").Activate
ActiveSheet.SaveAs Filename:= _
ThisWorkbook.Path & "Prueba.csv", _
FileFormat:=xlCSVWindows, CreateBackup:=False
Set CSVfile = ActiveWorkbook
CSVfile.Close savechanges:=1
End Sub
Sheet "Prueba" is Hidden
Other Sheet is visible
Why SaveAs stores only the visible Sheet?
How does stored hidden Sheets with WorkSheet.SaveAs?
I'm running a macro in a template and trying to save the results to a different folder.
I want the file name to be blank in the Saveas Text box of the UI also. Unfortunatley my code is bugging out & I can't work out why.
ActiveWorkbook.SaveAs Filename:="C:", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
The save to path does change so I want to be able to change the save location.
It is supposed to bring up an input box, the date entered in input box becomes the date of file. The file is then Saved As. This all seems to work but when I try to open the newly saved file the sheet does not open. Is this a file type issue? Should it be .xls?
Code so Far
Dim InputDate As Variant
Dim FileType As String
Dim myFileName As String
Dim myFolder As String
FileType = ".xls"
myFileName = "Report"
myFolder = "Q:Sales FinanceAndyDUBLIN MACRO TEST"
An already existing file will be changed by my macro and at the end I want the user to save the file with a different name in a different directory with the option to change the filename or directory. Therefore I use the excel-standard-dialog "save as". But I want the dialog to start in another path as the path the original-file is stored in. But also I use the ChDrive and ChDir Parameters the dialog even starts in the original-file-path. I think this is standard for the dialog and I should maybe use a parameter in the show-arguement? The code is below.
Option Explicit
Sub SaveMyFile()
Dim xFileName, xAnswer
ChDrive "J"
ChDir "J:myfolder"
xFileName = "testme.xls"
xAnswer = Application.Dialogs(xlDialogSaveAs).Show(xFileName)
End Sub
By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").
View 4 Replies View RelatedI'm hoping someone can stop me going crazy with what should be something very simple. To make things easier at the end of my code, all I'd like to do is bring up the SaveAs dialog box and have it default to a specific directory so that the user doesn't have to click through the mountain of various files and folders we have.
View 6 Replies View RelatedHello Members, I been trying to save a workbook as the name that will be input into cell B2 and the folder from what is input into cell B6. Without any luck using different codes.
In cell B3 is typed an new name and cell B6 is a list of clients that can be choosen from a selection in a dropdown.
Example, If 250R was input into cell B3 and Honda was selected in cell B6. The new workbook would be placed into the Honda folder with the name 250R.xlsm
To Copy and Save a workbook upon closing the new workbook.
way for other staff to do the following...
Open an exiting protected spreadsheet,
SaveAs [newfilename]
Unprotect new sheet
Edit new sheet
Protect new sheet
Save it.
Hence we how have two protected sheets with different filenames and different contents.
Then, next time, the teacher will start again with the new spreadsheet file resulting in yet another version of the same data. etc etc.
I want to use the SaveAs function using the Inputbox results. The user will input a date like 5-30-06. Then I want save the workbook as "WE5-30-06.xls". I can't figure out how to do this b/c it wants to use 5/30/06 which isn't allwoed in naming conventions. BTW the inputbox name is dWeekend.
View 2 Replies View RelatedI have the following code working well. I'd like the pdf to be saved to the users local machine before it emails it. The user must be able to choose the path. How can I do this?
[Code].........
I am trying to edit the code below to save in .xls instead of .xlsm. Also, I need to disable the compatibly checker.
[Code]....
I found that there is an issue with saving a workbook where there seems to be an issue between 2003 and 2007.
The code I used:
I am trying to use cell C6 in my control sheet
Which is this
C:UsersNeilWin7DBTradingETFfiles
And put as a string into SaveAs
wbNew.SaveAs Filename:=Worksheets("Control Sheet").Range("C6") & TickerIndex & "ProSharesNAVRatio" & ".txt", FileFormat:=xlTextWindows
I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.
Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code] .......
These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.
So as an example an excel workbook report generated in the folder:
"D:DropboxWorkVerification Reports!Test"
Would be saved as "!AirCheck AutoTest Report - !Test.xls"
or a report in the folder:
"D:DropboxWorkVerification ReportsHoliday Inn Surfside"
would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"
Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.
What I believe is the pertinent part of the code that needs adjusting is:
"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "
How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies
I am running 2003, but I'm working on a WB from a colleague running 2007.
I have downloaded the converter from MS.
The document is a Template, which I open, load with data from a .csv file, then SaveAs.
My problem is, whenever I SaveAs, the resulting document is Read Only, and I don't want it to be.
I have a macro that creates and saves an XML file with a specific file name. Is there a way to modify this code in such a way that the user will be able to specify the path?
Set wb = Workbooks.Add
NewFileName = "Export For " _
& "Cust " & Customer_Data.Range("custName") _
& ", LOB " & RequestLOB _
& ", Type " & RequestType _
& ", Site " & RequestSite
On Error GoTo DoNotReplaceExistingFile
ActiveWorkbook.SaveAs Filename:=NewFileName & ".xml", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
I think my code is close but I keep getting an error "Run-time error '1004': Application-defined or object-defined error"
I am trying to rename the active workbook by appending yesterday's date onto the filename, using the Save As.
I might be going about this all wrong anyway. My user needs the macro to save, rename, and close the renamed copy of the workbook but to leave the original workbook open. I'm trying to do it in steps so I might be doing this the hard way. However, my code to Save As is below:
Sub SaveAsRename()
Dim CurrentPath As String
Dim CurrentFileName As String
Dim NewFileName As String
Dim Today As Date
Today = Int(Range("A3") - 1) 'Cell A3 contains the =Today() formula
CurrentPath = ThisWorkbook.Path
CurrentFileName = "QU Backhaul Dispatch "
NewFileName = CurrentFileName & Today & ".xlsm"
ActiveWorkbook.SaveAs Filename:=CurrentPath & "" & NewFileName
End Sub
My Locals window has all of the correct values right up to the ActiveWorkbook.SaveAs which is where it fails.
I have written a routine that automatically saves the file to a specified folder as the name of a cell from one of the worksheets. I am trying to use the following code to see if that filename already exists and stop the routine if it does. If I manually enter an existing filename the routine works well, however I cannot get it to recognise the automatically created filename, here is my .....
View 9 Replies View RelatedI have the following
Private Sub cmdSave_Click()
SaveRow
On Error Goto ErrorHandler
ThisFile = Sheets("Data"). Range("B2").Value
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsMy Documents" & ThisFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks.Close
ErrorHandler:
MsgBox ("Error locating file. " + Err.Description)
End Sub
This saves a workbook with the File Name based on a cell in a worksheet in the above Path Name.
Is there a way that I can change this code to ask for the Path Name with the File Name, from the worksheet, as a default.
I have a code that opens a workbook and saves it under one file name and then saves it under 2 different file names, but it keeps on saving them as password protected and I can't work out why. Would it be something to do with the code or something else? The following is the code I am using:
Workbooks.Open "C:BatteriesStore InformationStores" & StoreFile
ActiveWorkbook.SaveAs "C:BatteriesStore InformationOld Stores" _
& StoreFile & " " & Format(Now, "d-mmm-yy"), xlNormal, Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:BatteriesStore InformationStores" _
& StoreFile, xlNormal, Application.DisplayAlerts = False
I've got a workbook that I want to keep intact in a central location on our local network, to make it easier for everybody to access, but don't want people to be able to save changes to the master workbook itself. I'd like to be able to disable the 'Save' feature while still allowing 'SaveAs'. The following code (installed in ThisWorkbook) results in the OPPOSITE of what I'm trying to accomplish (i.e., it allows 'Save' but disables 'SaveAs'):
Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)
If SaveUI Then
MsgBox "The 'Save' function for this workbook has " & Chr(10) & "been disabled. Please use 'Save As'.", vbOKOnly + vbInformation, "Save Disabled"
Cancel = True
End If
End Sub
Obviously I'd also like to check the filename they're performing the 'SaveAs' under and disallow it if it matches the master filename.
I've thought about making the workbook into a template, which would sort of accomplish the same thing, but it would be much easier to just keep it as a workbook.
I have the following code which filters data in a sheet, and loads the numbers into textboxes on a userform. There are two multipage controls, each with several textboxes on them. I have commented out several lines because if I leave any of them in, it crashes excel.
It will work once. But if I close the workbook, re-open it, and run the code again, it crashes.
The thing I find really interesting is that all the 'problem lines' were added in as part of a retro-fit. The person I created this workbook for needed one more column of data. I followed the same programming pattern as what I used on the other, but only these ones seem to cause problems. Any thoughts?
NOTE: The message boxes were just to help me figure out which lines of code were causing the crash. ....
My main workbook basically starts with, Sub macro1 (), Do Until, Web Query, many many many calculations in between and finally ActiveWorbook.Save.
After ActiveWorbook.Save it will start the same process with a new web query and works all perfectly and perpetually just as I would like it to....
I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.
1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.
2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.
My specific question is about no. 2 and the following are the individual macros and the macro that runs them.
I have a Workbook containing seven or eight Worksheets. The first Worksheet is a data entry and options selection sheet displaying a selection of Textboxes, Option buttons, Combo boxes etc, the second sheet contains reference data, following these are a number of Worksheets that are calculated and filtered as a result of those options and inputs. A 'print' button on the input Worksheet then runs a Procedure that applies the relevant filters, assigns print areas and prints out the 4 or 5 filtered worksheets. I now want to expand this Procedure to take a copy of just these filtered Worksheets and save them as another Workbook (values only) for subsequent free editting.
I have started by selecting the relevant cells on the first filtered Worksheet, copying, opening a new Workbook, renaming the first Worksheet to match the one I'm copying, PasteSpecial formats, PasteSpecial the values, go back to the original Workbook, select the relevant cells on the next Worksheet, copying, opening the new Workbook, renaming the Worksheet etc etc. Now this seems to be a very labour intensive approach and I'm wondering if there is an easier way and how to do it. Ideally something like... taking a copy of the whole Workbook somehow changing all the cells to values only, rather than formulars then deleting the first two Worksheets (input and data ones).
We're gradually moving over to Excel 2007 (from 2002) at my workplace.
My problem is that I need to run macros on both versions but I'm having trouble with the SaveAs method.
I have Excel 2007 set to save automatically in Excel 97-2003 File Format but this doesn't seem to apply to files saved using VBA.
My original 2002 code is as follows:
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls"
and 2007
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls", FileFormat:=xlExcel8
How can I get it to switch depending on the version it's run on? Unfortunately Excel 2002 doesn't recognise '=xlExcel8' so I don't think I can use an If/Else statement on the version number.