Swap a file path for a variable

[Paula1]

Spoiler

vTempFolder

=

C:\Geral\tmp\TEST.pdf

I need to change the path to a variable

objEmail.AddAttachment "C:\Geral\tmp\Numero.pdf"

Something like this

objEmail.AddAttachment  =  vTempFolder

Help!

**************************************************

sub PrintReport

Set WSHShell = CreateObject("WScript.Shell")

fln = "C:\Geral\tmp\myreport.pdf"

RegKey = "HKEY_CURRENT_USER\Software\QlikViewPDF\"

WSHShell.RegWrite RegKey & "BypassSaveAs", 1

WSHShell.RegWrite RegKey & "OutputFile", fln

ActiveDocument.GetApplication.Sleep 2000

WSHShell.RegWrite RegKey & "BypassSaveAs", 0

WSHShell.RegWrite RegKey & "OutputFile", ""

Set WSHShell = nothing

end sub

function GetVariable(varName)

        Dim v

        set v = ActiveDocument.Variables(varName)

        GetVariable = v.GetContent.String

end function

Sub ExecMacro()

       ActiveDocument.Reload        

        Envio()

        ActiveDocument.Save

       'ActiveDocument.GetApplication.Quit

       ' ActiveDocument.GetApplication.Close        

End Sub

Sub Envio()

        'Aqui apaga se tiver os arquivos.....

        Set filesys = CreateObject("Scripting.FileSystemObject")

If filesys.FileExists("C:\Geral\tmp\Numero.pdf") then

filesys.DeleteFile "C:\Geral\tmp\Numero.pdf"

End If

        '===========================================================================================

        ' Aqui roda o relatorio ID   RP01

ActiveDocument.GetApplication.Sleep 2000

Set filesys = Nothing

        ActiveDocument.PrintDocReport "RP01", "RP01"   

PrintReport()

ActiveDocument.GetApplication.Sleep 5000

' Aqui se existe o PDF, entao renomeia ele.......

        Set filesys = CreateObject("Scripting.FileSystemObject")

If filesys.FileExists("C:\Geral\tmp\QlikView Printing.pdf") then

   filesys.MoveFile "C:\Geral\tmp\QlikView Printing.pdf", "C:\Geral\tmp\Numero.pdf"

End If

Set filesys = Nothing

ActiveDocument.GetApplication.Sleep 5000

  SMTPServer =  "mail..com.br"

SMTPUser =   "biosanto@.com.br"

SMTPpPWD =    "***"

SMTPPorta =   "465"

Set objEmail = CreateObject("CDO.Message")

Set objConf = objEmail.Configuration

Set objFlds = objConf.Fields

With objFlds

'---------------------------------------------------------------------

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer 

if len(SMTPServer) > 0 then

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = SMTPUser

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = SMTPpPWD

else

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0 

end if

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPorta

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

.Update

'---------------------------------------------------------------------

Destinatario = GetVariable("vDestinatario")

Numero = GetVariable("vNumero")

Espaco = GetVariable("vEspaco")

    Titulo = GetVariable("vTitulo")

     Assunto = GetVariable("vAssunto")

     Anexo1 = GetVariable("vAnexo1")

         TempFolder = GetVariable("vTempFolder")

         'ActiveDocument.Variables("vTempFolder").SetContent "false", true

PDF = GetVariable("vPDF")

End With

objEmail.To       = Destinatario

'objEmail.CC       = "c"

objEmail.Bcc       = "contato@.com.br"

objEmail.From     = "biosanto@.com.br"

objEmail.Subject  =   Assunto & Anexo1

objEmail.HTMLBody =   Numero & Espaco & Titulo  

        '============================================================

        '===========================================================================================

        'Aqui colocamos os anexos.....

objEmail.AddAttachment "C:\Geral\tmp\Numero.pdf" ' Attachement

objEmail.Send

        '===========================================================================================

        'Aqui apagamos os arquivos pois senao da erro na proxima vez.....

'ActiveDocument.Save

