9 Replies Latest reply on Nov 1, 2013 9:54 AM by Amai Zeg

    Send the filepath of a selected file to an Excel file

    Amai Zeg

      Hello,

       

      I have an Excel file with macros in order to print some variables of a selected file (some kind of history of checks and approvals of a file).

      I'm using GetVersionVars in my Excel macro, which is working fine now.

       

      However, in order to obtain the path of the 'selected' file, I'm using FileName = Application.GetOpenFilename("All Files (*.*),*.*") in my file script (VBA).

      This only works when I have a file saved local. When a file is not saved local I recieve the error: "File not found", when selecting it.

      Using 'Get latest version' solves the problem, but is not the most convenient way. Is there any other way to solve this?

       

      I also made a print button on the datacard. Clicking it opens the Excel file. I guess my problem described above could be solved if I'm able

      to send the filepath of the selected file on the vault (datacard) into my (template) Excel file. Is there an easy way to do this? I'm not so familiar with

      the EPDM add-ins.

       

      Thanks in advance.

        • Re: Send the filepath of a selected file to an Excel file
          Tim Webb

          Hi Amai,

           

          See if this thread is what you are looking for:

           

          https://forum.solidworks.com/message/112620#112620

           

          Tim CEPA

            • Re: Send the filepath of a selected file to an Excel file
              Amai Zeg

              Thank you for your reply Tim, I have been reading the thread. I suppose getting the path in this way is the first step of the solution. The second step would be to open my Excel-file automatically and send the path to it.

               

              However, I will need to put more effort into creating add-ins myself. I'm not so familiar with that and hoped there would be an easier way.

            • Re: Send the filepath of a selected file to an Excel file
              Amai Zeg

              I replaced the FileName = Application.GetOpenFilename("All Files (*.*),*.*") method with:

               

              Dim PathList As EdmStrLst5

              Dim parenthwnd As Integer

                

              Set PathList = objVault.BrowseForFile(parenthwnd)

               

              This results in the same error: it only works when I have a file saved local. When a file is not saved local I recieve the error: "File not found", when selecting it. Using 'Get latest version' solves the problem, but is not the most convenient way.

               

              I do not really understand why this is not working with .BrowseForFile.. For the .GetOpenFilename I can understand that you are actually selecting some kind of ghost file in the C:\... path, if you don't have a local copy.

              • Re: Send the filepath of a selected file to an Excel file
                Amai Zeg

                Ok, this is how I solved it finally:

                 

                The command (when clicking the button on my filecard) is:

                cmd.exe /k cmd /c path\test_toy.bat %1

                 

                The /k command is to prevent my command prompt from closing after running the script (for debugging).

                EPDM opens the command prompt and runs my batch file, test_toy.bat. It does send the filepath to my batch file.

                 

                In my batch (test_toy.bat) file I have the following code:

                 

                echo off

                set EPDM_path=%1
                set EPDM_path=%EPDM_path: =__replace__%

                echo %EPDM_path%


                :: Calculate path length

                set #=%EPDM_path%
                set length=0
                :loop
                if defined # (
                    set #=%#:~1%
                   set /A length += 1
                   goto loop
                )

                start Excel.exe "path\Print_datacard.xlsm" /e/%EPDM_path%/%length%
                pause

                echo on

                 

                Please note that I do replace spaces with "__replace__" in my filepath, in order to solve the problem with using a /e switch with Excel, when there are spaces in the variable: http://superuser.com/questions/640359/bat-file-to-open-excel-with-parameters-spaces/

                 

                In my excel file I added a module with code (http://www.vbforums.com/showthread.php?366559-Excel-How-to-Pass-Command-Line-Parameter-DKenny-is-KING!&p=2234990#post2234990):

                Option Base 0

                Option Explicit

                Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long

                Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long

                Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)

                Function CmdToSTr(Cmd As Long) As String

                    Dim Buffer() As Byte

                    Dim StrLen As Long

                   

                    If Cmd Then

                        StrLen = lstrlenW(Cmd) * 2

                       

                        If StrLen Then

                            ReDim Buffer(0 To (StrLen - 1)) As Byte

                            CopyMemory Buffer(0), ByVal Cmd, StrLen

                            CmdToSTr = Buffer

                        End If

                    End If

                End Function

                 

                And in this workbook I added:

                Private Sub Workbook_Open()
                    Dim CmdRaw As Long
                    Dim CmdLine As String
                    Dim myParam As String
                   
                    CmdRaw = GetCommandLine
                    CmdLine = CmdToSTr(CmdRaw)
                   
                    myParam = CmdLine
                  
                    For i = 1 To Len(myParam)
                        If Mid(myParam, i, 1) = "/" Then
                            pos = i
                        End If
                    Next i
                   
                    Number_of_characters = Right(myParam, Len(myParam) - pos)

                    FilePath = Mid(myParam, Len(myParam) - Number_of_characters - (Len(myParam) - pos), [Number_of_characters])
                   
                    FilePath = Replace(FilePath, "__replace__", " ")

                       
                    MsgBox FilePath

                 

                End Sub

                 

                Please note that I replace "__replace__" back to spaces in my macro.