using DDE get a response from Python to VBA in MS Access

2022-05-06 23:39 Dave S imported from Stackoverflow

I have a need to pass strings back forth from a python script to a VBA application running in MS Access.

Communication from VBA to Python works great!

Communication from Python to VBA - not so great great...

The code runs without error, but I only get the 1st character of the response.

EX: Python returns "Joy" VBA only gets "J"

Keep in mind what is so frustrating is I can send strings from VBA to the Python script is no problem.

The Python DDE server:

import win32ui
from pywin.mfc import object
import dde


class MySystemTopic(object.Object):
    def __init__(self):
        object.Object.__init__(self, dde.CreateServerSystemTopic())

    def Exec(self, cmd):
        print("System Topic asked to exec", cmd)


class recv_from_VBA_topic(object.Object):
    def __init__(self, topicName):
        object.Object.__init__(self, dde.CreateTopic(topicName))

    def Exec(self, cmd):
        print("recv_VBA_CMD asked to exec", cmd)
        # print('sending back responce: ' + 'Dave is really great!!!!')


class send_to_VBA_topic(object.Object):
    def __init__(self, topicName):
        topic = dde.CreateTopic(topicName)
        topic.AddItem(dde.CreateStringItem(""))
        object.Object.__init__(self, topic)

    def Request(self, aString):
        daLen = str(len(aString))
        print("send_to_VBA_topic Request Topic asked to compute length of:", aString, daLen)
        return(daLen + ' : ' + aString)


def main():
    server = dde.CreateServer()
    server.AddTopic(MySystemTopic())
    server.AddTopic(recv_from_VBA_topic("recv_VBA_CMD"))
    server.AddTopic(send_to_VBA_topic("send_to_VBA_topic"))
    server.Create('SDER_DDE')
    print('server: SDER_DDE\n Topic: recv_VBA_CMD\n Topic: send_to_VBA_topic\n')

    while 1:
        win32ui.PumpWaitingMessages(0, -1)


if __name__ == "__main__":
    main()

The VBA code:

Option Compare Database
Option Explicit

Public Function dde_send(Optional strIn As String) As String
Dim sRet As String
Dim Blp As Long
'Blp = DDEInitiate("RunAny", "RunAnyCommand")
Blp = DDEInitiate("SDER_DDE", "recv_VBA_CMD")

If Not IsNull(strIn) Then
    Call DDEExecute(Blp, strIn)
Else
    Call DDEExecute(Blp, "Greetings from VBA!")
End If

Call DDETerminate(Blp)
dde_send = sRet
End Function

Public Function dde_recv(Optional strIn As String) As String
Dim sRet As String
Dim Blp As Long

Dim vDDE As Variant, sDDE As String
Blp = DDEInitiate("SDER_DDE", "send_to_VBA_topic")
If Not IsNull(strIn) Then
'    vDDE = DDERequest(Blp, strIn)
    sDDE = DDERequest(Blp, strIn)
    Debug.Print sDDE
Else
    vDDE = DDERequest(Blp, "dde_recv test")
End If

DDETerminate (Blp)
dde_recv = sDDE
End Function

dde_send works dde_recv only gets the 1st character

NOTE: I tested the Python server with this code and it works for receiving the correct string back from the server.

import win32ui
import dde

#apparently "servers" talk to "servers"
server = dde.CreateServer()
#servers get names but I'm not sure what use this name
#has if you're acting like a client
server.Create("TestClient")
#use our server to start a conversation
conversation = dde.CreateConversation(server)

# RunAny is the server name, and RunAnyCommand is the topic
conversation.ConnectTo("SDER_DDE", "send_to_VBA_topic")
# DoSomething is the command to execute
# conversation.Exec("Dave is Great!")

# For my case I also needed the request function
# request somedata and save the response in requested_data.
try:
    requested_data = conversation.Request("somedata")
    print(requested_data)
except:
    print('conversation.Request: exception error')

See also questions close to this topic

Python File Tagging System does not retrieve nested dictionaries in dictionary

I am building a file tagging system using Python. The idea is simple. Given a directory of files (and files within subdirectories), I want to filter them out using a filter input and tag those files with a word or a phrase.

If I got the following contents in my current directory:

data/
   budget.xls
   world_building_budget.txt
a.txt
b.exe
hello_world.dat
world_builder.spec

and I execute the following command in the shell:

py -3 tag_tool.py -filter=world -tag="World-Building Tool"

My output will be:

These files were tagged with "World-Building Tool":
data/ world_building_budget.txt
hello_world.dat
world_builder.spec

My current output isn't exactly like this but basically, I am converting all files and files within subdirectories into a single dictionary like this:

def fs_tree_to_dict(path_):
    file_token = ''
    for root, dirs, files in os.walk(path_):
        tree = {d: fs_tree_to_dict(os.path.join(root, d)) for d in dirs}
        tree.update({f: file_token for f in files})
        return tree

Right now, my dictionary looks like this: key:''.

In the following function, I am turning the empty values '' into empty lists (to hold my tags):

def empty_str_to_list(d):
    for k,v in d.items():
        if v == '':
            d[k] = []
        elif isinstance(v, dict):
            empty_str_to_list(v)

When I run my entire code, this is my output:

hello_world.dat ['World-Building Tool']
world_builder.spec ['World-Building Tool']

But it does not see data/world_building_budget.txt. This is the full dictionary:

{'data': {'world_building_budget.txt': []},  
'a.txt': [],
'hello_world.dat': [], 
'b.exe': [], 
'world_builder.spec': []}

This is my full code:

import os, argparse


def fs_tree_to_dict(path_):
    file_token = ''
    for root, dirs, files in os.walk(path_):
        tree = {d: fs_tree_to_dict(os.path.join(root, d)) for d in dirs}
        tree.update({f: file_token for f in files})
        return tree


def empty_str_to_list(d):
    for k, v in d.items():
        if v == '':
            d[k] = []
        elif isinstance(v, dict):
            empty_str_to_list(v)


parser = argparse.ArgumentParser(description="Just an example", formatter_class=argparse.ArgumentDefaultsHelpFormatter)

parser.add_argument("--filter", action="store", help="keyword to filter files")
parser.add_argument("--tag", action="store", help="a tag phrase to attach to a file")
parser.add_argument("--get_tagged", action="store", help="retrieve files matching an existing tag")

args = parser.parse_args()

filter = args.filter
tag = args.tag
get_tagged = args.get_tagged
current_dir = os.getcwd()

files_dict = fs_tree_to_dict(current_dir)
empty_str_to_list(files_dict)

for k, v in files_dict.items():
    if filter in k:
        if v == []:
            v.append(tag)
            print(k, v)

        elif isinstance(v, dict):
            empty_str_to_list(v)

    if get_tagged in v:
        print(k, v)

