Save Excel Sheet To A Specific Location Through Macro
Nov 19, 2009I have a excel sheet which is completely formula driven and no macros in that.
I want to macro which can save that excel sheet to a specific location.
I have a excel sheet which is completely formula driven and no macros in that.
I want to macro which can save that excel sheet to a specific location.
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View 2 Replies View RelatedI am running a macro in Excel which automatically generates a PDF of my worksheet. Currently it saves in the default location but i want to modify it to a specific location - P:Emergency Services|Procative ContactForms PDF.
The current code is;
VB:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E7").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Range("A1:E43").Select
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code:
Sub atest()
Dim strFileName As String
Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
I need excel 2010 to automatically save at 09:30 and 19:45 every day monday-friday. Also need the file name saved to reflect the time and date it was saved - if possible.
My PC locks after 10 minutes of inactivity - unfortunately this has to happen because of where my PC is situated.
I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.
In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.
In a workbook, I use a macro to extract a sheet and save it as xls.
What code should I use to prompt the user to define the location and name of the new file to be saved?
For a sheet that many non-expert users will use on different systems I need a macro that let's them save, print and send the results of their work. So I made a macro that makes a copy of only 1 sheet of the workbook and saves it with a given name to a given location. The problem is that I want a location prompt to ask the user where they want the file saved, while giving/suggesting them a fixed filename. A lot of different users will make and use their sheets so I need a certain naming policy to manage all the files. (date, location, etc)
View 4 Replies View RelatedI am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to
1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment
2. Save the file to my laptop directory My Documents.
Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect.
Here is what happens:
1. Open up MacroFile and run macro
2. Hyperlinked file LinkedFile_1.xls is opened
3. File NewFile_1 is saved but contains the info from MacroFile
4. Hyperlinked file LinkedFile_2.xls is opened
5. File NewFile_2 is saved but contains the info from LinkedFile_1
6. Hyperlinked file LinkedFile_3.xls is opened
7. File NewFile_3 is saved but contains the info from LinkedFile_2
The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook.
Sub LinkAndCopy()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
'**** Copy LinkedFile_1..................
I have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls"
The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.
View 14 Replies View RelatedI have recorded the below macro and I am not very happy with it. enhance the macro and add the following:
I need to copy the range "ROW" and past it below the range "MENU". Also the range "ROW" (ROW = copy of 3 line with some cells merged) is hidden.
I need to unhide and hide back the rang ROW. Right know I have to leave the range appearing otherwise my macro doesn't work.
HTML Code:
Sub ADD()
' ActiveWindow.LargeScroll Down:=2
Range("ROW").Select
Range("ROW").Activate
Application.CutCopyMode = False
Selection.Copy
' ActiveWindow.LargeScroll Down:=-2
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Range("A7:A9").Select
End Sub
I am currently having a slightly annoying issue that only happens 50% of the time, that when trying to re-save a excel sheet on a network location. The network location is a folder on another computer. The same happens when I try and save it as a pdf, but only happens half of the time.
The message which pops up says something along the line of 'Saving as: 'File-ServerDocumentsexcelsheetexample.xls' ' and a sort of green loading bar appears. It does this for about 15 seconds then goes off.
I've checked our anti virus (which is eset) and I've set it to not real time scan network locations.
I have attached a example of the saving message: ExcelWarningExample.jpg
If i have a sheet with multiple tabs can I somehow make excel open that file on a particular tab rather than the tab that the user was on when they saved/closed it.
View 9 Replies View RelatedI have a script that opens the workbook Excel, and save him as PDF format. But my script saves all sheets in a book. I need to save only sheets with name "Cnt1", "Cnt2" .... "CntX".
Code:
Dim xlObj, objArgs, xlWB, xlSH
Set xlObj = CreateObject("Excel.Application")
Set objArgs = WScript.Arguments
file = objArgs.item(0)
fileToSave = objArgs.item(1)
set xlWB = xlObj.Workbooks.Open(file)
xlWB.ExportAsFixedFormat 0, fileToSave , 0, 1, 0,,,0
xlWB.close False
xlObj.quit
Looking for VBA code to automatically save an excel file as an .xlsx format, and saving to a specific folder: C:UserswolfmDesktopLoad Lists
I have been using the attached code, but it's not working. It saves to the desktop, not the folder. Also, when it creates the file name based on sFile = ActiveSheet.Range("C4").Value & ".xlsx", it adds wierd things to the file name. I want the file name to be simply the value in C4.
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
I've been using this macro to pull data from multiple files, but instead of it creating a new tab, how can I have the macro pull the data and save it on a specific tab name(ex. Comp Stores). Here's the code I have currently.
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
Sub Combine_Workbooks_Select_Files()
Dim MyPath As String
[code]....
I have a folder called "Report" at C: and inside this folder there are 12 folder named from 1 to 12 ("1","2",...,"12")
I need a macro to save my workbook as a file in C:Report(one of these folders depending on the month of a certain cell)
i.e. if the date in this cell is 8/12/2012 .. then save my file in C:Report12*.xlsm
This is the code i use
Code:
Private Sub CommandButton1_Click()
Dim fname As String
fname = Format$(Range("S8"), "dd-mm-yyyy")
On Error Resume Next
ActiveWorkbook.SaveAs Filename:="C:Reort" & fname & ".xlsm"
End Sub
So what i need now is what to insert after C:Report
I want a macro that will save a copy of the current file using specific naming convention. My file is called OTR2CMMASTER - I want the macro to save a copy as OTR2CMSAGE(CURRENTMONTH/YEAR). The OTR2 is a named field (ProjectCode) in the workbook.
View 5 Replies View RelatedHow do I write a vba code to print active sheet to pdf, auto save in specific folder then mail using outlook
im using 'Nitro PDF Creator'
also id like it to automatically save the pdf as the same file name as the active worksheet and also auto enter the following when opening the email server:-
Address: Cell D18
Subject: Cell D21
Email body:
find attached quotation
Optional installation available on request
I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:
1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.
2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.
Here is the current code:
Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
On Error GoTo ErrorHandler
[Code] ..........
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
I have the following code allowing the user to save a file using a custom macro button. However, I do not want to let them change the name of the file, just the location. How do I do this? ...
View 9 Replies View RelatedI have a macro which performs the auto save function perfectly. I'm looking at possible enhancements.
1. How to set autorecover save location in VBA? I know how to do this on the front-end i.e goto tools/options/save tab and set it there As i have disabled save autorecovery feature, autorecover save location is greyed out
2. How to create a backup of my workbook? So that my workbook will perform autosave and when i close this workbook at the end of the day all the latest changes are added to the backup and saved
User selects 'go to page B' on page A. They look at an object then click the object to go back to page A. Once back on page A they need to check data on page B, but in the same general location as where they just looked. This is over simplified, but pretty much sums up what I need to do. The code I have now is below, but keeps sending me to the last selection on my current page.
Public Sub SaveLocation(ReturnToLoc As Boolean)
Static WB As Workbook
Static WS As Worksheet
Static R As Range
If ReturnToLoc = False Then
Set WB = ActiveWorkbook
Set WS = ActiveSheet
Set R = Selection
Else
WB.Activate
WS.Activate
R.Select
End If
End Sub
Public Sub SetSaveLoc()
SaveLocation (False)
End Sub
Public Sub GetSaveLoc()
SaveLocation (True)
End Sub
I have a workbook with 4 worksheets the first is called "input" which I use to enter information which goes to the other worksheets which arecalled engine, gearbox, doors.
I am stuck on the following; after inputting the information. I want to save as pdfs in various locations.
Is it possible to have 4 buttons on the input sheet which automatically :
Button 1 - Save "engine" worksheet as a pdf to a specified folder
Button 2 - Save "gearbox" worksheet as a pdf to a specified folder
Button 1 - Save "doors" worksheet as a pdf to a specified folder
Button 1 - Save the sheets as multiple excel files to a specified folder
I often need to save the user's current position on a worksheet, do some stuff then get them back to where they started. In the past I've saved the current location sometimes as a string and sometimes as a range.
Code:
Sub BackToRange()
Dim BackToHere As Range
Set BackToHere = Selection
' do stuff
BackToHere.Select
End Sub
Code:
Sub BackToString()
Dim BackToHere As String
BackToHere = Selection.Address
' do stuff
Range(BackToHere).Select
End Sub
managed to get a piece of code working to create unique sequential numbers for purchase orders, but only by "enabling all macros" which is apparently not recommended and could conceivably, as I understand it, leave a PC vunerable to viriuses contained in other imported files.
Please bear with me as I'm very new to anything other than basic Excel functions; macros were, until last week, something I didn't even know existed, let alone how to use them. Anyway, having got my macro working, I understand that the best thing to do is put it in a "trusted location" from where it will work automatically without requiring operator input (whilst still maintaining high overall security), rather than "enabling all macros", but I have a couple of issues with this.
Firstly, having created a folder in "my documents" as per the instructions, how do I save the macro there? What format should it be in? Surely not a word document? (as you can gather I am still really in the dark about all this). If not, what?
Secondly, how do I direct Excel to access and run the macro from this "trusted location" folder, rather than the already existing "module1"?
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....