My VBA macro in excel causes DDE application errors in Windows! Help!

[LSIGuy]

I recently wrote a program at work that performs several queries on sqlserver databases on outside servers. Additionally, it dumps the results of Pulist (a WIN NT RK add-in) into a text file which is then imported into excel via a recorded macro query. This data is then compared to old process data in the spreedsheet and then shifted over (Using column delete/shift). The program appears to run great and seems functional in every way. However, I am recieveing DDE application errors from Windows every once in a while when this macro runs. I don't have any idea as to what may be causing this. I am currently running the macro via a schedular (AT2000) with interactive disabled. Any ideas?...

Thanks,
LSIGuy

 

[Zathras]

See if the information in thread68-537464 applies to your situation.

 

[LSIGuy]

I did use get external data to create the macro that imports the text file. However, the Ignore Other Applications option is unchecked. Also, I am using Excel 2000. Is it likely that the issue with having to exit and restart Excel 97 carried over to this version? If so, does anyone have a clip of code showing how to import text file info mulitspace delimited into an excel spreadsheet? Thanks!

LsiGuy

 

[Zathras]

Have you tried using the Macro recorder?

 

[LSIGuy]

The macro recorder is how I originally made the query. If the query causes future DDE messages to be ignored, then I need a way outside of a query to import it. Is that even possible??? I haven't worked with queries in excel much as I'm normally an access guy!

thanks,
LsiGuy

 

[Zathras]

So... to get the external data, are you creating new queries "on the fly" or are you executing an existing .dqy query?

If the latter, see xlbo's excellent response in thread68-536451 for a technique that you might find useful.

Alternatively, I have previously used the technique of creating the .dqy file with code, then using it to Get External Data.

 

[xlbo]

Instead of "GetExternalData", you could rip off the SQL which it generates (just hit the SQL button in MSQuery) and use that to create your own SQL string
BEar in mind that I am no expert on ADO but something like this could well work:
Sub GetADOData()

Dim rs As ADODB.Recordset
Dim strConn As String, SQLString As String

Set rs = New ADODB.Recordset
SQLString = "The SQL that you rip off from MSQuery"
strConn = "Path of textfile.txt"
rs.Open SQLString, strConn

You would then need some kinda loop to go thru the recordset - something like:
Do while not rs.EOF
'enter data
Loop
or similar



Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde