Need Excel Automation Hyperlink help

[RaymondSamji]

Hi, I have a made program that open the table and put table’s data in Excel Sheet.
Now what I want to do is make hyperlink like (“C:\abc\123.tif” or “

http://www.mywebsite/folderone/subfolder/123.tif”)

in one cell. So if user click that link it will take it to that folder. I have no idea how I am going to do this.
Can any one help me?
Thanks
Raymond Samji

 

[Mike Lewis]

Raymond,

This is off the top of my head, not tested:

WITH loActiveSheet
  oRange = .Range("A1")
  .Hyperlinks.Add(oRange, "[URL unfurl="true"]http://www.tek-tips.com",[/URL] "", ;
     "Click here", "Tek Tips")
ENDWITH

That should place a hyperlink in cell A1, with the URL shown above; the anchor text will be "Tek Tips" and the tooltip should be "Click here". It assumes that loActiveSheet contains an object reference to the active sheet.

I hope this will give you a start.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[bborissov]

Assuming that oExcel is your Excel.Application instance:

oExcel.ActiveSheet.Hyperlinks.Add(oExcel.Range("A1"),;
"[URL unfurl="true"]http://www.mywebsite/folderone/subfolder/123.tif",,;[/URL]
"A picture from My Web Site",;
"See it, it is important")

or

oExcel.ActiveSheet.Hyperlinks.Add(oExcel.Range("A1"),;
"file://C:\abc\123.tif",,;
"A picture from My Web Site",;
"See it, it is important")

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

Remember that as a general rule, to find out how to do something via VFP Excel Automation, first do it manually in Excel alone (No VFP involvement at all) and record the actions as an Excel Macro.

Then the Excel Macro can be examined and the VBA code can most generally be easily converted to VFP Automation code.

Good Luck,
JRB-Bldr

 

[bborissov]

jrbbldr,
Shhhhhhhhh, that is a secret

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Mike Lewis]

JRB-Bldr,

... which is exactly how I came up with the code I posted above.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[RaymondSamji]

Thank you for reply, Mike Lewis, Borislav Borissov and JRB-Bldr. First do it manually in Excel.
Yes sir, JRB-Bldr sound like a good advice. I will do that, and then I use code in VFP.

Thanks Again

Raymond

 

[bborissov]

Mike, did you have ANY suspicions that I didn't?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

Mike - I was certain that you and the other gurus here already knew about doing it that way.

My suggestion was for RaymondSamji and anyone else who might come here looking for similar Excel Automation advice.

RaymondSamji - "I will do that, and then I use code in VFP"
Remember that the Excel Macro VBA code will not transfer EXACTLY into VFP Automation code. It will most often need some 'massaging' (a.k.a. 'converting') from one language into the other.

JRB-Bldr

 

[Mike Lewis]

JRB-Bldr,

My suggestion was for RaymondSamji and anyone else who might come here looking for similar Excel Automation advice.

I realise that. And it's good advice.

Raymond,

I've now tested the code I posted. It works. And I would guess that Borislav's does as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[RaymondSamji]

JRB-Bldr Thanks Again

This time I used Borislav Borissov & Mike Lewis's Code. I modify little instead of "Range("A1")" I used "oSheet.Cells(oRow,oCol)". Work perfect thanks to you folks you gave me idea how to start.

I was wondering is it possible instead of going to specify file, it will go to folder so user can have choice to view any file in folder?

Also Thanks Mike for testing it, I tested here and It work.
Thanks
Raymond Samji

 

[JRB-Bldr]

"I was wondering is it possible instead of going to specify file, it will go to folder so user can have choice to view any file in folder?"

I am not 100% clear on what you want here.

Obviously the Hyperlink can go wherever you point it to go (as long as its a valid address). But it is pretty 'dumb' in that it can only go to one location.

If you want to user to manually designate a destination folder BEFORE the hyperlink is written into the Excel file, then sure.

You can have them execute a GETDIR() to designate a folder or you can allow them to pick one out of a Grid, etc. from a pre-determined list of choices that you present to them in a FORM.

Then that pre-selected choice can be utilized in the Hyperlink address that you write to the Excel file.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Raymond,

You can point a hyperlink to a folder, by using something like the following for the URL (in place of the HTTP:// link):

file://c:\MyFolder

What you get depends on the user's browser and operating system. In Firefox, you get quite a nice display. In Internet Explorer, it's more like a standard folder window. What you won't get is a File Open dialogue, like you see with VFP's GETFILE().

I agree with JRB-Bldr that there are better ways of letting the user pick a file.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[RaymondSamji]

Hello Mike,

Thank you this is exactly what I wanted. I don’t need File Open dialogue.


Hello JRB-Bldr,

My user don’t have VFP program, what I am going to do is email Excel file and user going to click hyperlink to remote to our server to see only folder which I specify in that link, and under that folder I got some image files which he can view by double clicking it.

I hope that make sense

Thank you Again Mike & JRB-Bldr

Raymond Samji