ActiveDocument.GetApplication.Sleep 5000

'Next

'ActiveDocument.Fields("Gerente").Clear ' Limpar Filtro por Email

End Sub

[marcus_sommer]

Seems you need just to use the (right) variable because the exists already:

...
 TempFolder = GetVariable("vTempFolder")
...

[Paula1]

objEmail.AddAttachment "C:\Geral\tmp\Numero.pdf" ' Attachement

objEmail.AddAttachment  = TempFolder   ?

What would this variable look like on the path?

🤔 

[marcus_sommer]

In general, yes - means:

...
TempFolder = GetVariable("vTempFolder")
objEmail.AddAttachment  = TempFolder
...

In some cases (especially if any spaces or special chars are included) it might be necessary to wrap the path with double-quotes which requires often a triple/quadruple repeating of the quotes (easier would be to include them already within vTempFolder). Using: msgbox TempFolder will show the real existing content of the variable and is in general a very simple method to validate variables or a loop-iterator and also until which point the macro runs expected or breaks.

[Paula1]

Se filesys.FileExists("C:\Geral\tmp\Numero.pdf") então
filesys.DeleteFile "C:\Geral\tmp\Numero.pdf"
Fim se

Se filesys.FileExists("C:\Geral\tmp\QlikView Printing.pdf") então
filesys.MoveFile "C:\Geral\tmp\QlikView Printing.pdf", "C:\Geral\tmp\Numero.pdf"

Tem

pFolder = GetVariable("vTempFolder")

como faço para tratar essa parte do código. Pois tentei colocar a variável dentro e não funcionou. msgbox que você me deu trabalho.

I need to adapt the variable in these two places

[marcus_sommer]

Principally the hard-coded path could be simply replaced with the variable - but this variable needs of course to be available within the calling sub-routine. This means you need either to call the variable-routine within the sub or defining the variable globally or transferring the variable as sub-parameter.

What happens if the print is executed - nothing, any error or the execution breaks? Like above mentioned using one or several msgbox with the variable as output will quite soon show the cause behind the issue.

[Paula1]

Sorry, I see you tried to help me in many ways. But I can't change the variable path in the code. It's difficult being a newbie.

Sub Envio()

        'Aqui apaga se tiver os arquivos.....

        arquivo = GetVariable("vTempFolder")     

Set filesys = CreateObject("Scripting.FileSystemObject")

      If filesys.FileExists(arquivo) Then

      filesys.DeleteFile arquivo

End If

        '===========================================================================================

        ' Aqui roda o relatorio ID   RP01

ActiveDocument.GetApplication.Sleep 2000

Set filesys = Nothing

        ActiveDocument.PrintDocReport "RP01", "RP01"   

PrintReport()

ActiveDocument.GetApplication.Sleep 5000

' Aqui se existe o PDF, entao renomeia ele.......

        Set filesys = CreateObject("Scripting.FileSystemObject")

If filesys.FileExists(arquivo) Then

        filesys.DeleteFile arquivo

End If

Set filesys = Nothing

ActiveDocument.GetApplication.Sleep 5000

[marcus_sommer]

I need to repeat my question from above: "What happens if the print is executed - nothing, any error or the execution breaks?"

[Paula1]

I'm sorry, Marco, fail.

I know your friend is very busy, I imagine, but with the attached qvw you can't just do a test with the variable that is also in the qvw.

[marcus_sommer · Accepted solution]

Beside the efforts of resolving your task within your qvw which is beyond the targets of the community it wouldn't be very helpful because there are multiple effects from a lot of environment-related topics included, like user- and security settings as well as how and where the tasks should be triggered and many more.

Especially noticeable is the targeted pdf-printer which is the old and outdated QlikView printer which either hints that you are dealing with a quite old release or - sounding more likely - it's just old code found and applied without really knowing what to do with it. In this case I suggest you are involving any Qlik/Partner support and/or implementing appropriate tools like NPrinting which providing a GUI to define the various print/export-jobs.