DDE_SERVICE_INVALID error pulling data from Excel

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
montanan
Junior Coder
Posts: 49
Joined: Mon Jul 09, 2007 3:44 pm
Location: San Jose, CA

DDE_SERVICE_INVALID error pulling data from Excel

Post by montanan » Sun Jan 11, 2009 12:26 am

I'd appreciate another set of eyes on this. I use DDE with Excel quite a bit, and it is incredibly useful.

I must be doing something wrong with my code, but I am having trouble figuring it out.

What I'm trying to do is to create a .bat file to re-organize some files from one folder into many folders, according to an index file that is in Excel.

Here's the data pasted directly from Excel...

Financials
2007 Documents
2007 Acme, Inc. Tax Returns.PDF
2007 Tax Workpapers.pdf
2007 Acme, Inc. Tax Returns.PDF
2007 Quarterly payroll data - Corporate.pdf
2007 Tax Workpapers.pdf
2007 Acme, Inc. Tax Returns.PDF
2007 Quarterly payroll data - Corporate.pdf
2007 Quarterly payroll data - Corporate.pdf
2007 Tax Workpapers.pdf
2008 Quarterly payroll data - Corporate.pdf
@Value Service Agreement.pdf
Aged AP 12.31.07 Acme Corp.xls
Aged AR 12.31.07 Acme Corp.xls
Amended and Restated Acme Capital Markets License final.DOC
Corp Additional payroll procedures .xls
Corporate Other Rec & Assets.pdf
Corporate Revenue.pdf
Corporate Tax Due Diligence Checklist.xls
Corporate wages by state.xls
Corporate_20_Ownership Changes.doc
Employee Documents
101 2008 Headquarters EBITDA Format.xls
101 Headquarters - payroll reports.pdf
101 Headquarters - Tax Due Diligence Checklist.xls
101 Headquarters Change in Ownership.doc
2001 Bank Reconciliations & Bank Stmts.pdf
2002 Money Market Acct.pdf
2007 Headquarters EBITDA and TB.xls
2601 Prepaid insurance.xls
3201 gaap depreciation report.xls
3801 Operating Agreement.pdf
3802 Other Assets.pdf
Untagged
Local CPAs Contact List.xls
Request #17 Car Loan.pdf


Here's the output I'm getting right now, with the errors...

mkdir folders
cd folders
mkdir "Financials"
mkdir "Financials"\"2007 Documents"
copy files\"2007 Acme, Inc. Tax Returns.PDF" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"


Here's my code...

Code: Select all

//Specify the path of the Excel file here
Let>filename=C:\Folderizer\Input file.xls

IfFileExists>filename

//Start Excel
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

//Ask how many rows we should get
Input>maxrows,How many rows shall I process?,10

//As an example I'm going to paste the data into Notepad
Run>Notepad.exe
WaitWindowOpen>Untitled - Notepad
Send>mkdir folders%CR%
Send>cd folders%CR%
Let>r=1
Repeat>r
//get the fields for this row
 DDERequest>Excel,filename,R%r%C1,field_1,60
If>field_1=DDE_SERVICE_INVALID
 Let>field_1=%topleveltag%
 DDERequest>Excel,filename,R%r%C3,field_3,60
 Goto>fileonlyrow
EndIf
 DDERequest>Excel,filename,R%r%C2,field_2,60
 DDERequest>Excel,filename,R%r%C3,field_3,60
//remove the CRLF that Excel adds
 StringReplace>field_1,CRLF,,field_1
 StringReplace>field_2,CRLF,,field_2
 StringReplace>field_3,CRLF,,field_3
//Get the length of each field, so I know how to treat it
Length>%field_1%,len1
Length>%field_2%,len2
Length>%field_3%,len3

//Trim each field
VBSTART
VBEND
VBEval>LTrim("%field_1%"),field_1
VBEval>LTrim("%field_2%"),field_2
VBEval>LTrim("%field_3%"),field_3

//For this example we'll just paste each row into Notepad
SetFocus>Notepad*
If>len1>0
 Let>topleveltag=%field_1%
 Send>mkdir "%field_1%"
 Goto>nextrecord
EndIf
If>len2>0
 Let>secondleveltag=%field_2%
 Send>mkdir "%topleveltag%"\"%secondleveltag%"
 Goto>nextrecord
