Problem VBA REPLACE FUNCTION

[IGORR]

Good morning;

I need the help o somebody with the following code.

The error is "Argument not valid".

Sub PrintPDFServ() '*****Imprimir sin visualizacion*****
Application.ScreenUpdating = False
'Para Crear archivos PDF Desde Excel nada mas tienes que instalar el complemeto para guardar archivos pdf de Oficce
Dim sDate
sName = Replace(Sheets("Ficha Tecnica Serv").Range("E6"), ".", "_")
sDescrip = Replace(Sheets("Ficha Tecnica Serv").Range("E8"), ".", "_")
Set sDate = Sheets("Ficha Tecnica Serv").Range("I6")
XName = sName & "-" & sDescrip & " (" & Format(sDate, "ddmmyyyy") & ")"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\SERVIDORVELL\docs\Ejea\Calidad\Especificaciones\Especificaciones Tecnicas Materiales\Papel\Fichas Tecnicas de Papel\Definitivo\" & XName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Application.ScreenUpdating = True
End Sub

Thanks in advance.

 

[maabadi]

Why two "\\" at address name? change address to simple address like "D:\" and test it.

 

[IGORR]

It is not the problem.

The code works perfectly before I introduced this two lines:

sName = Replace(Sheets("Ficha Tecnica Serv").Range("E6"), ".", "_")
sDescrip = Replace(Sheets("Ficha Tecnica Serv").Range("E8"), ".", "_")

With this two lines; I try avoid problems with the name of the pdf printed when found points.

I used "set" before this two lines, and the mistake was other "error compilation, objet required.

 

[maabadi]

I test you macro with Sheet1 name and it run without Problem.
Are your sheet name doesn't have extra space or character at excel sheet name? Check it.

 

[Joe4]

I agree with maabadi. I think your sheet name is most likely the issue.
What happens if you run this code?

Sub Text()
    Sheets("Ficha Tecnica Serv").Activate
End Sub

If that doesn't work, then the only two things I can think of that would cause that would be:
- The sheet name is not really "Ficha Tecnica Serv" (you may have some extra spaces in there somewhere)
- The sheet is hidden so it cannot be selected

 

[IGORR]

The problem is when in Range("E6") and Range("E8") appears ".".

sName = Replace(Sheets("Ficha Tecnica Serv").Range("E6"), ".", "_")
sDescrip = Replace(Sheets("Ficha Tecnica Serv").Range("E8"), ".", "_")

These two lines convert "." in "_" becuase it is not possible to name the pdf with points between in the text.

Please, insert one point ins your example and sure it will give you the same error "Invalid argument".

When I use the example without points it works like your example, the problem is I need it works with points.

Thanks in advance.

 

[Joe4]

Please, insert one point ins your example and sure it will give you the same error "Invalid argument".

It does not. It works just fine for me, and runs to completion without errors.

Can you post exactly what is in cell E6 and E8?
You may have some other illegal characters in there that you cannot use in file paths or file names.

 

[Fluff]

If you are getting the compile error on this line

sName = Replace(Sheets("Ficha Tecnica Serv").Range("E6"), ".", "_")

Check that you don't have any procedures or variables called Replace

 

[IGORR]

E6 = Z01020325.903
E8= TISU SERV. 2C ROSA FRAMBUESA 16,5grs 32cms ECOLABEL *FSC MIX CREDIT*

 

[Joe4]

E8= TISU SERV. 2C ROSA FRAMBUESA 16,5grs 32cms ECOLABEL *FSC MIX CREDIT*

Yep, just as I expected. You cannot use "*" in file folders names or file names.
You need to remove those too.

You can remove those like this:

sDescrip = Replace(Replace(Sheets("Ficha Tecnica Serv").Range("E8"), ".", "_"), "*", "")