DDE Request - Does not work when a sheet name contains &quot

GeoffA

New Member
Joined
Dec 12, 2005
Messages
3
Undertaking a DDE Request to Excel from Access using VB in Access 2000, the "intChan1 = DDEInitiate("Excel", strSheetName)" command seems to fail if one of the worksheets in the workbook contains a "-" (Even if this is not one that you are initiating). How can you get round this please.

The code that I am using is:

Sub LoadExcel(filename, sheetname1, sheetname2)

Dim intI As Integer
Dim intChan1 As Long

Dim strResp As String
Dim CellToGet As String
Dim strstr As String
Dim strSheetName As String
Dim strTopics As String
Dim comments As String
Dim VariantArray As Variant

On Error GoTo zz ' Set up error handling.

' Create new worksheet.

intChan1 = DDEInitiate("Excel", "System")
tls = "[open(""" & filename & """,FALSE,FALSE)]"
DDEExecute intChan1, tls

' Get topic list, worksheet name.

info = DDERequest(intChan1, "SysItems")
VariantArray = DDERequest(intChan1, "Topics")
strTopics = DDERequest(intChan1, "Selection")
strSheetName = Left(strTopics, InStr(1, strTopics, "]")) & sheetname1
DDETerminate intChan1

' Establish link with specific worksheet - this next command fails with an error 282

intChan1 = DDEInitiate("Excel", strSheetName)

......
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,118,287
Messages
5,571,318
Members
412,382
Latest member
Langtn02
Top