Hello,
I am having trouble with macros, as I need to write one which saves an excel spreadsheet as a PDF, to the folder specified by the hyperlink in cell D27 and under the name based on a combination of cells F12, F13 and F14.
Can anyone help please?
Many thanks,
Chris
Chris
What have you tried and what problems have you had?
If posting code please use code tags, see here.
Hello,
I have tried many of them and I have deleted the one which was working best. I got as far as being able to convert the spreadsheet as a PDF, but I want it to be saved to the destination as specified as a hyperlink in cell D27 and under a name specified by a combination of the values in F12, F13 and F14.
cheers
It should be straightforward apart from the hyperlink part.
What exactly is in D27?
In D27 is =HYPERLINK("H:\LOS\Investigations\"&F14) and in F14 is the name of a specific store, so when the spreadsheet is saved, it goes into the correct folder for the relevant store.
You don't say how F12, F13 and F14 should be combined to give the filename but here's an example where they values in those cells are simply concatenated.
Please Login or Register to view this content.
I have tried using this macro:
Sub SaveAsPDF()
'Saves active worksheet as pdf using concatenation
'of F12,F13,F14
Dim fName As String
With ActiveWorkbook.ExportAsFixedFormat xlTypePDF, Range("D27").Value & "\" & Range("F12").Value & Range("F13").Value & Range("F14").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D27" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub
I am getting the error "Compile error: Syntax error" and it highlights the top line in yellow.
Start with this and don't forget to assign value to fName.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Hi bakerman2, what do you mean when you say assign a value to fName? What kind of value do i put in there?
This is shorter.
So fName must end up with all elements combined to create fullpath and name for the sheet you want to save.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks