Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Developing U++ » UppHub » [SEMI-SOLVED] OfficeAutomation : How to Set Hyperlink to a Excel cell?
[SEMI-SOLVED] OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33023] Fri, 01 July 2011 15:49 Go to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

OfficeAutomation : How to Set Hyperlink to a Excel cell?

Can I quickly add this functionality to OfficeAutomation?


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}

[Updated on: Sun, 03 July 2011 13:46]

Report message to a moderator

Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33030 is a reply to message #33023] Sat, 02 July 2011 12:32 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

I try to add Method HyperLink to OfficeAutomation. But it not work Sad

Where is my mistake?

bool MSSheet::Hyperlink(int col, int row, String hypertext, String hyperaddress) {
	if (!App)
		return false;	

	VariantOle x, y;
	x.Int4(col);
	y.Int4(row);
	
	if (!(Sheet = Ole::GetObject(App, "ActiveSheet")))
		return false;

	ObjectOle Hyperlinks = Ole::GetObject(Sheet, "Hyperlinks");
	ObjectOle Cell1 = Ole::GetObject(Sheet, "Cells", x, y);
	VariantOle vCell;
	vCell.ObjectOle(Cell1);
	ObjectOle Link = Ole::GetObject(Hyperlinks, "Add", vCell);
	String d;
	if (Link != NULL) d= "Not Null"; else d="Null";
	PromptOK("After assing & link is "+d);
	VariantOle vAddr,vText;
	vAddr.BString(hyperaddress);
	vText.BString(hypertext);
	Ole::SetValue(Link, "Address", vAddr) ;
	Ole::SetValue(Link, "TextToDisplay", vText) ;
	return true;
}


Please help!


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}

[Updated on: Sat, 02 July 2011 12:38]

Report message to a moderator

Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33035 is a reply to message #33030] Sun, 03 July 2011 13:42 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

Semi-Solved!

Idea is to use Cell formula HYPERLINK.

I add function SetFormula(x,y,formula) to package OfficeAutomation.
Officeautomation.cpp:
bool MSSheet::SetFormula(int col, int row, String formula) {
	if (!Sheet)
		return false;

	VariantOle x, y;
	x.Int4(col);
	y.Int4(row);
	
	VariantOle val;
	val.Value(formula);	
	//SetValue(col, row, (formula));
	return Ole::SetValue(Ole::GetObject(Sheet, "Cells", x, y), "FormulaR1C1", val);
}


I'm not shure in this function for OOo(correct me please)
Officeautomation.cpp below:
bool OPENSheet::SetFormula(int col, int row, String formula) {
	if (!SelCell(col, row))
		return false;
	VariantOle vText;
	vText.BString(formula); 
	return Ole::Method(Cell, "setFormula", vText);		
}


OfficeAutomationMethods.h after line 12:
Add line
	virtual bool SetFormula(int col, int row, String formula);	\

OfficeSheet.cpp after line 107:
Add lines
bool SheetPlugin::SetFormula(int col, int row, String formula) {return false;}
bool OfficeSheet::SetFormula(int col, int row, String formula) {return (static_cast<SheetPlugin *>(GetData()))->SetFormula(col, row, formula);}


This is my patch. Pls apply.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33153 is a reply to message #33035] Mon, 11 July 2011 17:25 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello Sergey

=HYPERLINK() is a very good idea.

Just try this with Automation demo:

sheet.SetValue("E23", "=hyperlink(\"http://www.ultimatepp.org\"; \"Ultimate link\")");


It will be included today in Bazaar.


Best regards
Iñaki
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33154 is a reply to message #33153] Mon, 11 July 2011 21:32 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

koldo wrote on Mon, 11 July 2011 19:25

Hello Sergey

=HYPERLINK() is a very good idea.

Just try this with Automation demo:

sheet.SetValue("E23", "=hyperlink(\"http://www.ultimatepp.org\"; \"Ultimate link\")");


