8 Replies Latest reply on Jan 6, 2014 3:55 PM by Artem Taturevych

    Open file from Excel that is in ePDM

    Neil Sommerville

      I am trying to write a simple VBA code in Excel that will open up a list of excel files (200+), grab some data from each them and place it into a single Excel file.   If these files were located on typical network directory I wouldn't have any issues, but for some reason when the file is in PDM it returns a run-time error and says either the directory or the file doesn't exsist.

       

      There is nothing wrong with the text string that calls out the directory and file name.  I know this becasue if I open file manually in Excel, then if I close the file and run the code it opens the file up without any problem.  Why does Excel (or PDM) let me open a file through the Excel Open Dialoag Box manually, but not through VBA Workbooks.Open?   Why if I open the file first and close it will the VBA Workbooks.Open work fine?  Is there some PDM Reference Libary that I should be using to tell PDM it is ok to open this file?

       

      The Macro below is in a file called "EC Log.xlsm".  "Worksheets("Search Result").Range("G" & i).Value" is the directory name directly from a PDM Search that I exported.  And "Worksheets("Search Result").Range("A" & i).Value" is the file name from the same PDM Search.

       

      Again, this works great if it was anywhere else but in ePDM.  I have even tried to had a "ChDir" before the ",Open" but it does the exact same thing.

       

      Sub Macro2()

      '

      Dim i, i2

       

       

      i = 2 'list row

      i2 = 7 'data row

      Do

          Workbooks.Open Filename:=Worksheets("Search Result").Range("G" & i).Value & "\" & Worksheets("Search Result").Range("A" & i).Value, ReadOnly:=True

          Windows("EC LOG.xlsm").Activate

          Range("A" & i2).Value = Workbooks(Worksheets("Search Result").Range("A" & i).Value).Worksheets("CHANGE REQUEST FORM").Range(Range("A4").Text).Value

          '***

          '* more copied data code goes in this area (about 50 cells).

          '***

          Workbooks(Worksheets("Search Result").Range("A" & i).Value).Close

          i = i + 1

          i2 = i2 + 1

      Loop Until Worksheets("Search Result").Range("A" & i).Value = ""

       

       

      End Sub