Jump to content
Macro Express Forums
Mychance

Transfer numbers from Excel column into web form

Recommended Posts

Hi.  I have just got Macro Express bundled with another software so, I am very new to it.  I was wondering if the following task can be easily done with ME.

I have to transfer grade marks from Excel columns into a web form for report transcripts.  Unfortunately, the latter would not take copy and paste of the column beyond the first entry.  In order to get to the other entry box on the web form, apart from clicking with the mouse, I have to press the tab key 3 times on the keyboard.  I use several columns on several pages on Excel and I am wondering if would be possible to set up some macros that would do the job without taking me more time than doing it manually.

Thanks in advance.

Share this post


Link to post
Share on other sites

I take it you regularly have to manually transfer hundreds (thousands?) of individual numbers from Excel into the web form??

It certainly sounds like something Macro Express can do, by automating the same keystrokes that you would use manually.  The macro(s) will be able to work much faster than you can.  But you have a learning curve to get this working.  I'd recommend you start very simply, just make a macro to copy one cell from Excel, Alt-Tab to the web form, paste the one number,  Alt-Tab back to Excel.  This can be done almost entirely with the Text Type command.  When you are comfortable with that, start expanding your macro -- to arrow down to the next Excel cell for example instead of doing it manually,  Keep expanding until you have something really useful -- though it sounds like the initial simple copy/switch window/paste/switch window may be helpful all by itself.  Set Properties of the macro so you can initiate it with a hot key combination.  Use the script editor to build your macro.  For example:

//  
// Copy from Excel cell where we are currently positioned (Ctrl-c)
Text Type: <CTRLD>c<CTRLU>
// Alt-Tab to web form
Text Type: <ALTD><TAB><ALTU>
// Delay briefly while Windows switches to the other window
Delay 500 Milliseconds
// Paste into web cell where currently positioned (Ctlr-v)
Text Type: <CTRLD>v<CTRLU>
// Tab a few times to get to the next web cell
Text Type: <TAB><TAB><TAB>
// Alt-Tab back to Excel
Text Type: <ALTD><TAB><ALTU>
// Delay briefly while Windows switches to the other window
Delay 500 Milliseconds
// Arrow down to next Excel cell that we will be copying
Text Type: <ARROW DOWN>
// 

Share this post


Link to post
Share on other sites

Hi,

Thank you VERY MUCH for your answer.  I see that it will be worthed that I investigate more on automating script with Macro Express.  Just out of the blue and to show you that I know next to nothing yet, is it conceivable to copy my column of numbers in Excel and then have Macro Express intercalate <TAB><TAB><TAB> between each line and paste ?

Share this post


Link to post
Share on other sites

Yes, but you will still somehow have to copy and paste each cell separately.  If the whole column of numbers is in the clipboard, then each time you paste you will be pasting the same thing, NOT subsequent cells on subsequent pastes.  There are other techniques for what you want to do, such as saving the Excel spreadsheet to a CSV file and processing that with a macro; or storing multiple Excel cell values in multiple macro variables so they can be separately pasted.  But get something really simple working to start. 

Share this post


Link to post
Share on other sites

UPDATE

I have have finally succeded in setting up a script that does exactly what I want,  Thank you very much for your help ! The last thing that is left to do is to set up a number of time that I want the script to be REPEATED.  I have looked at the repeat command and I find it a bit confusing.  So what would it need to be added to the following script if I want it to be repeated let's say 20 times ?

Control Key Down
  Text Type: c
Control Key Up
Text Type: <ARROW DOWN>
Delay 200 Milliseconds
Alt Key Down
  Text Type: <TAB>
Alt Key Up
Delay 500 Milliseconds
Control Key Down
  Text Type: v
Control Key Up
Text Type: <DOWN ARROW>
Alt Key Down
  Text Type: <TAB>
Alt Key Up

Share this post


Link to post
Share on other sites

Repeat Start (Repeat 20 times)

Control Key Down
  Text Type: c
Control Key Up
...
...
...

Alt Key Down
  Text Type: <TAB>
Alt Key Up

Repeat End

You might also try repeating indefinitely -- or repeat an extremely high number of times.  Then after the copy-to-clipboard (Ctrl-c), if the clipboard contains nothing, exit from the repeat loop.  That way your macro would deal automatically with a variable number of entries in a column, as long as the last cell in the spreadsheet column is followed by a null cell. 

Variable Set Integer %N1% to 1
Repeat Until %N1% <> 1

Control Key Down
  Text Type: c
Control Key Up

  If Clipboard Text Equals ""
     Repeat Exit
  End If

...
...
...

Alt Key Down
  Text Type: <TAB>
Alt Key Up

Repeat End

Share this post


Link to post
Share on other sites

Congratulations on getting so far!

 

There are many ways to make the script more reliable and robust. For the next iteration, consider substituting "Window Activate" commands for Alt + Tab commands. Alt + Tabs work reliably when only two windows are open. But if you more than two open windows, you may end up in the wrong one. "Window Activate" is more likely to bring up the windows you actually want to act on

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×