A Little Help Please....

[JerMu]

Ok guys forgive me for my lack of knowledge but I am brand new to the world of macroing...I learned about it through a game and now I am trying to learn a bit more so I can write a macro.

The macro I am writting is simply trying to access an excel worksheet online, download it, copy data from it to a spreadsheet that I have, so I can keep the data up to date as it changes, and then terminate.

The macro so far looks like this:

URLDownloadToFile("http://www.linkIwanttogoto.com", "C:\Documents and Settings\User\Desktop\FileIWantItToBe.xls")

If FileExists("C:\Documents and Settings\User\Desktop\FileIWantItToBe.xls") Then

Run('"C:\Microsoft Office\OFFICE\EXCEL.EXE" "C:\Documents and Settings\User\Desktop\StockTemp\FileIWantItToBe.xls" "C:\Documents and Settings\User\Desktop\FileIWantToUpdate.xls"')

Else

MsgBox(4096,"", "File does not exist yet")

EndIf

WinSetOnTop ("FileIWantItToBe", "", 1)

WinActivate ("FileIWantItToBe", "")

Problem Im having is I have excel open with the new file Im wanting to copy info from along with the file I'll be pasting info to, and I can't get the window I want to copy from to come to the top and activate so I can use some mouse moves to drag across the area I want to copy and paste.

Any Ideas??? Everything works so far except the part where I want to activate the window I want to copy from, then of course I'll want to switch to the other spreadsheet window so I can paste. The WinActivate command seems to not be working in the way Im wanting it to or Im doing something wrong. Im sure its the latter...lol.

Also Im curious about how I should make a mouse scroll movement...in the spreadsheet Im pasting to I need to scroll down the spreadsheet a ways before I can paste in the proper spot...I was thinking a mousedrag with the scroll bar, but is there a better way? The less I use the mouse the better I'll feel cause I feel that somewhere down the road the coordinates I put in can very easily be made wrong by me messing up and moving a window.

Anyway I hope my problems make sense so you guys can help...thanks for your time and help.

A new community member,

JerMu

[pekster]

I would suggest using Control functions (such as using ControlSend to the sheet) to move between cells, select data, and copy data. You can move between window elements with Control-Tab and Shift-Control-Tab, move between cells with the arrows, move by page with page-up and page-down, and select a cell range by holding shift as you move with the arrows.

Control* functions have a few advantages. First off, they involve no mouse movement, so you don't need to worry about blocking mouse input, or the correct coordinates. Second, they work even if the window is not active, is minimized, or hidden. Lastly, you can be sure your keys are being sent to the correct location without additional testing.

[JerMu]

nice...didn't think of that route...I'll give it a try. Thx

[JerMu]

Ok I started using just Send( "" ) as my functions using ctl+tab in there to move from window to window, and then the CTL+C/V as copy/paste...however the more I get into this the more I realize I really need to know how to call a specific window. While this method works well for just bouncing around between two windows...this is not a very viable option for more than two and I plan to have a few spreadsheets going at once taking info from one to another and then from another to yet another...so in order to get my macro working properly I need to be able to call a specific window(because once I have 3,4,5,etc.. windows going how do I make sure I jump to the right window just using CTL+TAB)...copy info from that window...then call another window to paste that info to. I started using the Send( "" ) and then I reread the post replied to my original post and saw that you said ControlSend, or Control(whatever)...so I looked at these commands and couldn't make sense of how it is to be used...I simply want to activate windows....copy/paste...then move along to other windows for the same thing...however this command wants title, text, classname, string, and flag...and I don't understand how it works with what I want to do.

Can you clarify as to how I would use this command to call a window by name to become active so I can then start to manipulate info on that window.

ControlSend("Untitled", "", "Edit1", "This is a line of text in the notepad window")

How would: File1.xls, File2.xls, File3.xls fit into this so I could call file1 copy text to file3, then call file2 and copy text again to file3?

Also is there a command I can use to make sure the active cell in excel is always A1 so I can just use the same up/down over and over...it seems the active cell is where you last saved it..Im just wondering is there a way I can have my macro changed this before I start to excecute moves so I know where the active cell is always for consistancy?

thanks again for all your help,

JerMu

[CyberSlug]

You might consider using Excel to export (save as) the file in tab-delimited text format. You could parse the text file with FileRead line in order to change cells or add rows or columns. Then you would open that text file with Excel: File > Open > enter filename > automate the "Text Import Wizard" that appears ....

