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.
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.
Would it be possible to use the command string of a button on a file card to send the filepath to my excel file, e.g. Folder Path (%5)?
I can open my file with the command string, but when I trie to use switches for the filepath, e.g. /e/Folder Path (%5), it is not working correctly.
I'm not sure what you are trying to do but take a look at my "openword.dll" on my site, load it, and run it with the instructions provided. It is a simple addin that sort of explains how to interact with Office files.
This is NOT what you are asking for, I realize that, but let me know if that add-in captures the "general idea" you are wanting to accomplish.
I can consult that for you to get it done.
What I'm trying to achieve is to pass the filepath of a selected file in the vault into my Excel file. My last guess was to use a 'Command String', when clicking a button on a file card, and than use for the command something like:
Call "path\test_toy.bat" %1
Where %1, is the filepath according to:
In my .bat I will open the Excel file and pass the filepath.
However, when running this command, EPDM tries to open 3 files (Call, "path\test_toy.bat", and %1), instead of just running the command like it would do in DOS. Removing the Call, so "path\test_toy.bat" %1, will result in trying to open 2 files.
Thank you for your link to your website. I will study this.
Ok I think I see where you are going. You are NOT wanting to "open" the filename passed, you are wanting to pass the filename as a text string to the excel application so excel opens with the filename passed in being added right into one of the cells. Right?
Hmmmm.... Take a look at this page
Yeah, see if that works. If not, you may need an add-in.
Thank you very much for your quick reply and help. You are indeed correct about what I'm trying to do.
By chance, I already found the same page as in your link. I tried the /e switch in the command string in this way:
"path\EXCEL.EXE" path\Print_filenames.xlsm /e/%2
However, when I change %2 (vault name) to %1 (file path), I will get problems, because there is a space in one of the folder names in the path. Therefore, EPDM/Excel will try to open the part after the space in my filepath as a new file. So if my path is something like:
C:/EPDM/myvault/project design/Print_filenames.xlsm, Excel will try to open: design/Print_filenames.xlsm.
I guess this is the same problem: http://superuser.com/questions/640359/bat-file-to-open-excel-with-parameters-spaces/
Therefore my latest idea was to try to send the filepath to a .bat file, manipulate the filepath so there are no spaces in it anymore, open Excel and send the path with the /e switch, and than use a macro in Excel to manipulate the filepath so the spaces are back in it ;-).
However, I'm not sure if I will receive the same errors when I send the variable to the .bat file. At this moment I'm not able to send the variable filepath to a .bat file at all.
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:
set EPDM_path=%EPDM_path: =__replace__%
:: Calculate path length
if defined # (
set /A length += 1
start Excel.exe "path\Print_datacard.xlsm" /e/%EPDM_path%/%length%
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
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
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
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__", " ")
Please note that I replace "__replace__" back to spaces in my macro.