Create a comment on a cell in Excel via MS Access 2007 VBA

[ino_mart]

All

I have a MS Access application which creates an Excel-file.

From MS-Access I need to put Excel cell comments, but I can't find the right code. I started with an Excel macro and tried to rewrite it into my Access-code.

The recorded macro code in Excel is

    Range("B10").AddComment
    Range("B10").Comment.Text Text:= _
        "Description:" & Chr(10) & "test" & Chr(10) & "" & Chr(10) & "case"

In MS-Access I've tried several things like

dim objExcel, objBook, objSheet, oSheet as object
set objExcel=CreateObject("Excel.application")
set objBook=objExcel.Workbooks.Add(1)
set objSheet=objBook.Sheets(1)
set oSheet=objBook.Worksheets("Sheet1")
 
oSheet.Range("B10").addcomment Text:="Description:" & Chr(10) & "test" & Chr(10) & "" & Chr(10) & "case"

and

dim objExcel, objBook, objSheet, oSheet as object
set objExcel=CreateObject("Excel.application")
set objBook=objExcel.Workbooks.Add(1)
set objSheet=objBook.Sheets(1)
set oSheet=objBook.Worksheets("Sheet1")
 
oSheet.Range("B10").addcomment.comment Text:="Description:" & Chr(10) & "test" & Chr(10) & "" & Chr(10) & "case"

But all of this fails with error: Invalid procedure or argument.

Anyone an idea?

Regads
Ino

 

[ino_mart]

I think I found the problem: it seems you can't add comments to more than one cell.
I get a similar error in Excel when I try

Range("B1:B10").AddComment
    Range("B1:B10").Comment.Text Text:= _
        "Description:" & Chr(10) & "test" & Chr(10) & "" & Chr(10) & "case"

 

[Brianwarnock]

I don't have 2007 and I have never done this but I have to ask 2 questions

1 why did you join the addcomment and the comment text in the access code when it is separate statements in the excel recorded code?

2 why did you only address 1 cell in the recorded macro if you want to do it on a multi cell range?

Brian

 

[ino_mart]

Brianwarnock

1: This is just the way how it works. The VBA-code in Excel just can't be copy/pasted to Access VBA. Excel VBA-code has some commands not known directly by Access VBA, so these have to be modified. Furthermore, many code created by the Excel VBA-recorder is redundant or can be shortened. Last case is true in this example. The two lines created by Excel can be merged into one line.

2: This was the problem. VBA-code seems not to be able to put a comment on more than one cell at the same time

The issue is however solved. In VBA I have to use
osheet.range("B1").addComment.Text Text:="test" & chr(10) & "case"
osheet.range("B2").addComment.Text Text:="test" & chr(10) & "case"
osheet.range("B3").addComment.Text Text:="test" & chr(10) & "case"
and so on

 

[Brianwarnock]

The point I was making is that you recorded a macro that was not attempting what you wanted, and then ignored its syntax. I know all about the inefficiency of recorded macros and never do it, but as you had I just wondered why the above questions.

Brian