It will be included today in Bazaar.


This don't work. I already try it.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33155 is a reply to message #33154] Mon, 11 July 2011 23:03 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello Sergey

It is uploaded. Please try it Smile.


Best regards
Iñaki
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33164 is a reply to message #33155] Tue, 12 July 2011 14:44 Go to previous messageGo to next message
ratah is currently offline  ratah
Messages: 107
Registered: July 2010
Experienced Member
Hi,

I come here rarely, i'm busy with my job now.
I send you another library for OfficeAutomation and whatever you want : the "DispHelper"

It demonstrates using COM objects including ADO, CDO, Outlook, Eudora, Excel, Word, Internet Explorer, MSHTML, PocketSoap, Word Perfect, MS Agent, SAPI, MSXML, WIA, dexplorer and WMI

Here is the links
http://cvs.sourceforge.net/viewcvs.py/disphelper/disphelper/
sourceforge.net/projects/disphelper/

--------------

I use the plugin OfficeAutomation and have this little bug:

The EXCEL.EXE does not close correctly!

Here is a program test and bug report.
index.php?t=getfile&id=3373&private=0
I do not know where is the problem.
  • Attachment: excel_bug.jpg
    (Size: 100.22KB, Downloaded 634 times)
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33165 is a reply to message #33164] Tue, 12 July 2011 14:46 Go to previous messageGo to next message
ratah is currently offline  ratah
Messages: 107
Registered: July 2010
Experienced Member
the sample code
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33175 is a reply to message #33165] Wed, 13 July 2011 09:50 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello Ratah

Sorry. I run your sample (thank you, it is simple and complete Smile) but I do not get any lost EXCEL.EXE process.

I use Windows XP SP3 and Office 2003.

A detail not related with your problem: In your sample you open sheet var first with "Open" and then with "Microsoft". As you open both with the same var the second plugin kills the first so

sheet.Init("Open");


can be deleted.


Best regards
Iñaki
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33180 is a reply to message #33175] Wed, 13 July 2011 13:45 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

Formula Hyperlink work fine!
and Excel don't crash during close OLE connection.!!!

Are you seen Hyperlink fuction at the top of the forum thread ?


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33181 is a reply to message #33180] Wed, 13 July 2011 14:05 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

My Code to work with OfficeAutomation:

void MainWindow::ExportExcel(){
	
	SelectColumnsDlg dlg;
	
	if(dlg.Execute() != IDOK)
		return;

	OfficeSheet sheet;
	String fn  = AppendFileName(GetDesktopFolder(), "Export.xls");
	bool openAvailable = sheet.IsAvailable("Open");
	bool microsoftAvailable = sheet.IsAvailable("Microsoft");
	if (openAvailable) {
		sheet.Init("Open");
	}
	if (microsoftAvailable) {
		sheet.Init("Microsoft");
	}

	//if (!sheet.OpenSheet(fn, true)) {
		sheet.AddSheet(true);
	//}	
	
	int col=1;
	#define eq(n,m,a) if (~dlg.n ==1) sheet.SetValue(col++, 1, m); a; 
	eq(opt_number,t_("Number"),)
	eq(opt_address,t_("Address"),sheet.SetColWidth(col-1, 60))
	eq(opt_time1,t_("Time from"),sheet.SetColWidth(col-1, 30))
	eq(opt_time2,t_("Time to"),sheet.SetColWidth(col-1, 30))
	eq(opt_price,t_("Price"),)
	eq(opt_tax,t_("Tax"),)
	eq(opt_makeprice,t_("Make price"),)
	eq(opt_maketax,t_("Make tax"),)
	eq(opt_size,t_("Size"),sheet.SetColWidth(col-1, 30))
	eq(opt_link,t_("Link"),sheet.SetColWidth(col-1, 30))
	eq(opt_status,t_("Status"),sheet.SetColWidth(col-1, 30))

	sheet.Select(1, 1, col-1, 1);
	sheet.SetBold(true);	
	
	int count=0;
	LOG("Start Export--------------------------");
	for(int i = 0; i < listobj.GetCount() ; i++) {
		String link = LinkCalc(listobj(i,OBJ_ID));
		if (listobj(i,Id("Select")) == 1) {
			int col=1;
			#undef eq
			#define eq(n,m) if (~dlg.n ==1) sheet.SetValue(col++, i+2, m);
			#define eq1(n,m) if (~dlg.n ==1) m;
			eq(opt_number,AsString(count+1))
			eq(opt_address,AsString(listobj(i,OBJ_ADDRESS)))
			eq(opt_time1,DateConvert().Format(listobj(i,OBJ_TIME_FROM)))
			eq(opt_time2,DateConvert().Format(listobj(i,OBJ_TIME_TO)))
			eq(opt_price,FormatL(listobj(i,OBJ_PRICE)))
			eq(opt_tax,AsString(listobj(i,OBJ_TAX)))
			eq(opt_makeprice,FormatL(listobj(i,OBJ_MAKEPRICE)))
			eq(opt_maketax,AsString(listobj(i,OBJ_MAKETAX)))
			eq(opt_size,AsString(listobj(i,OBJ_SIZE)))
			eq1(opt_link,sheet.SetValue(col++, i+2,"=HYPERLINK(\""+link+"\";\"Presentation\")"));
			eq(opt_status,AsString(listobj(i,OBJ_STATUS)))
			count++;
		}
		
	}
	PromptOK("End Export&"+AsString(count)+" lines exported");
	LOG("End Export----------------------------");
}


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33182 is a reply to message #33175] Wed, 13 July 2011 14:23 Go to previous messageGo to next message
ratah is currently offline  ratah
Messages: 107
Registered: July 2010
Experienced Member
Thank you,

I have the bug when "test.xls" is not yet created.
Try to delete this file and reexecute you program.
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33184 is a reply to message #33180] Wed, 13 July 2011 15:37 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
sergeynikitin wrote on Wed, 13 July 2011 13:45

Formula Hyperlink work fine!
and Excel don't crash during close OLE connection.!!!

Are you seen Hyperlink fuction at the top of the forum thread ?


Hello Sergey

I tried a different version of SetHyperlink but it does not work. It will be in the code (after uploading it today).

At first your version did not seem to be right for me but, in Office OLE often there are more than one way to do something. Does it work for you?


Best regards
Iñaki
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33185 is a reply to message #33184] Wed, 13 July 2011 16:00 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 746
Registered: January 2008
Location: Moscow, Russia
Contributor

Your code work fine. Thank you.

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #33186 is a reply to message #33185] Wed, 13 July 2011 17:05 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello Ratah

Yes, I see the problem. I have working with it unsuccessfully... Sad


Best regards
Iñaki
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #40804 is a reply to message #33186] Fri, 20 September 2013 10:35 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello Ratah

Problem solved, please check.

Now OfficeAutomation under no circumstance leaves an EXCEL instance running after closing it Smile.


Best regards
Iñaki
Re: OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #40852 is a reply to message #40804] Mon, 23 September 2013 13:22 Go to previous messageGo to next message
ratah is currently offline  ratah
Messages: 107
Registered: July 2010
Experienced Member
Great to see you and your resolution Koldo

Thanks
Re: [SEMI-SOLVED] OfficeAutomation : How to Set Hyperlink to a Excel cell? [message #40856 is a reply to message #33023] Mon, 23 September 2013 16:11 Go to previous message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Laughing

Please check if it works for you.

It uses a dirty trick to get ActiveX process id Smile


Best regards
Iñaki
Previous Topic: AESStream and Core/SSL libraries
Next Topic: MAPIEx compilation problem in Win7
Goto Forum:
  


Current Time: Tue Dec 07 16:05:12 CET 2021

Total time taken to generate the page: 0.01917 seconds