However, if you are using Excel for advanced formatting and formula calculations, this alternative won't be sufficient.

Edited August 4, 2004 by CyberSlug

[JerMu]

You might consider using Excel to export (save as) the file  in tab-delimited text format.  You could parse the text file with FileRead line in order to change cells or add rows or columns.  Then you would open that text file with Excel:  File > Open > enter filename > automate the "Text Import Wizard" that appears ....

However, if you are using Excel for advanced formatting and formula calculations, this alternative won't be sufficient.

<{POST_SNAPBACK}>

Well the format of the file is not a problem at this point my only problem is jumping between windows so I can excecute the copy/paste command. At this point I am just jumping between two files adn the send(^{tab}) works fine for jumping between the two, however when I figure out all my commands and really start to get into my macro I am going to be using like maybe 10 spreadsheets at once and jumping between them all. I really need to know a way to call a specific excel document to the active foreground so I can copy/paste then call another document to the foreground to copy/paste. I can get the copy/paste working fine, Im just trying to fix a problem I fore see in the future when Im dealing with multiple documents and I can't control the tab order of the windows meaning I can't control which data Im getting.

I've tried the WinActivate command but I cant seem to get it to work...Can someone shed some light on this, or enlighten me on a better method of jumping between different documents while having full control of which document Im jumping to?

Thanks guys

[drak]

In Excel have a look at doing Alt+w, under here your spreadsheets will be in number order starting with the first spreadsheet opened

to get make your active cell A1 then send Ctrl+Home to the active spreadsheet

moving around your spreadsheet can be done by sending End+arrowkey, this will take you in the required direction until it hits a blank cell, or if starting in a blank cell it will stop at the next filled cell

hope this helps

[JerMu]

Ok I think I've managed to get around my "hoping windows" problem...I decided to just have the macro open and close everything over and over so that way I can copy something close everything out, then reopen with the document I want and then paste....It's a bit of a head ache, but this way I can control which screens Im doing something to all the time...

However this brings rise to a new problem...or one I haven't reached until now.

When I copy cells from one document to another the clipboard seems to erase itself durring the switching of documents...that or the command isn't working. I was testing this all on one spreadsheet to see if the commands where working and they did...I would just copy move over a few cells and paste...before I started jumping excel documents everything seemed to work...Now that I have a method of jumping documents the clip board doesn't want to seem to jump with me...

I'll attach my lines and hopefully you guys can see something wrong.

URLDownloadToFile("http://www.FileDownLoad.com", "C:\Documents and Settings\User\Desktop\File1.xls")

If FileExists("C:\Documents and Settings\User\Desktop\File1.xls") Then

Run('"C:\Microsoft Office\OFFICE\EXCEL.EXE" "C:\Documents and Settings\User\Desktop\File1.xls"')

Else

MsgBox(4096,"", "File does not exist yet")

EndIf

Send("^{Home}")

Send("{DOWN}")

Send("{RIGHT}")

Send("{SHIFTDOWN}")

Send("{DOWN 11}")

Send("{RIGHT 8}")

Send("{SHIFTUP}")

Send("^c")

$s = ClipGet()

If @error Then

MsgBox(16, @ScriptName & " - Error", "Error reading Clipboard")

Exit

EndIf

Send("^w")

Send("y") <-------(this very well could be the prob, its the "yes" button for a prompt that asks if you want to save the clipboard for pasting to other documents)ProcessClose("EXCEL.EXE")

Run('"C:\Microsoft Office\OFFICE\EXCEL.EXE" "C:\Documents and Settings\User\Desktop\File2.xls"')

Send("^{Home}")

Send("^{PGDN 10}")

Send("^{Home}")

Send("{DOWN}")

Send("{RIGHT}")

Send("^v")

Hope you guys see something...

thanks for all the help, it's greatly appreciated.

JerMu

[pekster]

Chances are really good it's sending the "y" keystroke before the window actually appears. You should probably put a WinWaitActive in there so that it will wait until the dialog box comes up before sending.

Without any kind of a delay or pause until the window opens, it will send the y keystroke right after the control-w, which is probably way too fast.

[MHz]

The "y" looks lonely.

Send("y")

Try

Send("!y")

for sending an ALT + Y key.

Hope it helps.

[JerMu]

yay! it works...I put in a sleep(250) and it's gold baby!!!!

thx guys..