title: Accessing Sheets, Saving Excel files, Java, Java Com Bridge ( Jacob jacob-1.16)
body: <p>I looked at the included Excel example of the latest version of Java Com Bridge (jacob-1.16) and was a little disappointed because it creates and works on a brand new Excel file.</p>
  
  <p>What I would like to do is having access to an existing Excel file from Java, in this case named  'JACOBWithExcel.xls', but any Excel file should be fine.</p>
  
  <p>The SourceForge example for Excel works fine on my machine, and as I modified it for accessing an existing Excel file I ran into the following issues:</p>
  
  <p>1., I could not get an existing sheet in the workbook, even though I am trying the
     same way I got the workbook:</p>
  
  <pre><code>Dispatch sheet = Dispatch.get(workbook, "Worksheets").toDispatch();  
  Dispatch.call(sheet, "Select", new Object[]{"Sheet2"}).toDispatch();
  </code></pre>
  
  <p>This code will generate the following exception:
      com.jacob.com.ComFailException: Can't map name to dispid: Worksheets</p>
  
  <p>2.; Could not save the workbook:</p>
  
  <pre><code>// Save the open workbook as "C:\jacob-1.16-M1\Test1.xls" file:
  Dispatch.call(workbook, "SaveAs", new Variant("C:\\jacob-1.16-M1\\Test1.xls"),new   Variant("1"));
  </code></pre>
  
  <p>This code will generate the following exception:
      com.jacob.com.ComFailException: Can't map name to dispid: SaveAs</p>
  
  <p>3.; I don't know how to get started with the following simple but very common Excel operations, as far as Java syntax for Java COM bridge:</p>
  
  <p>(Included here the Excel VBA code that I am trying to implement in Java)</p>
  
  <p>Selecting a  single cell:
      Range("A4").Select</p>
  
  <p>Copy selected range to clipboard:</p>
  
  <pre><code>Selection.Copy
  </code></pre>
  
  <p>Select multi-cell range to copy to:</p>
  
  <pre><code>Range("D9:D17").Select
  </code></pre>
  
  <p>Paste clipboard contents to selection:</p>
  
  <pre><code>ActiveSheet.Paste
  </code></pre>
  
  <p>Renaming a sheet:</p>
  
  <pre><code>Sheets("Sheet2").Select
  Sheets("Sheet2").Name = "MySheet2"
  </code></pre>
  
  <p>Format cells, example for text:</p>
  
  <pre><code>Selection.NumberFormat = "@"
  </code></pre>
  
  <p>Delete Rows:</p>
  
  <pre><code>Rows(intI).Select
  Selection.Delete Shift:=xlUp
  </code></pre>
  
  <p>And possibly... </p>
  
  <p>Sort a selection:</p>
  
  <pre><code>Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
  OrderCustom:=1, MatchCase:=False,  Orientation:=xlTopToBottom
  </code></pre>
  
  <p>Find the last cell in sheet:                 </p>
  
  <pre><code>ActiveSheet.Cells(65536, 1).End(xlUp).Select
  intLastCellRow = Selection.Row
  </code></pre>
  
  <p>Thank you for your help.</p>
  
  <p>P.S.:</p>
  
  <p>The full code of the application:</p>
  
  <pre><code>import com.jacob.activeX.ActiveXComponent;
  import com.jacob.com.ComThread;
  import com.jacob.com.Dispatch;
  import com.jacob.com.Variant;
  
  public class TestJACOBWithExcel {
  public static void main(String[] args) {
  
  String strInputDoc = "C:\\jacob-1.16-M1\\JACOBWithExcel.xls";  // file to be opened.
  
  ComThread.InitSTA();
  
  ActiveXComponent xl = new ActiveXComponent("Excel.Application"); // Instance of application object created.
  
  try {
  // Get Excel application object properties in 2 ways:
  System.out.println("version=" + xl.getProperty("Version"));
  System.out.println("version=" + Dispatch.get(xl, "Version"));
  
  // Make Excel instance visible:
  Dispatch.put(xl, "Visible", new Variant(true));
  
  // Open XLS file, get the workbooks object required for access:
  Dispatch workbook = xl.getProperty("Workbooks").toDispatch();
  Dispatch.call(workbook, "Open", new Variant(strInputDoc),new Variant("1"));
  
  Dispatch sheet = Dispatch.get(workbook, "Worksheets").toDispatch();
  Dispatch.call(sheet, "Select", new Object[]{"Sheet2"}).toDispatch();
  
  // put in a value in cell A22 and place a a formula in cell A23:
  Dispatch a22 = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { "A22" }, new int[1]).toDispatch();
  
  Dispatch a23 = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { "A23" }, new int[1]).toDispatch();
  
  Dispatch.put(a22, "Value", "123.456");
  Dispatch.put(a23, "Formula", "=A22*2");
  
  // Get values from cells A1 and A2
  System.out.println("a22 from excel:" + Dispatch.get(a22, "Value"));
  System.out.println("a23 from excel:" + Dispatch.get(a23, "Value"));
  
  // Save the open workbook as "C:\jacob-1.16-M1\Test1.xls" file:
  Dispatch.call(workbook, "SaveAs", new Variant("C:\\jacob-1.16-M1\\Test1.xls"),new Variant("1"));
  
  // Close the Excel workbook without saving:
  Variant saveYesNo = new Variant(false);
  Dispatch.call(workbook, "Close", saveYesNo);
  
  } catch (Exception e) {
  e.printStackTrace();
  } finally {
  
  // Quit Excel:
  // xl.invoke("Quit", new Variant[] {});
  ComThread.Release();
  }
  
  }
  }
  </code></pre>