How to add picture in Excel Comment Box from VFP?

[geminihk]

Windows XP/Excel 2003/VFP 6.0

 

[Olaf Doschke]

Record whatever you want foxpro to do via ole automation inside Excel, read the recorded Excel macro. This needs to be slightly ported to vfp syntax, as in VFP your root element always is the application object and call convention differs in VBA and VFP. You'll see for yourself.

Bye, Olaf.

 

[geminihk]

Thanks.

 

[Mike Lewis]

A picture in a comment?

As far as I know, you can't do that. The comment feature only takes text, and you only have limited control over its formatting.

You can place a picture directly in the worksheet, and set it to "move and size with cells", but that's the closest to what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[geminihk]

Is it possible I can save a macro in the excel created from FoxPro?

 

[Olaf Doschke]

Created from foxpro? You create macros in Excel via recording. You can call a macro embedded in the excel sheet, yes, via Ole Automation you can access Excel application features, documents and document objects, also their macros.

Perhaps look out for Hentzenwerkes books on office automation.

As Mike I wonder how you'd put a picture in a comment, but if you say you can I believe you, perhaps we think of a different thing than you.

Bye, Olaf.

 

[Mike Lewis]

I don't have Excel 2003, but I've just been looking at both 2000 and 2010. In both cases, there doesn't appear to be any way of inserting a picture into a comment.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[geminihk]

Thanks. You can add but can't save the excel. So I have to do from excel macro.

oExcel.ActiveWorkbook.VBProject.VBComponents([ThisWorkbook]).CodeModule.AddFromFile("D:\BCSQuot\DLL\CommentImage.txt")
oExcel.ActiveWorkbook.Save

Does the above stmt save macro in excel?

I have added a macro Autpen() when ever the file opens i do some work.

 

[Olaf Doschke]

You can check that yourself. Look into the Macros of the Excel Workbook and see if it's there.

If I add a macro to some XLS(X) and save, the macro is saved, too. It can also be set to get part of Normal.XLT or some other Excel template.

Anyway, what you have there is an execl problem, if excel macros don't save into your xls files, then perhaps ask an Excel expert. It will not matter how the macros got there. It will rather be settings of Excel and the Excel workbook. You can turn off emacros for security and all kind of settings I as a non Excel expert don't know could have an effect.

Bye, Olaf.

 

[Olaf Doschke]

Perhaps we're talking differnt things:

I talk about excel recording macros to see how a comment is added to some cell in VBA Code, not to put the comment text as an excel macro.

I just opened Excel2003, go to Menu:Extras, Macros, record macro, then did add a comment to some Cell F9 and then stopped macro recording via Menu: Extras, Macro, stop recording. (Menu items may differ in english, I'm translating from german)

This is the code I got in the excel Macros section:

Sub Makro1()
    Workbooks.Add
    Range("F9").AddComment
    Range("F9").Comment.Visible = False
    Range("F9").Comment.Text Text:="username:" & Chr(10) & "test"
End Sub

The result of your macro recording may differ, depending on what you do in excel. The point is: You don't need to code it, you just do whatecer you're used to do in excel, and excel shows you the VBA code needed to repeat your manual actions with macro code.

Now this helps me to now what to do in code and generalize this into code adding a comment to any cell with foxpro:

* first the unavoidable creation of an excel application object:
oExcel = CreateObejct("Excel.Application")
* and a new workbook, as the macro does:
oExcel.Workbooks.Add()
* Now first create a range object as used in the recorded macro:
oRange = oExcel.Workbooks(1).Sheets(1).Range("F9")
* now we can continue as in the recorded macro:
oRange.AddComment()
oRange.Comment.Text("test")
* take a look at the result:
oExcel.visible = .t.

You could also call the recorded macro to do it's stuff instead of translating the code to VFP syntax. Depends on what you want.

Translating VBA to VFP is some experience, there is no one fits all recipe, but you'll see once you've done one or two programs. In VBA macros something like Range("F9") falls from the sky, to do the same in VFP you first need a Range object. Those are the things you need experience to know, also in VBA a call to a method Text is using a parameter syntax like TEXT='...', in VFP you need to pass parameters via method(parameter1,parameter2,...), you can lookup in the VBA help, that Text is the first parameter of the Text method, the full definition is declared in the description of Comment Objects, it's Comment.Text(Text, Start, Overwrite).

Bye, Olaf.

 

[Olaf Doschke]

Please ammend CreateObejct() to CreateObject().

Bye, Olaf.

 

[geminihk]

Thanks Olaf & Mike.

I have done what is necessary....only thing is when i call Save method the macro is not saved. As mentioned i guess there is a problem in macro. I will check it.

 

[JRB-Bldr]

When you create a Macro in Excel you do so without using VFP at all.

You manually Record your actions into an Excel Macro and Stop recording when done. Then, when done, you Edit your Excel Macro to find out the steps necessary to accomplish your task.

Finally with the list of steps and how those steps were accomplished in Excel, you can take that VBA code and use it as a guideline to creating your VFP Automation code. The Excel VBA code cannot be used exactly the same in VFP, but it will work as a guide.

Once you have your VFP Automation code successfully duplicating the Excel Macro functionality, you no longer need the Excel Macro at all and can delete it.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

If I do

oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add()
oExcel.ActiveWorkbook.VBProject.VBComponents([ThisWorkbook]).CodeModule.AddFromFile("D:\BCSQuot\DLL\CommentImage.txt")

I get an OLE Error, it's not save to use the VBProject. Programmatic adding of a code module is not allowed. Your security settings may differ, but I'd say this is normal.

Put the macor you have in CommentImage.txt into the Macros section of a new empty Workbook and save it as a template file and later create workbooks based on that template, for example via:

oExcel.Workbooks.Add("D:\BCSQuot\DLL\YourTemplate.xlt")

Bye, Olaf.

 

[Mike Lewis]

Geminihk,

I come back to my earlier point: You can't add an image to a comment. This discussion about how to save a macro won't change that.

Unless you are using the term "comment" in a different way from the rest of us.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[hgirish]

http://www.xcelfiles.com/VBA_Quick11.html