Jump to content


Photo
- - - - -

DDE with Excel


6 replies to this topic

#1 Joseph Serra

Joseph Serra

    Advanced

  • Members
  • PipPipPip
  • 93 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 19 November 2001 - 11:06 AM

I am have been using the DDE_POKE command to populate some fields in Excel recently using the syntax:

#E = DDE_POKE('EXCEL','[BOOK1]SHEET1',$_R + $_C,$_FIELD)

This seems to work well when I have when I have just opened Excel and Book1 is the active work book. If Excel is already open and Book1 no longer available then this command obviously does not work.

I would like to create a new active work book and name it 'Joseph' for example so my command will look like...

#E = DDE_POKE('EXCEL','[JOSEPH]SHEET1',$_R + $_C,$_FIELD)

instead. I have tried to use DDE_EXECUTE but have had limited success. I can create a new work book which is automatically named 'Bookn', using the command:

#A = DDE_EXECUTE('EXCEL','system','[new]')

but I can't seem to save it to another name. Does anyone know how to do this? I tried using:

#A = DDE_EXECUTE('EXCEL','system','[save as(c:\temp\JOSEPH.xls]')

Can anyone help? Does anyone have the full list of commands and syntax for DDE_EXECUTE for Excel? Where can I find this? I have looked everywhere on the internet to no avail.

Thanks in advance...

Joseph

#2 Andy Jones

Andy Jones

    Member

  • Members
  • PipPip
  • 41 posts
  • Gender:Male

Posted 19 November 2001 - 01:41 PM

I've only used DDE with Word so this is a bit of a guess, but I don't think there should be a space in the 'save as' method, and there's a ) missing after your filename.

One way to find out the commands is to record actions as a macro then view the source in the macro editor. I think that the VBA macro functions & methods are identical in syntax to those DDEExecute can use.

for the parameters 'SaveAs' will accept (and a starting point) see the MSDN page under 'Microsoft Excel Visual Basic Reference'

http://msdn.microsof...xlmthSaveAs.asp
Otherwise, the Macro editor will probably have the info in its help.


Hope that's of help

Andy
Nothing is foolproof to a sufficiently talented fool...

Don't learn from your own mistakes - it's safer and more entertaining to learn from the mistakes of others!

Just because you can, it doesn't mean you should!

#3 Joseph Serra

Joseph Serra

    Advanced

  • Members
  • PipPipPip
  • 93 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 19 November 2001 - 02:33 PM

Thanks for that, but there are three things...

1. I tried this again with the following commands:
#A = DDE_EXECUTE('Excel','system','[new(1)]')
#A = DDE_EXECUTE('Excel','system','[saveas(C:\TEMP\RELEASE.xls)]')

This didn't work at all. Same problem as last time, it seems to be ignored.

2. I did the Macro record of the Save As command in XL and this is what it gave me:

ActiveWorkbook.SaveAs FileName:='C:\TEMP\RELEASE.xls', FileFormat:=xlNormal _
, Password:='', WriteResPassword:='', ReadOnlyRecommended:=False, _
CreateBackup:=False

I didn't think that this format was compatible with the DDE Command.

3. I did try the Macro Help and it didn't get me anywhere. I couldn't find anything regarding DDE commands.

Any other ideas?

#4 Glenn Meyers

Glenn Meyers

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 221 posts
  • Gender:Male
  • Location:St. Louis, MO, United States
  • Interests:I also raise African Gray Parrots and build hot rod automobiles.

Posted 19 November 2001 - 10:28 PM

I had similar problems.
The problem varies depending upon the client you are using.
One of the older clients did all of the DDE functions correctly, including open an Excel spreadsheet, and read form same, but it was at least 8 months to a year ago when we played with it.
I reported the bug to PROIV and they have acknowledged it. You will note that the newest client download notes the trouble!
Be well!

#5 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 396 posts
  • Gender:Male
  • Location:Florida,USA

Posted 19 November 2001 - 10:34 PM

Greetings Joseph.

As part of the version 5.0 features demo we documented all the various execute command we knew of in the [help][about dde] drop down. The one for saving a document we documented to be as follows

[SAVE(C:\mydir\excel\spreadsheet.xls)]

You raise a good point with regard to the valid execute commands for the various Microsoft products. I also had little success in locating a valid list and the help topics on DDE with the MS products are a little spars with info.

This may be an opportune thread to ask all the developers to post all the various dde commands for say WORD,EXCEL and ACCESS on a thread so that we can produce a definitive list or alternatively, let us know where they are documented.
Things should be made as simple as possible, but not simpler

#6 Andy Jones

Andy Jones

    Member

  • Members
  • PipPip
  • 41 posts
  • Gender:Male

Posted 20 November 2001 - 01:51 AM

Yeah sorry Joseph, I didn't mean it would be exactly same syntax, although I did sort of say that didn't I!

If I remember rightly from Word, a macro line such as
ActiveWorkbook.SaveAs FileName:='C:\TEMP\RELEASE.xls' ...
would/should convert to
[SaveAs .FileName='C:\TEMP\RELEASE.xls' ...etc.. ]

But this doesn't seem to work for me either on win2k/v5 ... neither did the SAVE syntax that Darren stated, SAVE only seemed to work without a filename...


Not all is lost though... :o

I think you'll find that the following works:

#A = DDE_EXECUTE('EXCEL','SYSTEM','[SAVE.AS('C:\TEMP\RELEASE.XLS',1,'',False,'',False)]')

I've found that DDE commands use Excel v4.0 macro syntax...
AND there's a 680k help file that you can download from Microsoft! see:
http://support.micro...s/Q128/1/85.asp

Basically the commands listed in there can just be placed inside square brackets to be used in DDE!


A Couple more tips:

If you're doing any lengthy processing, it's probably best to name the worksheet early and reference it directly when selecting/running formulas etc... just put the basic name.xls in-place of 'SYSTEM' in the DDE_EXECUTES.
An [APP.Minimize] wouldn't hurt either, and may even make things run faster.

#A = DDE_REQUEST('EXCEL','SYSTEM','TOPICS',$$STRING)
- returns all open workbooks & sheets, the current one after running your code will be last in the list.

(You may want to define $$STRING as long as possible,say 2000)


#A = DDE_REQUEST('EXCEL','SYSTEM','STATUS',$$STRING)
- Returns Excel's status - when it's waiting it will be 'Ready'. If you're opening EXCEL with SYSTEM instead of version 5's Client.Execute, you can loop on reading this for a short time so you don't send any DDE before Excel's actually loaded!

The 'System' items that can be requested in different MS Apps/versions may vary - the following will return what's supported by your version of Excel:
#A = DDE_REQUEST('EXCEL','SYSTEM','SYSITEMS',$$STRING)


Have Fun.


Andy.
Nothing is foolproof to a sufficiently talented fool...

Don't learn from your own mistakes - it's safer and more entertaining to learn from the mistakes of others!

Just because you can, it doesn't mean you should!

#7 Joseph Serra

Joseph Serra

    Advanced

  • Members
  • PipPipPip
  • 93 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 20 November 2001 - 01:52 PM

Andy you are a star!

I have decided not to do the save at all. Instead I create a new work book then issue a Topic Request as in your example, so I can find the latest Book number, then I poke the values into this work book.

Works a treat! Thanks for all the tips!

Joseph



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users