DDE - How to do it?

[Guest]

Can anyone please show me the correct lines of VBA of how to establish a DDE
connection from Access to Excel. I tried it the way as shown in the help. It
gave me the error saying that the application Excel cannot be found. I have
Excel and Access 2003 installed with XP.

What I need to do in Excel is to open a file, select a sheet, save and close
the file. This has to be done from instructions within the Access VBA.

Further, on a subsequent operation, I need to open the file again with the
selected sheet, change the row height to match the height of a text box
control of the Access report.

Any help will be appreciated.

Thanks,

Sajit

 

[Alex Dybenko]

Hi,
better idea - to use OLE automation, here an example:
http://www.mvps.org/access/modules/mdl0006.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Steve]

Look at the TransferSpreadsheet function in the Help file.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

to get access to excel VBA commands you also have to add a library to VBA
in access open any form or module

EXTRA > first line "verwijzingen" in dutch

look in the long list at "microsoft excel library" or similar words
select the library with the yes/no square
try again

 

[Guest]

Wonder what that means,

EXTRA > first line "verwijzingen" in Dutch

Is it about selecting references, do they appear in Dutch for you?
I thought VB was all in English.

Can some one one else please help to translate.

 

[Guest]

VBA might be englisch, but taskbars etc are in various languages. Anyhow,
reference might be the right word in an englisch version of access, in my
program it is the first line in the EXTRA pull down menu.
look in the list for: microsoft excel x.x object library and include this
library by clicking the selectionbox, where x.x is a version number
The result is that specific EXCEL VBA words like 'worksheet' are regocnized
by the VBA you use. Apart from looking in the access library the VBA
interpreter will now also scan the EXCEL library to check whether a certain
command exist there
When you setup a link with for instance WORD, you have to include the ...
WORD object library, and so on


This code works fine in a program I used for getting used with adressing
cells in worksheets.

Sub t()
Dim exWORK As Object
Dim strtemp As String, strdump As String, i As Integer

Set exWORK = CreateObject("Excel.Application") 'alternative
CreateObject("Word.appication") or even ...("Access.application") and set-up
a link to another database

With exWORK
.Visible = False
.Workbooks.Open ("exceloefen.xls") 'filename is
"exceloefen.xls"

.Sheets("contributie 2006").Select 'worksheet is "contributie
2006"

strtemp = "a" 'column 'A'
For i = 1 To 20
strdump = strtemp & Right$(str$(i), Len(str$(i)) - 1)
Debug.Print Range(strdump).Value
'print the value of A1 ... A20
Next i
.Quit
End With

Set exWORK = Nothing

End Sub

Hope you will have more success now

regards Peter