Saving as pdf to the desktop

[CarlosV]

Hi I have a file that i need to be saved as a PDF but on the desktop every time and if I could make the file name the same as the content of a cell it will be great.


Bellow the code, this sabes as pdf but in the desktop of my pc, the file will be in diferents computers when the button with the macro is clicked


Sub PDF()

'

' PDF Macro

'


'

Range("B2").Select

ActiveCell.FormulaR1C1 = "NOMBRE"

Range("A1:I22").Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:Documents and SettingsmyuserEscritorioNOMBRE.pdf", Quality:= _

xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _

OpenAfterPublish:=True

End Sub


Please help

 

[SirJB7]

Hi, CarlosV!


Your Royal Highness, please replace the filename reference with this:

-----

Environ("HOMEDRIVE") & Environ("HOMEPATH") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "NOMBRE.pdf"

-----


Your subdit,


Regards!


PS: In non English Windows versions (Spanish in this case) even if the desktop folder appears as "Escritorio" it's actually still named "Desktop". You'd check that running (Start button or Windows+R) cmd.exe command and issuing a Dir command.


PS2: I see you're neither using Windows 7 nor 8 but either XP or Vista, I haven't test it on these last ones, but I think it works there too (I don't remember how were the folder names in those OS, but I hope you'd be able to adjust the "Desktop" to "Escritorio" if it happens to be necessary -which I discard a priori-).

 

[Debraj]

@ SirJB007!


Just curious to know.. Is "$" is a concat character is spanish or above is just a typo..


Just in case if someone has change his/her default desktop location.. (in my case My Default Desktop & My Document are in D: Drive, as I frequently Format & Change my OS..)

[pre]

Sub test()
Set objWSHShell = CreateObject("WScript.Shell")
specialfolderpath = objWSHShell.SpecialFolders("Desktop")
Debug.Print specialfolderpath
Set objWSHShell = Nothing
End Sub

[/pre]

Regards,

Deb

 

[SirJB7]

@Debraj Roy

Hi!

Thanks for the catch, it's a typo. $ in Spanish Excel or Windows versions is the currency sign and in Excel the fixed prefix for cell references. Previous post updated yet.

BTW, nice approach that of Shell object.

Regards!