VBS code to VBA

[rjtaylor]

I have the following code working in VBS. I cannot figure out how to use it in VBA. Any ideas would be great. I am trying to figure out which PDFs in a folder are in Portfolio format vrs standard format
here is the VBS that works:

The Code says VBA but it actually is VBS

Set objShell = CreateObject("WScript.Shell")
objShell.CurrentDirectory = "C:\Temp"

Function CmdOut(p):Dim w,e,r,o:Set w=objShell :Set e=w.Exec("Cmd.exe"):e.StdIn.WriteLine p&" 2>&1":e.StdIn.Close:While(InStr(e.StdOut.ReadLine,">"&p)=0)::Wend:Do:o=e.StdOut.ReadLine:If(e.StdOut.AtEndOfStream)Then:Exit Do:Else:r=r&o&vbLf:End If:Loop:CmdOut=r:End Function

str = "for /f " & """" & "delims=" & """" & " %f in ('dir /b *.pdf') do @(echo/ && find " & """" & "/Collection" & """" & " " & """" & "%f" & """" & " >nul && echo PORTFOLIO= " & """" & "%f" & """" & " || echo NOT Portfolio or Error " & """" & "%f" & """" & ")"

Dim PortfolioList
PortfolioList = CmdOut(str)
WScript.Echo PortfolioList

 

[6StringJazzer]

There is a problem with this code in that there is an embedded function definition but no containing procedure. Is this the complete set of code? I am not familiar with VBS code structure but in general it looks to me like something's missing.

I do not know if VBA can access StdIn and StdOut for streams associated with cmd.exe.

The variable WScript is going to give you trouble. This is a VBS object that does not have a corresponding entity in VBA. Please describe what you want this line of code to do.

   WScript.Echo PortfolioList

However, here is the converted code. I have no idea if it will do what you want it to do. What is "Portfolio format" for a PDF file?

Public Sub VBSRewrite()

   Dim PortfolioList As String
   Dim CmdString As String
   
   CmdString = "for /f " & """" & "delims=" & """" & " %f in ('dir /b *.pdf') do @(echo/ && find " & """" & "/Collection" & """" & " " & """" & "%f" & """" & " >nul && echo PORTFOLIO= " & """" & "%f" & """" & " || echo NOT Portfolio or Error " & """" & "%f" & """" & ")"
   
   PortfolioList = CmdOut(CmdString)
   ' WScript.Echo PortfolioList ' will not work in VBA. where do you want this output to go?

End Sub

Function CmdOut(p As String) As String

   Dim w As Object
   Dim e As Object
   Dim r As String
   Dim o As String
   Dim objShell As Object
   
   Set objShell = CreateObject("WScript.Shell")
   objShell.CurrentDirectory = "C:\Temp"
   
   Set w = objShell
   Set e = w.Exec("Cmd.exe")
   
   e.StdIn.WriteLine p & " 2>&1"
   e.StdIn.Close
   
   While (InStr(e.StdOut.ReadLine, ">" & p) = 0)
   Wend
   
   Do
      o = e.StdOut.ReadLine
      If (e.StdOut.AtEndOfStream) Then
         Exit Do
      Else
         r = r & o & vbLf
      End If
   Loop
   
   CmdOut = r

End Function

 

[rjtaylor]

There is a problem with this code in that there is an embedded function definition but no containing procedure. Is this the complete set of code? I am not familiar with VBS code structure but in general it looks to me like something's missing.

I do not know if VBA can access StdIn and StdOut for streams associated with cmd.exe.

The variable WScript is going to give you trouble. This is a VBS object that does not have a corresponding entity in VBA. Please describe what you want this line of code to do.

   WScript.Echo PortfolioList

However, here is the converted code. I have no idea if it will do what you want it to do. What is "Portfolio format" for a PDF file?

Public Sub VBSRewrite()

   Dim PortfolioList As String
   Dim CmdString As String
  
   CmdString = "for /f " & """" & "delims=" & """" & " %f in ('dir /b *.pdf') do @(echo/ && find " & """" & "/Collection" & """" & " " & """" & "%f" & """" & " >nul && echo PORTFOLIO= " & """" & "%f" & """" & " || echo NOT Portfolio or Error " & """" & "%f" & """" & ")"
  
   PortfolioList = CmdOut(CmdString)
   ' WScript.Echo PortfolioList ' will not work in VBA. where do you want this output to go?

End Sub

Function CmdOut(p As String) As String

   Dim w As Object
   Dim e As Object
   Dim r As String
   Dim o As String
   Dim objShell As Object
  
   Set objShell = CreateObject("WScript.Shell")
   objShell.CurrentDirectory = "C:\Temp"
  
   Set w = objShell
   Set e = w.Exec("Cmd.exe")
  
   e.StdIn.WriteLine p & " 2>&1"
   e.StdIn.Close
  
   While (InStr(e.StdOut.ReadLine, ">" & p) = 0)
   Wend
  
   Do
      o = e.StdOut.ReadLine
      If (e.StdOut.AtEndOfStream) Then
         Exit Do
      Else
         r = r & o & vbLf
      End If
   Loop
  
   CmdOut = r

End Function

Thanks this worked GREAT!!!.
To answer your questions.
First the VBS code does work. You save the code in a text file, then rename the extension as (.vbs)
Second a Portfolio is when you combine different PDF files into one PDF, but each remains its own file (a Collection). The other option is combining PDFs into one continuous PDF file (not a Collection).

 

[6StringJazzer]

I'm pretty happy it worked, especially since I didn't test it.

a Portfolio is when

That's interesting, was not familiar with that. I am baffled as to how your code figures that out.

I have a suspicion that if this being done in VBA there might be a simpler way to do it rather than just porting the VBS over. But it ain't broke, don't fix it.