EndIf
If>len3>0
 Label>fileonlyrow
 Let>filename=%field_3%
 Send>copy files\"%filename%" folders\"%topleveltag%"\"%secondleveltag%"
 Goto>nextrecord
EndIf
Label>nextrecord
'Press Enter
Send>%CR%
Let>r=r+1
Wait>0.05
Until>r=maxrows

Else
MessageModal>Could not find: %filename%
Endif
Thanks!
Top
Me_again
Automation Wizard
Posts: 1101
Joined: Fri Jan 07, 2005 5:55 pm
Location: Somewhere else on the planet

Post by Me_again » Sun Jan 11, 2009 1:16 am

It worked out of the box for me, I got this in notepad:

mkdir folders
cd folders
mkdir "Financials"
mkdir "2007 Documents"
mkdir "2007 Acme, Inc. Tax Returns.PDF"
mkdir "2007 Tax Workpapers.pdf"
mkdir "2007 Acme, Inc. Tax Returns.PDF"
mkdir "2007 Quarterly payroll data - Corporate.pdf"
mkdir "2007 Tax Workpapers.pdf"
mkdir "2007 Acme, Inc. Tax Returns.PDF"
mkdir "2007 Quarterly payroll data - Corporate.pdf"
mkdir "2007 Quarterly payroll data - Corporate.pdf"
mkdir "2007 Tax Workpapers.pdf"
mkdir "2008 Quarterly payroll data - Corporate.pdf"
mkdir "@Value Service Agreement.pdf"
mkdir "Aged AP 12.31.07 Acme Corp.xls"
mkdir "Aged AR 12.31.07 Acme Corp.xls"
mkdir "Amended and Restated Acme Capital Markets License final.DOC"
mkdir "Corp Additional payroll procedures .xls"
mkdir "Corporate Other Rec & Assets.pdf"
mkdir "Corporate Revenue.pdf"
mkdir "Corporate Tax Due Diligence Checklist.xls"
mkdir "Corporate wages by state.xls"
mkdir "Corporate_20_Ownership Changes.doc"
mkdir "Employee Documents"
mkdir "101 2008 Headquarters EBITDA Format.xls"
mkdir "101 Headquarters - payroll reports.pdf"
mkdir "101 Headquarters - Tax Due Diligence Checklist.xls"
mkdir "101 Headquarters Change in Ownership.doc"
mkdir "2001 Bank Reconciliations & Bank Stmts.pdf"
mkdir "2002 Money Market Acct.pdf"
mkdir "2007 Headquarters EBITDA and TB.xls"
mkdir "2601 Prepaid insurance.xls"
mkdir "3201 gaap depreciation report.xls"
mkdir "3801 Operating Agreement.pdf"
mkdir "3802 Other Assets.pdf"
mkdir "Untagged"
mkdir "Local CPAs Contact List.xls"
mkdir "Request #17 Car Loan.pdf"

I created the .xls by copy paste from your post, I have no blank rows before the data. My excel on this PC is 2002, you are probably using a later version.
Top
Aaron
Pro Scripter
Posts: 113
Joined: Mon Apr 09, 2007 1:35 am
Location: Wyoming

Just a thought

Post by Aaron » Sun Jan 11, 2009 8:37 pm

Just a thought, but every once in a while I forget to look at what version of excel the worksheet was made in.

The other day I had the same problem

The code said .xls when infact it was a 2007 version, .xlsx

Probably not your issue, but thought it might help
Aaron
Top
montanan
Junior Coder
Posts: 49
Joined: Mon Jul 09, 2007 3:44 pm
Location: San Jose, CA

Post by montanan » Sun Jan 11, 2009 9:33 pm

Thanks, Aaron.

I'd like to email you the excel file and see if you can try that. Would that be ok?

I can see that the cut & paste from the text above didn't format in your excel the way I think it should have.

My email is montanan at gmail dot com

-Richard
Top
Me_again
Automation Wizard
Posts: 1101
Joined: Fri Jan 07, 2005 5:55 pm
Location: Somewhere else on the planet

Post by Me_again » Mon Jan 12, 2009 3:13 am

My result is the same as yours using your spreadsheet. I'll play with it tomorrow (if Marcus doesn't beat me to it). My guess would be that it doesn't like whatever is in the "empty" cells.

