Quick question ...

[tamaranth]

Anyone feeling generous? Am fighting Access 97 / Excel 97 and losing.

Background: am writing values out from Access 97 database to Excel spreadsheet, using DDEPoke. (Do not ask why. This is the way things are.)

Problem: I'm checking to see if target spreadsheet exists, and if it doesn't I'm creating it via CreateObject. Whether it existed or not, I would now like to open it using DDEInitiate. But this doesn't seem to work -- either opens a completely new spreadsheet without a filename (???) or (if file already existed) opens nothing at all. Occasionally -- and I can't replicate this right now, but it did happen -- Excel will crash when I try to open the spreadsheet, and Access will hang there going "What spreadsheet? I see no spreadsheet."

Ideal Solution: some code that will check for existence of spreadsheet, create a blank spreadsheet if there isn't one by that name, and open spreadsheet (whether new or not) ready to be DDEPoked. The only proviso is that I do need to carry on using DDE, and I have no control at all over user installations, so can't add DLLs or register objects or anything.

Failing a workable solution, I would like an Axe.

Proposed Reward: BEER.

Comments

[flickgc.livejournal.com]

I know nothing about DDE, but I'd do it in Excel like this:

dim FileIWant
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = FileIWant
If .Execute(SortBy:=msoSortbyFileName, SortOrder:=msoSortOrderAscending) > 0
Then Workbooks.Open FileName:=FileIWant
Else
Set NewBook = Workbooks.Add
With NewBook
.SaveAs Filename:=FileIWant
End With
End If
End With

[tamaranth.livejournal.com]

yep, logic's similar, but Access VBA doesn't let me at the Excel commands. (DDE might, but it's not documented anywhere: trial and error, trial and error ...)

Current method is to display a message asking the user to open the required spreadsheet and then click OK. But apparently this is too difficult.

[flickgc.livejournal.com]

Hmm.

I've never really used Access VBA, I thought they were fairly similar. Can you use Access to call an Excel Sub?

[flickgc.livejournal.com]

Looking in the Access help files, there is a filesearch function
eg:
With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With

So can you not combine that with an if statement to tell it to create a new one if needed and then use DDEInitiate to open it?

Or is that what you're doing already and I missed the point?

Is the file always in the same place? You could have an excel spreadsheet with the excel code set to run on opening, DDEInitiate *that*, becuase it's always there, and then let it run so that the file you need is open and then DDEIPoke it? I think. Um.

[tamaranth.livejournal.com]

The code to check file existence is working fine (though rather different to your example, which is Access 2000/2002, I think: I'm using the Dir command) -- it's the DDEInitiate which doesn't. Two problems with your in-a-spreadsheet solution: (1) it'd be on user's hard drives, which I can't install anything on, and (2) I am hideously happily ignorant of Excel innards ... this may not be the best time to start learning!

In a nutshell: whatever happens, has to happen in my database (on a shared drive). I have a suspicion that it's just an incompatibility between GetObject and DDE ...

[flickgc.livejournal.com]

Ah, the being-on-user's-hardrive problem does seem to be a killer for any hope of my being of help.

Sorry. Good luck!

[andrewducker]

Can I ask why you aren't using OLE rather than DDE?

[d-floorlandmine.livejournal.com]

Good luck m'dear! Been a bit of a while since I wrangled Abcess/Excel, so I don't think I can suggest anything, except wishing luck.

[Thinks: I need to get back into practice]