Cut and paste to Excel

[ainterne]

I am trying to cut and paste two lists into Excel, one list to Col A and then another list to Col F. Then run a macro that compares the both.

The problem I am having is that when I try and put the content of a file in to Col A it just puts the top line of the list in Cell A1....etc
If I cut and paste manually then it works fine.

Can anyone share with me the best way of getting my lists into Excel please?

ReadFile>S:\test1.txt,File1
MessageModal>File1
ReadFile>S:\test2.txt,File2
MessageModal>File2

ifWindowOpen>MyWorkBook*
else>
exe>s:\mytest.xls
endif>
wait>3
DDEPoke>Excel,s:\mytest.xls:Sheet1,R1C1,%File1%
wait>2
DDEPoke>Excel,s:\mytest.xls:Sheet1,R1C6,%File2%

[Marcus Tettmar]

Your code puts the entire contents of the file into one cell. R1C1 is cell A1.

What (I assume) you want to do is loop through the file line by line and put each line in a different row. Something like:

Code: Select all

ReadFile>S:\test1.txt,File1
Separate>File1,CRLF,Lines
Let>k=1
Repeat>k
  Let>CurrentLine=Lines_%k%
  DDEPoke>Excel,s:\mytest.xls:Sheet1,R%k%C1,CurrentLine
  Let>k=k+1
Until>k>Lines_Count

Hope that helps.

[ainterne]

Thank for the response but I seem to have hit that brick wall again.
I can get the lists into Excel in the right places but then I have to run the macro and I note from your mail you published a link in your blog that indicates a macro should be run from :

"Use the Run method of the Excel Application object to run Excel macros/VBA routines"

Does anyone know how to run a macro in Excel from Scheduler without running VB?

This is what I have so far:

ifWindowOpen>MyWorkBook*
else>
exe>s:\mytest.xls
wait>3
ReadFile>S:\test1.txt,File1
Separate>File1,CRLF,Lines
Let>k=1
Repeat>k
Let>CurrentLine=Lines_%k%
DDEPoke>Excel,s:\mytest.xls:Sheet1,R%k%C1,CurrentLine
Let>k=k+1
Until>k>Lines_Count

ReadFile>S:\test2.txt,File2
Separate>File2,CRLF,Lines
Let>y=1
Repeat>y
Let>CurrentLine=Lines_%y%
DDEPoke>Excel,s:\mytest.xls:Sheet1,R%y%C6,CurrentLine
Let>y=y+1
Until>y>Lines_Count

[Marcus Tettmar]

This post has a really simple function you can use to run an Excel VBA macro:
http://www.mjtnet.com/forum/viewtopic.p ... ight=excel

But if you don't want to do that another way is to send the keystrokes to run a macro. ALT+T followed by M and then M will open the Macro dialog. Then you just have to enter the macro name and hit Enter. So the following code will run a macro:

Code: Select all

SetFocus>Microsoft Excel*
Press ALT
Send>t
Release ALT
Wait>0.2
Send>m
Wait>0.2
Send>m
WaitWindowOpen>Macro
Wait>0.2
Send>MACRO_NAME
Press Enter

Replace MACRO_NAME with the name of your macro. The code works on an already open Excel window. Modify the SetFocus line if necessary so that it finds the right Excel window.

[ainterne]

Thanks, I did see the VB link you sent and looked at it.

The reason I am having trouble with this is that I have to open the Excel to get the list data into it. Then when I run the VB script it fails because the sheet is already open. I can't run the VB first because the list data is not in it at that time so the macro runs with no data to work with.

The keystroke method however works nicely because it runs the macro with out having to open the sheet. So thank you for that.