mkdir folders
cd folders
mkdir "Financials"
mkdir "Financials"\"2007 Documents"
copy files\"2007 Acme, Inc. Tax Returns.PDF" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
copy files\"DDE_SERVICE_INVALID" folders\"Financials"\"2007 Documents"
Top
montanan
Junior Coder
Posts: 49
Joined: Mon Jul 09, 2007 3:44 pm
Location: San Jose, CA

Post by montanan » Mon Jan 12, 2009 3:58 am

Thanks for trying it and confirming that you're seeing the same result.

Maybe it is something in the cells. I thought that could be the case and even cut the text from Excel, pasted into Notepad, and then re-imported into Excel to get a spreadsheet with "clean" cells. That doesn't mean that there still isn't some hidden characters lurking.
Top
User avatar
Marcus Tettmar
Site Admin
Posts: 7391
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:
Contact Marcus Tettmar

Post by Marcus Tettmar » Mon Jan 12, 2009 9:14 am

You are changing the value of filename!

line 1 sets filename to "C:\Folderizer\Input file.xls"

This is used in your DDERequest call.

Later, you set filename to the value of field_3:

Let>filename=%field_3%
Send>copy files\"%filename%" folders\"%topleveltag%"\"%secondleveltag%"

Then you jump back to the top of the Repeat loop and try and do a DDERequest again, but this time filename is no longer "C:\Folderizer\Input file.xls". You've changed it. So the DDERequest is invalid.

Use a different variable name for your copy files section.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
Top
montanan
Junior Coder
Posts: 49
Joined: Mon Jul 09, 2007 3:44 pm
Location: San Jose, CA

Post by montanan » Mon Jan 12, 2009 2:34 pm

Thank you Marcus!

A big ooops on that one. That's what I get for re-using some old code for the handling of the source file name. I changed the variable to "inputfile" and everything worked fine.

Here's the working code, in case others need a "folderizer" script at some point.

Code: Select all

//Specify the path of the Excel file here
Let>inputfile=C:\Input file.xls

IfFileExists>inputfile

//Start Excel
ExecuteFile>inputfile
WaitWindowOpen>Microsoft Excel -*

//Ask how many rows we should get
Input>maxrows,How many rows shall I process?,10

//As an example I'm going to paste the data into Notepad
Run>Notepad.exe
WaitWindowOpen>Untitled - Notepad
Send>mkdir folders%CR%
Send>cd folders%CR%
Let>r=1
Repeat>r
//get the fields for this row
 DDERequest>Excel,inputfile,R%r%C1,field_1,60
If>field_1=DDE_SERVICE_INVALID
 Let>field_1=%topleveltag%
 DDERequest>Excel,inputfile,R%r%C3,field_3,60
 Goto>fileonlyrow
EndIf
 DDERequest>Excel,inputfile,R%r%C2,field_2,60
 DDERequest>Excel,inputfile,R%r%C3,field_3,60
//remove the CRLF that Excel adds
 StringReplace>field_1,CRLF,,field_1
 StringReplace>field_2,CRLF,,field_2
 StringReplace>field_3,CRLF,,field_3
//Get the length of each field, so I know how to treat it
Length>%field_1%,len1
Length>%field_2%,len2
Length>%field_3%,len3

//Trim each field
VBSTART
VBEND
VBEval>LTrim("%field_1%"),field_1
VBEval>LTrim("%field_2%"),field_2
VBEval>LTrim("%field_3%"),field_3

//For this example we'll just paste each row into Notepad
SetFocus>Notepad*
If>len1>0
 Let>topleveltag=%field_1%
 Send>mkdir "%field_1%"
 Goto>nextrecord
EndIf
If>len2>0
 Let>secondleveltag=%field_2%
 Send>mkdir "%topleveltag%"\"%secondleveltag%"
 Goto>nextrecord
EndIf
If>len3>0
 Label>fileonlyrow
 Let>filename=%field_3%
 Send>copy files\"%filename%" folders\"%topleveltag%"\"%secondleveltag%"
 Goto>nextrecord
EndIf
Label>nextrecord
'Press Enter
Send>%CR%
Let>r=r+1
Wait>0.05
Until>r=maxrows

Else
MessageModal>Could not find: %inputfile%
Endif
Top
Post Reply

Return to “Technical / Scripting”

cron
Sign up to our newsletter for free automation tips, tricks & discounts