2 Replies Latest reply: Mar 25, 2015 8:33 AM by Thorsten Schröder RSS

    Macro to check if exact path exists based on server name

    Thorsten Schröder

      Hello community,

       

      I want to transfer my Excel macro (see below) to QlikView in order to get the following job done:

       

      I retrieve five files from five servers.

      Each file contains a list of paths to certain folders on that particular server. I want to check if those exact paths actually exist on that server and mark it green or red in QlikView.

       

      Details:

      Each file contains the following path column with records like so:

      {SERVERPATH}\abc\def\ghi\123\456\folderX

      {SERVERPATH}\xyz\uvw\rst\789\123\456\folderY

      {SERVERPATH}\one\two\three\four\five\six\seven\folderZ

       

      In an inline table I use the filename as a key and add the column serverpath.

      SERVERPATH

      \\server1\subdir

      \\server2\subdir

      \\server3\subdir

      \\server4\subdir

      \\server5\subdir

       

      So my goal would be to select one file in QlikView and then click a button which checks if those paths listed in the file actually exist on the corresponding server.

       

      My working Excel-Macro looks like this:

      Dim MyCheckDir As String
      Dim iRow As Integer
      iRow = 2
      Do Until IsEmpty(Cells(iRow, 3))
              
          MyCheckDir = Range("SERVERPATH").Value & Mid(Cells(iRow, 3).Value, 12) & "\"
      
          If Dir(MyCheckDir, vbDirectory) <> "" Then
                 Cells(iRow, 6).Value = "OK"
          Else
                 Cells(iRow, 6).Value = "NO"
          End If
          iRow = iRow + 1
      Loop
      

       

      Hope you can show me a way to get this done with QlikView.

       

      Thank you,

      Thorsten