9 Replies Latest reply on Aug 17, 2018 11:14 AM by Jim Sculley

    Macro to pull in DXF/PDF from server is missing files

    Geoff Moss

      I made a VBA macro for Outlook to scan the text of an email, pull out part numbers, and display a dialog box listing the results to allow the user to automatically attach a matching DXF or PDF file from our server. Reading the text, matching the regex, etc., all goes fine. However many files that do exist in the proper location with the proper name are not attached. It's like I have to manually view them in Windows before the macro can see them.

       

      How do I make it so it can see all the files in my vault without manually *Getting* them through PDM or Windows? I attached my Macro code and form, it's fairly simple, here's the part where I try to get the files:

       

      Sub GetPDMFiles(Optional pdfFile As String = "", Optional dxfFile As String = "")

          Dim i As Integer

          i = 0

        

          If pdfFile <> "" Then

              foldersToGet(i).mlDocID = vault.GetFileFromPath(pdfFile).iD

              foldersToGet(i).mlProjID = vault.GetFolderFromPath(pdfDir).iD

              i = i + 1

          End If

        

          If dxfFile <> "" Then

              foldersToGet(i).mlDocID = vault.GetFileFromPath(dxfFile).iD

              foldersToGet(i).mlProjID = vault.GetFolderFromPath(dxfDir).iD

              i = i + 1

          End If

        

          fileGetter.AddSelection vault, foldersToGet

          fileGetter.CreateTree 0, EdmGetCmdFlags.Egcf_Nothing

          fileGetter.GetFiles 0, Nothing

      End Sub

        • Re: Macro to pull in DXF/PDF from server is missing files
          Danniel Sims

          Geoff,

           

          I've found with Outlook, that often it autocopies a space, tab or carriage return pretty frequently.

          It may not be your issue, but I remove all these in the string before passing it to my search.

          I don't work with PDM, so I can't really relate to that side of it, but I don't see any code for the attachment.  The macro I made takes the active selection in the message.  I know that if someone pops the mail out it changes how the macro interacts with it.  It has to be in the reader pane or it changes whether it is an "Inspector" item or an "Explorer" item.

           

              '*** REMOVE SPACES ****

              If Left(strText, 1) = " " Or Right(strText, 1) = " " Then

                  strText = Trim(strText)

              End If

             

              '***REMOVE TABS ******

              If Left(strText, 1) = Chr(9) Then

                  strl = Len(strText) - 1

                  strText = Right(strText, strl)

              End If

             

              If Right(strText, 1) = Chr(9) Then

                  strl = Len(strText) - 1

                  strText = Left(strText, strl)

              End If

             

              '*** REMOVES RETURNS ********

              If Left(strText, 1) = Chr(13) Then

                  strl = Len(strText) - 1

                  strText = Right(strText, strl)

              End If

             

              If Right(strText, 1) = Chr(13) Then

                  strl = Len(strText) - 1

                  strText = Left(strText, strl)

              End If

            • Re: Macro to pull in DXF/PDF from server is missing files
              Geoff Moss

              Thanks for the response Danniel. I have run into those or similar issues before, however I don't think they are at fault here. Parsing the text and pulling out the part numbers all works fine, it's only retrieving the actual file that's the problem. I have a dialog pop up and show which files it successfully found, and also show a list of what it detected as part numbers, but did not find a matching PDF for.

               

              My problem is that valid part numbers that do in fact have a PDF in the correct location aren't visible to the macro unless I've viewed them in Windows Explorer, and have a copy in my local cache. If I manually view each PDF before running my macro, they attach just fine, but if I try with part numbers I haven't viewed, it acts like no file is available.

            • Re: Macro to pull in DXF/PDF from server is missing files
              Geoff Moss

              For convenience, here's the full code (FileSelector is the name of the Form that displays the files found):

               

              Option Explicit

              Public Const vaultName As String = "COMPANY_NAME"

              Public Const vaultDrive As String = "C:/"

               

              Public Const pdfDir As String = vaultDrive & vaultName & "/PDF/"

              Public Const dxfDir As String = vaultDrive & vaultName & "/DXF Files/"

              Public Const drwDir As String = vaultDrive & vaultName & "/Drawings/"

               

              Public objOL As Application

              Public objMailItem As Object

               

              Public vault As EdmVault5

              Public foldersToGet() As EdmSelItem

              Public getAll() As EdmSelItem

              Public fileGetter As IEdmBatchGet

               

              Sub InsertItemLink()

                  On Error Resume Next

                 

                  Dim objRegEx As Object, objFileSys As Object, allMatches As Object

                  Dim urlPDF As String, urlDXF As String, textInput As String, textOutput As String

                  Dim partNumberPattern As String, partNumber As String, seriesNumber As String

                  Dim filesMissing As String, pdfsFound As String, dxfsFound As String, tempMatch As String

                  Dim i As Integer, j As Integer, iPDF As Integer, iDXF As Integer, bump As Integer

                  Dim sortedResults() As String

               

                  Set objOL = Application

                  Set objMailItem = objOL.ActiveInspector.CurrentItem

                  Set objRegEx = CreateObject("VBScript.RegExp")

                  Set objFileSys = CreateObject("Scripting.FileSystemObject")

                  Set fileGetter = vault.CreateUtility(EdmUtil_BatchGet)

                     

                  Set vault = New EdmVault5

                  If Not vault.IsLoggedIn Then

                      vault.LoginAuto vaultName, 0

                  End If

                 

                  With objRegEx

                      .ignorecase = True

                      .MultiLine = True

                      .Global = True

              '       .Pattern = "\b(\d{8}(?:-\d{1,2})?|MC?R[\d-]{4,8}[a-z]?|(?:xx-)?[a-z]{1,2}\d{2,3}-?\d{1,5}(?:-[a-z]{1,3})?(?:-blk)?|SN\d{4}|\d{5})\b"

                      .Pattern = "\b(\d{8}(?:-\d{1,2})?|MC?R[\d-]{4,8}[a-z]?|[a-z]{1,2}(?:\d{2,3})?-\d{1,5}(?:-[a-z]{1,3})?(?:-blk)?|SN\d{4}|\d{5})\b"

                  End With

                 

                  FileSelector.PDFList.Clear

                  FileSelector.DXFList.Clear

                     

                  textInput = objMailItem.HTMLBody

                  partNumberPattern = "<a href='" & pdfDir & "$1.pdf'>$1</a>"

                  Set allMatches = objRegEx.Execute(textInput)

                 

                  iPDF = 0

                  iDXF = 0

                 

                  GetAllNewFiles

                 

                  With FileSelector.PDFList

                      For i = 0 To allMatches.Count - 1

                          partNumber = UCase(allMatches.Item(i))

                 

                          If Not InStr(pdfsFound, partNumber) Then

                              urlPDF = pdfDir & partNumber & ".pdf"

                              urlDXF = dxfDir & partNumber & ".dxf"

                              GetPDMFiles urlPDF, urlDXF

                              GetPDMFiles "xx-" & urlPDF

                         

                              If objFileSys.fileExists(urlPDF) Then

                                  .AddItem partNumber

                                  .Selected(iPDF) = True

                                 

                                  pdfsFound = pdfsFound & ", " & partNumber

                                  iPDF = iPDF + 1

                              Else

                                  urlPDF = "xx-" & urlPDF

                                 

                                  If objFileSys.fileExists(urlPDF) Then

                                      .AddItem partNumber

                                      .Selected(iPDF) = True

                                     

                                      pdfsFound = pdfsFound & ", " & partNumber

                                      iPDF = iPDF + 1

                                  Else

                                      filesMissing = filesMissing & partNumber & ", "

                                  End If

                              End If

                             

                              If objFileSys.fileExists(urlDXF) Then

                                  FileSelector.DXFList.AddItem partNumber

                                  FileSelector.DXFList.Selected(iDXF) = False

                                  iDXF = iDXF + 1

                              End If

                          End If

                      Next i

                  End With

                 

                  FileSelector.missingFiles.Text = Left(filesMissing, Len(filesMissing) - 2)

                  FileSelector.Show

              End Sub

               

               

              Sub GetPDMFiles(Optional pdfFile As String = "", Optional dxfFile As String = "")

                  Dim i As Integer

                  i = 0

                 

                  If pdfFile <> "" Then

                      foldersToGet(i).mlDocID = vault.GetFileFromPath(pdfFile).ID

                      foldersToGet(i).mlProjID = vault.GetFolderFromPath(pdfDir).ID

                      i = i + 1

                  End If

                 

                  If dxfFile <> "" Then

                      foldersToGet(i).mlDocID = vault.GetFileFromPath(dxfFile).ID

                      foldersToGet(i).mlProjID = vault.GetFolderFromPath(dxfDir).ID

                      i = i + 1

                  End If

                 

                  fileGetter.AddSelection vault, foldersToGet

                  fileGetter.CreateTree 0, EdmGetCmdFlags.Egcf_SkipOpenFileChecks

                  fileGetter.GetFiles 0, Nothing

              End Sub

               

               

              Sub GetAllNewFiles()

                  getAll(0).mlDocID = 0

                  getAll(0).mlProjID = vault.GetFolderFromPath(dxfDir).ID

                 

                  fileGetter.AddSelection vault, getAll

                  fileGetter.CreateTree 0, EdmGetCmdFlags.Egcf_SkipExisting

                  fileGetter.GetFiles 0, Nothing

                 

                  getAll(0).mlDocID = 0

                  getAll(0).mlProjID = vault.GetFolderFromPath(pdfDir).ID

                 

                  fileGetter.AddSelection vault, getAll

                  fileGetter.CreateTree 0, EdmGetCmdFlags.Egcf_SkipExisting

                  fileGetter.GetFiles 0, Nothing

              End Sub

               

               

               

              Private Sub btnAttach_Click()

                  Dim i As Integer

                  Dim fileName As String

                 

                  FileSelector.Hide

                 

                  With FileSelector.PDFList

                      For i = 0 To .ListCount - 1

                          If .Selected(i) Then

                              fileName = pdfDir & .List(i) & ".pdf"

                              objMailItem.Attachments.Add (fileName)

                          End If

                      Next i

                  End With

                 

                  With FileSelector.DXFList

                      For i = 0 To .ListCount - 1

                          If .Selected(i) Then

                              fileName = dxfDir & .List(i) & ".dxf"

                              objMailItem.Attachments.Add (fileName)

                          End If

                      Next i

                  End With

                 

                  Unload Me

              End Sub

                • Re: Macro to pull in DXF/PDF from server is missing files
                  Jim Sculley

                  Geoff Moss wrote:

                   

                  For convenience, here's the full code (FileSelector is the name of the Form that displays the files found):

                   

                  Option Explicit

                  Public Const vaultName As String = "COMPANY_NAME"

                  Public Const vaultDrive As String = "C:/"

                   

                  Public Const pdfDir As String = vaultDrive & vaultName & "/PDF/"

                  Public Const dxfDir As String = vaultDrive & vaultName & "/DXF Files/"

                  Public Const drwDir As String = vaultDrive & vaultName & "/Drawings/"

                   

                  Public objOL As Application

                  Public objMailItem As Object

                   

                  Public vault As EdmVault5

                  Public foldersToGet() As EdmSelItem

                  Public getAll() As EdmSelItem

                  Public fileGetter As IEdmBatchGet

                   

                  Sub InsertItemLink()

                  On Error Resume Next

                   

                   

                  Comment out that last line above.  It will mask any errors that are being thrown, making the cause of the problem impossible to determine. 

                   

                  After tweaking your code slightly to match my vault setup, it fails in the GetPDMFiles subroutine.  Specifically, the GetFileFromPath call returns Nothing, from which you then try to get the ID, which is an error.

                   

                  The root cause of this is that Windows paths use back slashes '\', not forward slashes'/'.  Change the four const declarations above to use back slashes and try it out. It still errors out, but now the problem is a subscript out of range error since size of the foldersToGet array was never specified. 

                   

                  There are probably other errors beyond this, but I'll leave it to you to sort them out now that you've removed the On Error Resume Next nonsense.

                    • Re: Macro to pull in DXF/PDF from server is missing files
                      Geoff Moss

                      Jim, thanks for the reply and the tips. Firstly I will state that I know enough about programming in general to know that GoTo, On Error, etc. are bad practices; unfortunately I'm a little over my head here and this is mostly cobbled together from other examples, so my lack of VBA knowledge makes it tough to figure out which statements I can (or should) remove from examples.

                       

                      That said, I did make both the filepath and On Error changes you suggested. I now see the same issue as you state, that it fails trying to populate the foldersToGet array. Would you be able to help me past that one too? Apparently I don't understand arrays very well, and yet can't find anything online to help initialize it. I've tried using ReDim to manually set the size, and I've tried moving the declaration into that specific GetPDMFiles instead of the parent sub, neither seems to make a difference. I originally thought I could just use arrays the same as other variables, evidently that's not the case; what am I missing here?

                        • Re: Macro to pull in DXF/PDF from server is missing files
                          Josh Brady

                          Since you're using VBA, just declare your variables that receive arrays as returns to be Variant.  What you'll end up with is a Variant that is an array of whatever things are returned by the function.

                          • Re: Macro to pull in DXF/PDF from server is missing files
                            Jim Sculley

                            Geoff Moss wrote:

                             

                            Jim, thanks for the reply and the tips. Firstly I will state that I know enough about programming in general to know that GoTo, On Error, etc. are bad practices; unfortunately I'm a little over my head here and this is mostly cobbled together from other examples, so my lack of VBA knowledge makes it tough to figure out which statements I can (or should) remove from examples.

                             

                            That said, I did make both the filepath and On Error changes you suggested. I now see the same issue as you state, that it fails trying to populate the foldersToGet array. Would you be able to help me past that one too? Apparently I don't understand arrays very well, and yet can't find anything online to help initialize it. I've tried using ReDim to manually set the size, and I've tried moving the declaration into that specific GetPDMFiles instead of the parent sub, neither seems to make a difference. I originally thought I could just use arrays the same as other variables, evidently that's not the case; what am I missing here?

                            Possibly, you are missing the fact that VBA arrays are indexed starting at 1 not 0.  So, change i = 0 to i  = 1 and that along with Redim will help.