Macro To Save Xls To PDF With Path & Naming Convention
Mar 31, 2014
I am using the below macro to save my workbook with in the correct path and naming convention.
In fact, as I also need a pdf file from that excel file, I tried to change the below macro and replace ".xlsm" by ".pdf"... However acrobat does not like it..
HTML Code:Â
Sub PDF()
'created by Gregory Charbonnel
'ActiveWorkbook.SaveAs Filename:="Z:FOLDER & FILE MANAGEMENTREview (ETF)PDF ETFETF_" & Format(Now(), "dd_mm_yyyy") & ".pdf", _
' FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
I guess that I should use the print option which gives me that when I record the macro. how to implement my naming convention + path in that macro.
HTML Code:Â
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
View 2 Replies
ADVERTISEMENT
Feb 3, 2008
I have a workbook composed of many worksheets with varying names. Of the 40 or so worksheets, 25 of them contain data that I need to copy and consolidate into a master summary sheet within the same workbook. All of the sheets I need to grab data from have a worksheet name that ends in “(Data)”. All worksheets contain data that starts on the same row (row 8) but the number of rows of data will change daily. When I copy the data range on the individual sheets I do not want to copy the header row. I’ve tried various uses of the offset command with no luck to resize the selected range. I’ve also tried giving each range a named Dynamic Range and referring to this name in VBA code but that didn’t get me very far either. So the flow looks something like this:
1. Find all sheets with name ending in “(Data)” (i.e. “Project X (Data)”)
2.For each sheet found, find the data range and omit the first row of data.
3.Copy selected range.
4.Go to sheet “Master” and find the last row of data. Move down one row to the first empty row and paste copied range.
5.Go to next sheet and repeat.
View 9 Replies
View Related
Apr 2, 2012
The macro is working fine but what I want to change is the path for saving the file. The path that I would like it saved to is in the worksheet "NEW ORDER_" cell "N2"
Sub RENAME2()
Dim strWbKill As String
Dim myName As String
myName = ThisWorkbook.Path & Application.PathSeparator 'copy to same folder that file is in
myName = myName & Application.Cells(3, 7) & "_" 'cell G3 Style
[Code]...
View 5 Replies
View Related
Mar 4, 2013
is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?
I notice that the hyperlink is ....pdf ry.pdf
if I change the position of the file excel there is a problem!
I would like to save es. d:invoichepdf ry.pdf
View 1 Replies
View Related
Aug 21, 2008
I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."
Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete
End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................
View 5 Replies
View Related
Mar 19, 2008
I want to create a macro that saves my file this way:
in a folder named after B2
and
as a file named after B4n.
View 4 Replies
View Related
Aug 6, 2008
I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as “c:
eports8052008”, so tomorrow 's folder would be “c:eports8062008”.
All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), “mmddyyyy”), get error message.
View 9 Replies
View Related
Jan 31, 2014
I need to save a document in the following path: [URL] .......
Only the year month and day sould change when I hit the save button/macro: 2014_01_29
How to build a macro that would save my file as per my above explanation??
If I would run the macro today the excel file should be saved in the path: [URL] .......
View 1 Replies
View Related
Oct 3, 2012
I want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
View 2 Replies
View Related
May 27, 2009
I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?
Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False
On Error GoTo ErrCatcher
Sheets(Array("Copy Me", "Copy Me2")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues....................
View 2 Replies
View Related
Apr 18, 2007
how do i save a text file to xls in code. i want the new .xls file to appear alongside the original .txt file.
View 9 Replies
View Related
Dec 15, 2009
I am creating a training records matrix which will be used by 30 or so locations. each one may want to save in a different location. I would like to save the chosen location as an entry in a cell so this can be accessed by other macros.
View 9 Replies
View Related
Jul 1, 2008
I want a Macro which will copy my workbook and paste it into a new workbook. Then it will automatically save it under the SAME name as the original and the SaveAs Box must show up (for cosmetic fixes). It is important it asks if it wants to save it under the same name. Example: My file is called "Summary_2008" and when I hit the Macro it will do those things and it will ask me if I want to save it in the SAME directory under the name "Summary_2008" (I will only add a number then).
(The macro must know how to read the original name since I have 7 possible filenames - it depends on which company I choose, that is why it is important it is saved under the same name)
Sub Zamenjaj()
Worksheets.Copy
Dim WS As Worksheet
For Each WS In Worksheets
Zadnja_vrstica = WS.UsedRange.Rows.Count
Zadnji_stolpec = WS.UsedRange.Columns.Count
For rwIndex = 1 To Zadnja_vrstica
For colIndex = 1 To Zadnji_stolpec
With WS.Cells(rwIndex, colIndex)
If WS.Cells(rwIndex, colIndex).HasFormula Then..........................
View 3 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Oct 6, 2011
I'd like a macro that will:
Save my workbook
Then
Open the SaveAs box with the path already pointing to a specific directory and with the Name and file format already entered (.xls)
This is basically to save time navigating through our folder structure (which is a real mess). I don't want to automatically saveas the file as the target folder will change each month and the last part of the file name will chance (month). So I'd like a bit of flexibility rather than total automation.
View 3 Replies
View Related
Apr 1, 2012
I am currently using the following code to lauch the "save as" window.
Application.Dialogs(xlDialogSaveAs).Show
But I would like it to default to a poticular folder not the one of the current file location i.e. C:my documentswork
View 3 Replies
View Related
Dec 28, 2006
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following
Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub
to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be
ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
View 4 Replies
View Related
Aug 27, 2007
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub
View 5 Replies
View Related
Jan 5, 2008
Excel 2002 SP3
When a file is currently open, and the user selects "Save as", it takes them to the files native directory. (Where it was last saved).
If the user has that same file open, opens another file from a different directory, goes back to the first file and selects "Save as", it takes them to the location where the last file was opened from.
View 9 Replies
View Related
Feb 6, 2008
i try to write a code to pop up input box with browse button to select the file path
the code without browse button is workin corectly
Private Sub CommandButton3_Click()
Dim N As Integer
Dim Fname As String
Fname = InputBox("Please enter the file name and path to save the output", "enter file name")
Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy
ActiveWorkbook.SaveAs Filename:=Fname
ActiveWorkbook.Close
End Sub
Need to add browse button to make it easier than writting the file path
View 4 Replies
View Related
May 30, 2014
Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:
Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C
View 6 Replies
View Related
Aug 11, 2009
I have a spreadsheet where I have the first and last name in two separate columns. In the same spreadsheet, I have a column where there is an Email Convention. The email convention can change from row to row. Is there a way to take the first and last name columns and make it format according to the email convention column. For are some samples.
First Name Last Name Email Convention
Ada Smith @mail.uch.org
Ann Blanc _@chs.net
Bob Jones .@halifax.org
View 9 Replies
View Related
Mar 17, 2014
I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
[Code] .....
View 7 Replies
View Related
May 3, 2008
I was wondering if there is a way to open up a given program (notepad in my example) ..paste data from cells copied in excel and save the notepad file to a specific location and close it out??
The file path will be given from a data cell in excel as well as the file name.
From searching the forums i've come up with the way to open notepad
using the lines
Dim RetVal As Variant
RetVal = Shell("C:WINDOWSSYSTEM32
otepad.exe")
View 9 Replies
View Related
Dec 20, 2009
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................
View 2 Replies
View Related
Feb 11, 2014
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
View 1 Replies
View Related
Sep 6, 2012
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
View 1 Replies
View Related
Oct 22, 2008
I'm trying to have a macro that opens the save as window, places the name in cell f5 and then allows you to save the workbook with cell f5 as the filename. I have managed to get the save as window to come up and the cell f5 as the name, but when I press save is doesn't. Here is what I have (also, is there a way to direct this to a specific folder).
View 4 Replies
View Related
Jan 17, 2010
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.
View 10 Replies
View Related
Dec 3, 2008
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.
View 2 Replies
View Related
