VBA Code to export as pdf

[Chris1973]

Goodday everyone.

I have a problem with my export to pdf code

i use

Sub create PDF()

Dim ID As Atring

Id= Range("D4").Text

ActiveSheet.ExoprtAsFixedFormat _
Type:=xlTypePdf, _
Filename:C:\Users\chrish\documents\1.Wurth Master\Quotes\Pdf\" + id +".pdf",
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub


My question now how do i adjust the code to save cell D4 AND D8. That must be the reference to my workbook so it wil be

3005 john
3005 is D4
John is d8

And then it needs to close the workbook when done

Thanks a mil

 

[Chris1973]

I don't come right.

One of my friends suggest i use this code rather

Sub CreatePdf()

'Create and assign variables
Dim SaveLocation As String
Dim rng As Range

SaveLocation = "C:\Users\chrish\Documents\1.Wurth Master\3.Quotes and Brochures\Pdf"
Set rng = Sheets("Snooper").Range("B2:H49")

'Save a range as Pdf
rng.ExportAsFixedFormat Type:=TypePDF, _
Filename:=SaveLocation
ActiveWorkbook.Close

End Sub

This work also no problems, there is now problem with this code.

I just need to get assistance of where to add the following. I need to save cell d8 and d4 as reference( this will be the name under which it will be saved

 

[rlv01]

I just need to get assistance of where to add the following. I need to save cell d8 and d4 as reference( this will be the name under which it will be saved

That is the same problem as before. If you have bad names in d8 and d4 you will not be able to export the range to any filename that uses them.

 

[Chris1973]

Please ignore my ignorance.

I have no idea what you mean by bad names. I am not excel nor macros taught. I do not understand the coding phrases nor the language.

 

[rlv01]

Please ignore my ignorance.

I have no idea what you mean by bad names. I am not excel nor macros taught. I do not understand the coding phrases nor the language.

It is not an excel or macro issue. It is windows issue. There are rules about what characters you can use to make a file name. If you try to give a name to a file that does not follow the rules, then that name is a bad name. You are trying to export to a bad name, and that is why you are getting Error -2147024773.

Naming Files, Paths, and Namespaces - Win32 apps

All file systems supported by Windows use the concept of files and directories to access data stored on a disk or device.

docs.microsoft.com

Use any character in the current code page for a name, including Unicode characters and characters in the extended character set (128–255), except for the following:

• The following reserved characters:
  • < (less than)
  • > (greater than)
  • : (colon)
  • " (double quote)
  • / (forward slash)
  • \ (backslash)
  • | (vertical bar or pipe)
  • ? (question mark)
  • * (asterisk)
• Integer value zero, sometimes referred to as the ASCII NUL character.
• Characters whose integer representations are in the range from 1 through 31, except for alternate data streams where these characters are allowed. For more information about file streams, see File Streams.
• Any other character that the target file system does not allow.

 

[Chris1973]

another question then. Like i said my knowledge is extremely limited, what i know is purely what your average person knows


I have added a copy of my invoice lines i want to save

	27 April 2020
ACCOUNT NUMBER	412773
CUSTOMER NAME	DUTOIT AGRI PRODUKSIE: WESKAAP
ORDER NUMBER	Johan
WURTH REP
WURTH REP NUMBER
BOOK NUMBER	1000212

Customer name=D4 However D4 ALSO HAVE A VLOOKUP CODE =VLOOKUP(D3;CUSTOMERS!A2:D30000;2;0)
BOOKNUMBER =D8

Is this what might cause the problem then

 

[rlv01]

What is causing the problem is that there are characters in D4 that may not be used in a filename. In your example above D4 is "DUTOIT AGRI PRODUKSIE: WESKAAP". It contains a colon character (":"). A colon (":") is not permitted to be used in a file name, so when you attempt to export the file to DUTOIT AGRI PRODUKSIE: WESKAAP.pdf, you will get an error.

 

[Chris1973]

Ok

This makes 100% sense now

This was a previous code you gave me

Will it work, obviously do i need to take out the :

Sub CreatePDF()

ActiveSheet.ExoprtAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\chrish\documents\1.Wurth Master\Quotes\Pdf\" & Range("D4").Value & " " & Range("D8").Value & ".pdf", _
IgnorePrintAreas:=False, OpenAfterPublish:=False

ActiveWorkbook.Close

 

[Chris1973]

Thank you very much, problem solved. IT WORKS

THANKS SO MuTCH. I NEED TO SHOUT THIS WAS 2 WEEKS IN THE MAKING

Now just for interest sake, can i save this as a pdf and xlsx file at the same time

 

[Chris1973]

Thank you to everyone involved in helping me.

Everything is working as supposed to work and this thread is closed.