Using Workspace Macro Pro with Excel & other MS Office programs

For simple macro automation tasks, just record your actions and re-play it back with high accuracy. Alternatively to improve the performance you can create macros using simple commands like Open Files, Insert Keystrokes, Close Window, Launch Website etc.

For advanced uses of Workspace Macro Pro please see few examples below. If you have any questions about how to automate your task please feel free to use our FREE service Ask the Windows Macro & Automation Expert. For more examples see Macro Examples

Examples:

Copy data from Excel to any Windows application (or copy data from any Windows application to Excel)
Check Excel cell C1 and send an e-mail if a particular condition is met
Compare two Excel cells and perform different actions depending on certain condition
Call Workspace Macro from Excel macro and check if macro is successful
Call Workspace Macro from Word macro

1. Copy data from Excel to any Windows application (or copy data from any Windows application to Excel)

Below is an example about how you can copy data from Excel to Word, field by field. We are using Word for ease of explanation but you can copy it from or two any web form, application or database.

When you record, the key is use keyboard to copy-paste data and & not mouse, and then let repeat do the rest. The example below will clarify this even more.

Example: Lets say I have excel file with 5 records and I want to copy it into a table in WORD. Download the zip file (contain excel.xls and word.doc) for the visual.

Before you start recording open the Excel file and click the cell that you want to start copying from. Cell B2 in our example. Open the word file and position the mouse in row1, column1.
Start recording by clicking 'New' in Workspace Macro Pro.
Now click Excel window in taskbar just to get focus on that window (taskbar is the bottom bar where all open windows are shown). Do NOT click in Excel workbook.
Now copy the cell (CTRL+C) in Excel, click mouse on WORD window in taskbar and paste (CTRL+V) it into Word table. Get back to Excel, use arrow key to go to next cell in Excel, Copy, go to WORD, use arrow key to go to the next column and paste into Word table. Continue this way. When the first row is over, use arrow keys to position the cursor in Excel in cell B3 and for WORD, row2, column1.
Stop and save the macro.
Set repeat 4 times and run. It will copy data from Excel into WORD. If you had 10000 rows, you can copy this way by setting 10000 repeat.

Note: In the above example we record it such that it copies from what ever the active cell is into what ever is the active destination cell is. And let repeat do the rest. With the same concept many of our customers transfer 100's of fields and 1000's of records from Excel or some application to other applications and databases.

Customer Testimony

"Great product. I love it. I recorded macro to get customer name from my excel file, search in my database and update customer record. It was very easy to setup and run. I recommend this product."
- Dennis L.

Read more Customer Testimonies

2. Check Excel cell C1 and send an e-mail if a particular condition is met

You can call Workspace Macro Pro recorded macros from any Excel macros.

Sub Macro1()
'
    Dim RetVal
    If (Range("C1") > 5) Then
           RetVal = Shell("C:\Program Files\Workspace Macro Pro 6.5\Workspace Macro Pro.exe
           send-email.wksp /u", 1)
    End If
End Sub

3. Compare two Excel cells and perform different actions depending on certain condition

The following Excel macro compares cell C1 and C2. If C1 > C2, it downloads data, otherwise it uploads the data.

Sub Macro1()
'
    Dim RetVal
    If (Range("C1") > Range("C2")) Then
    RetVal = Shell("C:\Program Files\Workspace Macro Pro 6.5\Workspace Macro Pro.exe download-data.wksp /u", 1)
    Else
    RetVal = Shell("C:\Program Files\Workspace Macro Pro 6.5\Workspace Macro Pro.exe upload-data.wksp /u", 1)
    End If
End Sub

4. Call Workspace Macro from Excel macro and check if macro is successful

If macro is successful it returns 0 to the calling program or if it encounters any error it will return 1. However, if you call macros using syntax below, shell will always return task id, which will be different each time.

RetVal = Shell("C:\Program Files\Workspace Macro Pro 6.5\Workspace Macro Pro.exe send-email.wksp /u", 1)

To check if macro is successful use the example below

Dim RetVal As String

Set wsShell = CreateObject("wscript.shell")
Set proc = wsShell.Exec("C:\Program Files\Workspace Macro Pro 6.5\Workspace
Macro Pro.exe send-email.wksp /u")

//Shell command is asynchronous so it will not wait till macro finishes. Wait for 30 seconds.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

//use proc.ExitCode to check the return value.
RetVal = proc.ExitCode

You can check the value of RetVal and take actions appropriately.

If you don't know how long the macro will take to finish, please refer to the Microsoft site for a more generic wait example http://support.microsoft.com/?kbid=147392