0

I have a tool I've automated that creates multiple spreadsheets from a file and the next addition to the process would be to use Spreadsheet Compare to get the differences between 2 of the files created, export the results, then modify the formatting of the resulting sheet. I have the code working to open Spreadsheet Compare but I don't know how to feed the file locations of the sheets to it correctly.

Based on this link and this link tried setting up 2 variables with the locations and joining them in one variable to enter in the shell() and also adding both variables in the shell() but neither worked. I can tell it is effecting the file load though since I gives me an error from Spreadsheet Compare that reads "Please specify 2 files to compare", which it did not when I was only opening the application without trying to feed it files.
As per FreeMan's suggestion, here is very format I have tried;

-C:\file1.xlsx C:\file2.xlsx
-"C:\file1.xlsx C:\file2.xlsx"
-"C:\file1.xlsx" "C:\file2.xlsx"
-'C:\file1.xlsx C:\file2.xlsx'
-'C:\file1.xlsx' 'C:\file2.xlsx'
-[C:\file1.xlsx C:\file2.xlsx]
-[C:\file1.xlsx] [C:\file2.xlsx]
-(C:\file1.xlsx C:\file2.xlsx)
-(C:\file1.xlsx) (C:\file2.xlsx)
-all of the above in a single variable (first method)
-all of the above split between 2 variables (second method)
-all of the above from run
-all of the above from run using a .txt file for the file locations

Sub tCompare()
Dim strRoboappPath As String, varProc As Variant
Dim strArg As String
Dim var1 As String
Dim var2 As String
var1 = "C:\file1.xlsx"
var2 = "C:\file2.xlsx"

On Error Resume Next

strRoboappPath = "C:\Program Files (x86)\Microsoft Office\Office15\DCF\SPREADSHEETCOMPARE.exe"
strArg = var1 + " " + var2

varProc = Shell("""" & strRoboappPath & """ """ & strArg & """") 'first method
varProc = Shell("""" & strRoboappPath & """ """ & var1 & """ """ & var2 & """") 'second method

End Sub

I'm looking for Spreadsheet Compare to open with both files already loaded.

user10348094
  • 13
  • 4
  • 1
    in the `Immediate Window` (Ctrl-G) of your VBE, type `?"""" & strRoboappPath & """ """ & var1 & """ """ & var2 & """"` (copy/paste from the second method). Look at the output of that to ensure that you've got all the right quote marks in the right places. If it looks right, copy/paste that into the Windows `Run` command & see what happens. Tweak your command string at the `Run` command or a command prompt until it's working, then duplicate the results back into your VBA code. – FreeMan May 17 '19 at 17:13
  • The quotes are correct and it behaves in Run the same as it does in my code. Telling me to "tweak" until it works isn't particularly helpful since it's just more blind experimenting on my part that isn't getting me anywhere, which is why I asked here.... – user10348094 May 17 '19 at 19:01
  • because [tag:excel-vba] is deprecated – FreeMan May 17 '19 at 19:27
  • Knowing that you've tested those things out and that the quotes are correct will be helpful for someone who has more knowledge of the `Shell()` command than I do. It never hurts to ask the obvious questions, like "Is it plugged in?" (long but true story...) – FreeMan May 17 '19 at 19:28
  • Also, knowing that you can't get SpreadsheetCompare.exe to work from the command line either gives you (and others) another avenue to explore in looking for an answer. – FreeMan May 17 '19 at 19:30
  • "[the prompt] behaves in Run the same as it does in my code". My code opens the application, just not with the files to compare. Spreadsheet Compare works from the command line and from my code but I do not know the correct way to feed the files as a parameter when doing so. I'll update my question to include every single format that has not worked. – user10348094 May 17 '19 at 20:43

0 Answers0