3

I have a dataset and I write it's values to an excel file using Microsoft Jet 4.0 Oledb provider. I want to put hyperlinks into the excel, for this I write "=HYPERLINK("http://www.abc.org/x.pdf")" in DS and then write it to excel. But when the excel is opened it prefixes a ' (quote/apostrophe) before the formula and there it comes as a text, not link. Can you help me removing that quote so that my excel file has hyperlinks instead of that as text?

I need to use Microsoft Jet 4.0 provider (not excel component) as the client machine may or may not have MS-Excel installed on his machine.

il_guru
  • 8,383
  • 2
  • 42
  • 51
Saubhagya
  • 1,075
  • 1
  • 9
  • 12

1 Answers1

0

Jet will provide data integration with Excel. You can read/write data. Not formulas or formatting or anything else.

To do what you want you'll need to output formatted data. You can do this by generating an HTML file and telling the client it's Excel which will work fine but has minor side effects. A better option would be to use an actual Excel binary writer. A lot of options here:

Import and Export Excel - What is the best library?

Community
  • 1
  • 1
Samuel Neff
  • 73,278
  • 17
  • 138
  • 182
  • I was about to ask essentially this same question (regarding the inserted ' before all fields regardless of their data type). One would think that, since the Ole architecture receives server type information from me when I issue Linq queries, it would at least know not to change the type of an int to a string? – Reinderien Apr 01 '11 at 01:16
  • @Reinderien, in my many many years of experience programming against Excel, the one constant I've found is that common sense never applies. – Samuel Neff Apr 01 '11 at 02:48
  • I disagree, you **can** include a formula in your query, as a column in your ADO DB recordset, then use *CopyFromRecordset* to output the resultset to the worksheet – Our Man in Bananas Jun 26 '13 at 19:08
  • @Philip, `CopyFromRecordset` is an Excel VBA function. It can be used when automating Excel, but not when writing a program purely against the Jet engine to read/write XLS files. The OP specifically said they want to use their application on a computer that may not have Excel installed, which is required for Excel automation and `CopyFromRecordset` – Samuel Neff Jun 27 '13 at 02:45
  • Ah, I misunderstood, sorry my bad :) – Our Man in Bananas Jun 27 '13 at 08:52