sanantonio
Board Regular
- Joined
- Oct 26, 2021
- Messages
- 124
- Office Version
- 365
- Platform
- Windows
Hi All,
I'm trying to recycle a loop that @NateSC helped me with previously. This might sound premature but I only have limited testing time (4 hours on Wednesday afternoon) where I'll have access to the systems I need.
What it should do is loop through the data in the table below and "sendkey" it to a module within our client-specific software.
This list could be as many as 250 lines of entry or 1 or 2 lines of entry depending on the week and decisions made by other business divisions.
This is the code that I've adapted that I think will work (Like I say I can't test this until Wednesday). Can anyone see anything that'll go wrong?
Thanks in advance!
I'm trying to recycle a loop that @NateSC helped me with previously. This might sound premature but I only have limited testing time (4 hours on Wednesday afternoon) where I'll have access to the systems I need.
What it should do is loop through the data in the table below and "sendkey" it to a module within our client-specific software.
This list could be as many as 250 lines of entry or 1 or 2 lines of entry depending on the week and decisions made by other business divisions.
This is the code that I've adapted that I think will work (Like I say I can't test this until Wednesday). Can anyone see anything that'll go wrong?
VBA Code:
Sub SFO_Entry()
Dim myAlo As Range
Dim myRow, myCount As Long
Dim myWindow As String
Dim myItem, myQuantity As Range
Dim mySlot As Variant
Dim hWnd As Long
Dim Row1 As Long, Row2 As Long, Num1 As Long, Counter1 As Long
Dim Item As Range, Items As Range
Dim ItemCode As String
Dim Window1 As String, Window2 As String, Window3 As String
If Cell Is Nothing Then
'Select MDE Module
Window1 = "[SFO000] - Store Forms Module - DB: USWH00" & Sheets("Cover").Range("J13").Value & "L (USWH00" & Sheets("Cover").Range("J13").Value & "L) Schema: WAWIADM Role: R_WAWI"
Window2 = "[SFO002] Store Claim Delivery"
hWnd = FindWindow(vbNullString, Window1)
SetForegroundWindow hWnd
If hWnd > 0 Then
Else
MsgBox ("SFO Module cannot be found.")
myCancel = "Cancel"
Exit Sub
End If
' Updated code below this point !!!
Dim numStores As Integer
Dim i As Integer
Dim j As Integer
Dim temp(10) As Variant
Dim ws As Worksheet
Dim myLastCol As Integer
Dim myFirstCol As Integer
Dim myLastRow As Integer
Dim myFirstRow As Integer
Dim myCurrentRow As Integer
Set ws = ActiveSheet
' Finds first and last rows and columns with data
myLastRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
myFirstRow = ws.Cells(myLastRow, 4).End(xlUp).Row
myLastCol = ws.Cells(myFirstRow, Columns.Count).End(xlToLeft).Column
myFirstCol = ws.Cells(myFirstRow, myLastCol).End(xlToLeft).Column
'Loop 1, enters store, document number, document date
For i = 1 To numStores
'Store
temp(1) = ws.Cells(myFirstRow, myFirstCol - 1 + i * 2).Value
SendKeys temp(1)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Document Nr
temp(2) = ws.Cells(myFirstRow + 3, myFirstCol - 1 + i * 2).Value
SendKeys temp(2)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Picklist Nr
temp(3) = ws.Cells(myFirstRow + 2, myFirstCol - 1 + i * 2).Value
SendKeys temp(3)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Item
temp(4) = ws.Cells(myFirstRow + 1, myFirstCol - 1 + i * 2).Value
SendKeys temp(4)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Case Size
temp(4) = ws.Cells(myFirstRow + 1, myFirstCol - 1 + i * 2).Value
SendKeys temp(5)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Case Qty
temp(4) = ws.Cells(myFirstRow + 1, myFirstCol - 1 + i * 2).Value
SendKeys temp(6)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Units Qty
temp(4) = ws.Cells(myFirstRow + 1, myFirstCol - 1 + i * 2).Value
SendKeys temp(7)
SendKeys ("{TAB}")
Application.Wait (Now + TimeValue("00:00:01"))
'Then inserts
SendKeys ("{TAB}")
SendKeys ("{TAB}")
SendKeys (" ")
Application.Wait (Now + TimeValue("00:00:01"))
SendKeys ("{F3}")
Next i
SendKeys ("{ESC}")
MsgBox "Action Complete"
End If
End SubThanks in advance!