Actaully i am working on a project and in it, it is showing no module name pip_internal plz help me for the same. I am using pycharm(conda interpreter

 File "C:\Users\pjain\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\pjain\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Users\pjain\AppData\Local\Programs\Python\Python310\Scripts\pip.exe\__main__.py", line 4, in <module>
  File "C:\Users\pjain\AppData\Local\Programs\Python\Python310\lib\site-packages\pip\_internal\__init__.py", line 4, in <module>
    from pip_internal.utils import _log

I am using pycharm with conda interpreter.

Looping the function if the input is not string

I'm new to python (first of all) I have a homework to do a function about checking if an item exists in a dictionary or not.

inventory = {"apple" : 50, "orange" : 50, "pineapple" : 70, "strawberry" : 30}

def check_item():
    x = input("Enter the fruit's name: ")
    if not x.isalpha():
        print("Error! You need to type the name of the fruit")
    elif x in inventory:
        print("Fruit found:", x)
        print("Inventory available:", inventory[x],"KG")
    else:
        print("Fruit not found")
check_item()

I want the function to loop again only if the input written is not string. I've tried to type return Under print("Error! You need to type the name of the fruit") but didn't work. Help

.Body & .HTMLBody Indent and Default Signature Issue
I've set up a macro to send an e-mail through Outlook.

.Body is read from a cell inside the file with indents. Since the value will change depending on the usage, I need to reference that cell for the body.

However, there rises 2 issues using .HTMLbody I lose indents which are constructed with CHAR(10) but I keep the default HTML signature.

When using just .BODY indents are displayed are correctly however the default signature is not constructed as HTML and I lose the images.

How should I go about fixing this issue?

My code:
```
sig = .HTMLBody
body = xlSht.Range("B4").Value
    .To = xlSht.Range("B2").Value
    .CC = ""
    .Subject = xlSht.Range("B1").Value
    .body = body & sig
    .Display
```
I'd really appreciate your assistance.

Thanks.
VBA: Creating a class property that is an array of dictionaries
In Microsoft Excel VBA I need to create a class that has two properties, "Name" and "Holdings". "Name" is just a string so the code for that is easy. But I need "Holdings" to be a variable length array containing dictionary objects. So this is what I wrote:
```
Private mName As String
Private mHoldings() As Scripting.Dictionary

Public Property Let Name(vName As String)
      mName = vName
End Property
 
Public Property Get Name() As String
      Name = mName
End Property
 
Public Property Set Holdings(vHoldings() As Scripting.Dictionary)
      Set mHoldings = vHoldings
End Property
 
Public Property Get Holdings() As Scripting.Dictionary
      Set Holdings = mHoldings
End Property
```
When I try to compile it gives me this error: "Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalide Set final parameter.

What I doing wrong?

Yahoo Finance no longer returns VBA cookie request for .getResponseHeader("Set-Cookie")

The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

Set httpReq = New WinHttp.WinHttpRequest

DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
With httpReq
    .Open "GET", DownloadURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send
    .waitForResponse
    response = .responseText
    cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
End With

Fetch value and display to comboBox

Hi anyone can help me.

I want to retrieve data from my table and display it to combBox but i wont display. But when i switch to textBox it will display.

Code:

OleDbDataReader reader = cm.ExecuteReader();
                while (reader.Read())
                {

                  dataGridView2.Rows.Add(
                      reader["Employee Name"].ToString());

Datagridview:

private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                connection.Open();
                OleDbCommand cm = new OleDbCommand();
                cm.Connection = connection;
                cm.CommandText = query;
                OleDbDataReader reader = cm.ExecuteReader();
                while (reader.Read())
                {
             comboBox4.Text = dataGridView2.SelectedRows[0].Cells["Column8"].Value.ToString();

VBA QuickBooks: Class not registered

My long-time running MS Access application will no longer create QuickBooks invoices and is failing on the OpenConnection line with the following error:

HRESULT -2147221164 (8004154) Class not registered

My relevant code is:

 Option Compare Database

Public requestMsgSet As QBFC2Lib.IMsgSetRequest ' QB global variable
Public booSessionBegun As Boolean ' QB global variable
Public Response As QBFC2Lib.IResponse ' QB global variable
Public responseMsgSet As QBFC2Lib.IMsgSetResponse ' QB global variable
Public sessionManager As New QBFC2Lib.QBSessionManager ' QB global variable
Public invoiceLineAdd As QBFC2Lib.IInvoiceLineAdd ' QB global variable
Public invoiceAdd As QBFC2Lib.IInvoiceAdd ' QB global variable
Public OrientIDHolder As Integer
Public MultipleInvoice As Boolean ' QB global variable

'False until session is begun
booSessionBegun = False

' Create the session manager object using QBFC 2.0, and use this
' object to open a connection and begin a session with QuickBooks.
sessionManager.OpenConnection "AZENCODE02", "Database - QuickBooks invoice add"
sessionManager.BeginSession "", QBFC2Lib.omDontCare
booSessionBegun = True ' Session has begun

The error is in the QuickBooks.sessionManager.OpenConnection line but I cannot figure out what is happening.

copying rows(live Data) on specific sheets in set of interval time

I've been trying many ways for copying a range of rows that updates automatically (Linked Excel with Interactive Broker app for Stocks ) the point is i need to copy the rows at end of each hours or end of day ( each symbol on it's specific sheet ) and the copy as values so i can come back on the end of week to make studies on the objects My example :

Let's say that my market watch and I need to take a copy of each of the symbol and copying on the specific sheet ( on set of intervals time ( hours , end of day 4:00 P.M for example )

see symbol : TASI

I need from excel to copying as a value the all rows and pasting on the specific sheet for ( TASI ) then on the end of next hour take another copy and pasting on the next embty cells and so on

I read about (on_time Function on VBA ) But I Think I need more help from You gentles for more solutions

Thank you
Rocket Terminal Emulator (Formerly BlueZone) OLE/DDE Paste From Excel?

The software our company uses is called CDK Drive and integrates Bluezone into it. Is there a way to link excel and add a "paste from excel" function to CDK Drive?

Tried the link below but no luck :( https://www3.rocketsoftware.com/bluezone/help/v51/en/bzadmin/CHAPTER_12/OLE_DDE_to_other_Apps.htm

Related questions

using DDE get a response from Python to VBA in MS Access

See also questions close to this topic