Morning all. Let me start off by saying no. No, I'm not a moron. However, I know next to nothing about the basic functionality of the various office packages, despite having used them since I was age ten. My problem is this: I've been using a (very) basic macro I created at work to speed up certain repetitive tasks (how no-one thought of this before, I don't know). Management somehow caught wind of this, and being a tool of some considerable efficiency, have asked me to present to my colleauges (group-wide) a demonstration of how these macros could be implemented in the interim, while a proper software solution is developed. Now, as said - my macro is very basic. I created it using a third party program, and to do one task only. I'm expected to explain and demonstrate how it could do other things also. The macro recording software I've used is very basic, and lacks the functionality I need to demonstrate, professionally and effectively, how to do what I need. So, what I think I'm looking for is: A macro tool? That can: Access/read a cell in an Excel spreadsheet Take the first word ONLY of the above cell and paste elsewhere (another program) Be able to replicate a keypress i.e. tab Move mouse to x,y click/ right click etc Read another cell, paste whole contents elsewhere (another program) Read whole cell and type it out manually elsewhere (another program) go through a number of rows, doing the above tasks until completion If/Else statements would be amazing, but not neccessary If anybody knows of anything like this, I would be SO grateful and it has been said my generosity knows no bounds... Thanks in advance, TSB
There are macro expression builders built into excel in think, I've never used them myself but the are regularly used by other people in our company. Don't know if that's any help but it may be?
Part of the problem is, when I say 'paste elsewhere' it will be into a field in another program - can excel handle that? Tabbing out of itself to paste in a different window? TSB
When you say elsewhere do you mean: - Within the Excel application? - Another application? Could write some VBA if we had more info. Just read previous post. I use VBA for setting up new users in our finance system using info from Excel. It is possible. I use a function called SendKeys but you can also send messages to the other application using window and control handles. All depends on the application you are sending too.
As an example here is some code that I use. Code: '**Win32 API Declarations Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long Private Declare Function mouse_event Lib "user32.dll" (ByVal dwflags As Integer, ByVal dx As Integer, ByVal dy As Integer, ByVal dwData As Integer, ByVal dwExtraInfo As Integer) As Integer Const MOUSEEVENTF_LEFTDOWN As Integer = 2 Const MOUSEEVENTF_LEFTUP As Integer = 4 Const MOUSEEVENTF_RIGHTDOWN As Integer = 8 Const MOUSEEVENTF_RIGHTUP As Integer = 16 Sub Close_Role() Dim myRole As String Dim i As Long Dim CheckRng As Long Dim myDesc As String CheckRng = Columns(1).Find("*", SearchDirection:=xlPrevious).Row For i = 1 To CheckRng myRole = Cells(i, 1).Value 'set roleID AppActivate ("AGRESSO Business World") 'Activate Agresso Application.Wait (Now + TimeValue("00:00:01")) Application.SendKeys "{F5}", True 'Clear screen Application.SendKeys myRole, True 'Paste RoleID Application.SendKeys "{TAB}", True 'Tab Application.Wait (Now + TimeValue("00:00:01")) SetCursorPos 650, 403 'Set Mouse Position for Status Box Click Application.Wait (Now + TimeValue("00:00:01")) SetCursorPos 650, 433 'Set Mouse Position for Closed Status Click Application.Wait (Now + TimeValue("00:00:01")) Application.SendKeys "{TAB}", True 'Tab SetCursorPos 451, 530 'Set Mouse Position for Client Click Application.Wait (Now + TimeValue("00:00:01")) Application.SendKeys "+{TAB}", True 'Tab Application.SendKeys " ", True 'Remove Client Application.Wait (Now + TimeValue("00:00:01")) Application.SendKeys "{F12}", True 'Save Next i AppActivate ("Microsoft Excel") MsgBox "All Done" End Sub Oops forgot the Click function that goes with it. Code: Function Click() x = mouse_event(MOUSEEVENTF_LEFTDOWN, x, y, 0, 0) x = mouse_event(MOUSEEVENTF_LEFTUP, x, y, 0, 0) End Function
God, I love this community. I can tell you exactly step by step what I need - it's fairly rudimentary - and am not fussy about the way the VBA handles the windows/applications - I'm happy to have them open side by side for a simple click at the correct point and paste, or the only two applications open while running the macro for easy alt+tab, and cursor location pasting/typing or whatever is recommended by you geniuses. Select B4, Copy contents, paste contents to x,yloc1 (in the other prog) , tab keypress Select N4, Copy contents, paste contents to x,yloc2 (in the other prog) Select P4, Read contents, type out contents to x,yloc3 (in the other prog) Select L4, Copy contents, paste to contents to x,yloc4 (in the other prog) Select Q4, Read contents, type out contents to x,yloc5 (in the other prog) Random clicks I can sort out here Select R4, Read contents, type out contents to x,yloc6 (in the other prog) Random clicks Select S4, Read contents, type out contents to x,yloc7 (in the other prog) Select T4, Read contents, type out contents to x,yloc8 (in the other prog) Select U4, Copy contents, paste contents to x,yloc9 (in the other prog) Clicks And then to go on to row 5 and repeat etc. If we call the 'other program' InSoft, I think it has a VB.net? C? Frontend obviously linking to a massive database in the rear, locally being run on XP (or can use Win7 if preferable). And all this, because my admin assistant was off for a week, and needing to progress, doing some of their work, and realising how inefficient it was!
The random clicks. Are they actions such as Save etc etc and can they be performed with keyboard shortcuts?
Give this a whirl. You will need to set the x y locations in the FillArray Function. SendKeys isn't the most efficient way and I'm sure it could be better. But I'm self taught and am no Expert Code: '**Win32 API Declarations Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long Private Declare Function mouse_event Lib "user32.dll" (ByVal dwflags As Integer, ByVal dx As Integer, ByVal dy As Integer, ByVal dwData As Integer, ByVal dwExtraInfo As Integer) As Integer Const MOUSEEVENTF_LEFTDOWN As Integer = 2 Const MOUSEEVENTF_LEFTUP As Integer = 4 Const MOUSEEVENTF_RIGHTDOWN As Integer = 8 Const MOUSEEVENTF_RIGHTUP As Integer = 16 Public InputValues(1 To 9, 2) As Variant Sub InSoftSendKeys() Dim i As Long, j As Long For i = 4 To 4 'Insert Row number or we can make this dynamic by evaluating the last row in the worksheet FillArray (i) AppActivate ("Insoft") 'Activate Insoft - Rename as appropriate SetCursorPos InputValues(1, 1), InputValues(1, 2) 'Sets the mouse location for loc1 Click Application.SendKeys InputValues(1, 0), True Application.SendKeys "{TAB}", True For j = 3 To 4 SetCursorPos InputValues(j, 1), InputValues(j, 2) 'Set Mouse Position for loc2 and 3 Click Application.SendKeys InputValues(j, 0), True Next j SetCursorPos InputValues(2, 1), InputValues(2, 2) 'Set Mouse Position for loc4 Click Application.SendKeys InputValues(2, 0), True SetCursorPos InputValues(5, 1), InputValues(5, 2) 'Set Mouse Position for loc5 Click Application.SendKeys InputValues(5, 0), True '1st Set of Random clicks SetCursorPos InputValues(6, 1), InputValues(6, 2) 'Set Mouse Position for loc6 Click Application.SendKeys InputValues(6, 0), True '2nd Set of Random clicks For j = 7 To 9 SetCursorPos InputValues(j, 1), InputValues(j, 2) 'Set Mouse Position for loc7 to 9 Click Application.SendKeys InputValues(j, 0), True Next j 'Last lot of Random Clicks Next i AppActivate ("Microsoft Excel") End Sub Function FillArray(row As Long) InputValues(1, 0) = Cells(row, 2).Value 'Value in B InputValues(1, 1) = 100 'x location for output InputValues(1, 2) = 100 'y location for output InputValues(2, 0) = Cells(row, 12).Value 'Value in L InputValues(2, 1) = 100 'x location for output InputValues(2, 2) = 100 'y location for output InputValues(3, 0) = Cells(row, 14).Value 'Value in N InputValues(3, 1) = 100 'x location for output InputValues(3, 2) = 100 'y location for output InputValues(4, 0) = Cells(row, 16).Value 'Value in P InputValues(4, 1) = 100 'x location for output InputValues(4, 2) = 100 'y location for output InputValues(5, 0) = Cells(row, 17).Value 'Value in Q InputValues(5, 1) = 100 'x location for output InputValues(5, 2) = 100 'y location for output InputValues(6, 0) = Cells(row, 18).Value 'Value in R InputValues(6, 1) = 100 'x location for output InputValues(6, 2) = 100 'y location for output InputValues(7, 0) = Cells(row, 19).Value ' Value in S InputValues(7, 1) = 100 'x location for output InputValues(7, 2) = 100 'y location for output InputValues(8, 0) = Cells(row, 20).Value 'Value in T InputValues(8, 1) = 100 'x location for output InputValues(8, 2) = 100 'y location for output InputValues(9, 0) = Cells(row, 21).Value 'Value in U InputValues(9, 1) = 100 'x location for output InputValues(9, 2) = 100 'y location for output End Function Function Click() mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0 mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0 End Function
You can probably use Autoit as well (or in conjunction with the vb script). The language is very similar to VB and CPP. There's the added advantage of certain functions making life easier (like shell commands or window title/ pid lookup or mouse/ kb macros).
Thankyou so much for taking the time to help (by all means, invoice me for your time!) I get this error when trying to run though. This happens whether I use your '9' specified value or the 26. All I've done, is modify the inputvalues to reflect an updated spreadsheet format, made the array a complete 26 letters and removed the iterations? recursions? that you used to simplify/tidy the code as they were no longer sequential. Code: '**Win32 API Declarations Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long Private Declare Function mouse_event Lib "user32.dll" (ByVal dwflags As Integer, ByVal dx As Integer, ByVal dy As Integer, ByVal dwData As Integer, ByVal dwExtraInfo As Integer) As Integer Const MOUSEEVENTF_LEFTDOWN As Integer = 2 Const MOUSEEVENTF_LEFTUP As Integer = 4 Const MOUSEEVENTF_RIGHTDOWN As Integer = 8 Const MOUSEEVENTF_RIGHTUP As Integer = 16 Public InputValues(1 To 26, 2) As Variant Sub InSoftSendKeys() Dim i As Long, j As Long For i = 3 To 8 'Insert Row number or we can make this dynamic by evaluating the last row in the worksheet FillArray (i) AppActivate ("InSoft") 'Activate InSoft SetCursorPos InputValues(19, 1), InputValues(19, 2) 'Sets the mouse location for loc1 Click Application.SendKeys InputValues(19, 0), True SetCursorPos InputValues(18, 1), InputValues(18, 2) 'Set Mouse Position for loc2 Click Application.SendKeys InputValues(18, 0), True SetCursorPos InputValues(12, 1), InputValues(12, 2) 'Set Mouse Position for loc3 Click Application.SendKeys InputValues(12, 0), True SetCursorPos InputValues(17, 1), InputValues(17, 2) 'Set Mouse Position for loc4 Click Application.SendKeys InputValues(17, 0), True SetCursorPos InputValues(13, 1), InputValues(13, 2) 'Set Mouse Position for loc5 Click Application.SendKeys InputValues(13, 0), True SetCursorPos InputValues(14, 1), InputValues(14, 2) 'Set Mouse Position for loc6 Click Application.SendKeys InputValues(14, 0), True SetCursorPos InputValues(15, 1), InputValues(15, 2) 'Set Mouse Position for loc7 Click Application.SendKeys InputValues(15, 0), True SetCursorPos InputValues(16, 1), InputValues(16, 2) 'Set Mouse Position for loc8 Click Application.SendKeys InputValues(16, 0), True SetCursorPos InputValues(11, 1), InputValues(11, 2) 'Set Mouse Position for loc9 Click Application.SendKeys InputValues(11, 0), True Next i AppActivate ("Microsoft Excel") End Sub Function FillArray(row As Long) InputValues(1, 0) = Cells(row, 1).Value 'Value in A InputValues(1, 1) = 100 'x location for output InputValues(1, 2) = 100 'y location for output InputValues(2, 0) = Cells(row, 2).Value 'Value in B InputValues(2, 1) = 100 'x location for output InputValues(2, 2) = 100 'y location for output InputValues(3, 0) = Cells(row, 3).Value 'Value in C InputValues(3, 1) = 100 'x location for output InputValues(3, 2) = 100 'y location for output InputValues(4, 0) = Cells(row, 4).Value 'Value in D InputValues(4, 1) = 100 'x location for output InputValues(4, 2) = 100 'y location for output InputValues(5, 0) = Cells(row, 5).Value 'Value in E InputValues(5, 1) = 100 'x location for output InputValues(5, 2) = 100 'y location for output InputValues(6, 0) = Cells(row, 6).Value 'Value in F InputValues(6, 1) = 100 'x location for output InputValues(6, 2) = 100 'y location for output InputValues(7, 0) = Cells(row, 7).Value ' Value in G InputValues(7, 1) = 100 'x location for output InputValues(7, 2) = 100 'y location for output InputValues(8, 0) = Cells(row, 8).Value 'Value in H InputValues(8, 1) = 100 'x location for output InputValues(8, 2) = 100 'y location for output InputValues(9, 0) = Cells(row, 9).Value 'Value in I InputValues(9, 1) = 100 'x location for output InputValues(9, 2) = 100 'y location for output InputValues(10, 0) = Cells(row, 10).Value 'Value in J InputValues(10, 1) = 100 'x location for output InputValues(10, 2) = 100 'y location for output InputValues(11, 0) = Cells(row, 11).Value 'Value in K InputValues(11, 1) = 400 'x location for output InputValues(11, 2) = 435 'y location for output InputValues(12, 0) = Cells(row, 12).Value 'Value in L InputValues(12, 1) = 345 'x location for output InputValues(12, 2) = 260 'y location for output InputValues(13, 0) = Cells(row, 13).Value 'Value in M InputValues(13, 1) = 150 'x location for output InputValues(13, 2) = 320 'y location for output InputValues(14, 0) = Cells(row, 14).Value 'Value in N InputValues(14, 1) = 150 'x location for output InputValues(14, 2) = 345 'y location for output InputValues(15, 0) = Cells(row, 15).Value 'Value in O InputValues(15, 1) = 150 'x location for output InputValues(15, 2) = 400 'y location for output InputValues(16, 0) = Cells(row, 16).Value 'Value in P InputValues(16, 1) = 150 'x location for output InputValues(16, 2) = 420 'y location for output InputValues(17, 0) = Cells(row, 17).Value 'Value in Q InputValues(17, 1) = 150 'x location for output InputValues(17, 2) = 290 'y location for output InputValues(18, 0) = Cells(row, 18).Value 'Value in R InputValues(18, 1) = 150 'x location for output InputValues(18, 2) = 260 'y location for output InputValues(19, 0) = Cells(row, 19).Value 'Value in S InputValues(19, 1) = 150 'x location for output InputValues(19, 2) = 235 'y location for output InputValues(20, 0) = Cells(row, 20).Value 'Value in T InputValues(20, 1) = 100 'x location for output InputValues(20, 2) = 100 'y location for output InputValues(21, 0) = Cells(row, 21).Value 'Value in U InputValues(21, 1) = 100 'x location for output InputValues(21, 2) = 100 'y location for output InputValues(22, 0) = Cells(row, 22).Value 'Value in V InputValues(22, 1) = 100 'x location for output InputValues(22, 2) = 100 'y location for output InputValues(23, 0) = Cells(row, 23).Value 'Value in W InputValues(23, 1) = 100 'x location for output InputValues(23, 2) = 100 'y location for output InputValues(24, 0) = Cells(row, 24).Value 'Value in X InputValues(24, 1) = 100 'x location for output InputValues(24, 2) = 100 'y location for output InputValues(25, 0) = Cells(row, 25).Value 'Value in Y InputValues(25, 1) = 100 'x location for output InputValues(25, 2) = 100 'y location for output InputValues(26, 0) = Cells(row, 26).Value 'Value in Z InputValues(26, 1) = 100 'x location for output InputValues(26, 2) = 100 'y location for output End Function Function Click() mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0 mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
I have no idea! Will let you know tomorrow. Regardless, thank you so much for all your time spared so far.
You were right, It was in the wrong place - I made a standalone module and it works perfectly. I've made it quite a bit more complicated now too. Struggling to use the wait commands at the mo though. Clicks for pop-up messages just can't be timed well! You've saved my bacon - hopefully I won't have any more problems!
