andreeaiancu
02-17-2022, 12:00 PM
Hello,
I have tree macros and I want to link two of them:
1. The first one( Report macro) creates an open an Excel Document from where I want to copy some date to another Excel template created by another macro
So, I want Report macro to open its result and open the other macro result and to paste there the data I need. I need to specify that both documents have different name and I used fullpath method, please see below:
Dim sht As Worksheet
Dim sapId As String
Dim myPath As String
Dim sExt As String
Dim fullpath As String
Set sht = ThisWorkbook.Worksheets("Sheet2")
lr = sht.Range("A2").CurrentRegion.Rows.Count
For i = 2 To lr
sapId = ThisWorkbook.Worksheets("Sheet1").Cells(i, 1)
DATEto = ThisWorkbook.Worksheets("Sheet1").Cells(i, 2)
DATEuntil = ThisWorkbook.Worksheets("Sheet1").Cells(i, 3)
Sheets("Sheet2").Select
Range("A1:B1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$47").AutoFilter Field:=1, Criteria1:=sapId
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
sht.Range("C2:C" & lr).Copy
session.findById("wnd[0]").resizeWorkingPane 186, 22, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nvf05"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtFACOM-KUNDE").Text = sapId
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT").Text = DATEto
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT_BIS").Text = DATEuntil
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT_BIS").SetFocus
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT_BIS").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[33]").press
session.findById("wnd[1]/usr/ctxtFACOM-VKORG").Text = "PL12"
session.findById("wnd[1]/usr/ctxtFACOM-VKORG").caretPosition = 4
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/ctxtFACOM-KUNDE").caretPosition = 8
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell -1, "MATNR"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectColumn "MATNR"
session.findById("wnd[0]/tbar[1]/btn[38]").press
session.findById("wnd[1]/usr/ssub%SUBSCREEN_FREESEL:SAPLSSEL:1105/btn%%%DYN001_%APP%-VALU_PUSH").press
session.findById("wnd[2]/tbar[0]/btn[24]").press
session.findById("wnd[2]/tbar[0]/btn[8]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell 5, "MATNR"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectedRows = "5"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\85111430\Desktop\Corrective invoices\VF05 EXPORT"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = sapId & ".XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 13
session.findById("wnd[1]/tbar[0]/btn[11]").press
Call opening
Next i
End Sub
Sub opening()
Dim sapId As String
Dim myPath As String
Dim myPath2 As String
Dim sExt As String
Dim sExt2 As String
Dim fullpath As String
Dim fullpath2 As String
Dim wb As Workbook
Dim wb2 As Workbook
Dim name As String
lr2 = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr2
sExt = ".xlsx"
myPath = "C:\Users***\Desktop\Corrective invoices\VF05 EXPORT"
sapId =Cells(i,1).Value
name =Cells(i,4).Value
fullpath = myPath & sapId & sExt
IfLen(Dir$(fullpath))<>0Then
Set wb =Workbooks.Open(fullpath)==> here it opens its result and after this makes the changes
wb.Sheets(1).Activate
EndIf
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-22
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("K3").Select
ActiveSheet.Paste
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("M3").Select
ActiveSheet.Paste
Range("K3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.Range("$K$3:$M$144").RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlNo
ActiveWindow.SmallScroll Down:=-132
Selection.Copy
Next i
2.
Dim name As String
Dim sapId As String
Dim myPath2 As String
Dim sExt2 As String
Dim fullpath2 As String
Dim wb2 As Workbook
sExt2 = ".xlsx"
myPath2 = "C:\Users***\Desktop\Corrective invoices\Invoices"
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
sapId = Cells(i, 1).Value
name = Cells(i, 4).Value
fullpath2 = myPath2 & name &" "& sapId & sExt2
IfLen(Dir$(fullpath2))<>0Then
Set wb2 =Workbooks.Open(fullpath2)
wb2.Sheets(2).Activate
How can I merge the 2 macros in order to work to open both documents using fullpath and to paste in wb2.Sheet 2 Range(B3:D &lr) the data copied at the end of the first macro?
Your help will be much appreciated
I have tree macros and I want to link two of them:
1. The first one( Report macro) creates an open an Excel Document from where I want to copy some date to another Excel template created by another macro
So, I want Report macro to open its result and open the other macro result and to paste there the data I need. I need to specify that both documents have different name and I used fullpath method, please see below:
Dim sht As Worksheet
Dim sapId As String
Dim myPath As String
Dim sExt As String
Dim fullpath As String
Set sht = ThisWorkbook.Worksheets("Sheet2")
lr = sht.Range("A2").CurrentRegion.Rows.Count
For i = 2 To lr
sapId = ThisWorkbook.Worksheets("Sheet1").Cells(i, 1)
DATEto = ThisWorkbook.Worksheets("Sheet1").Cells(i, 2)
DATEuntil = ThisWorkbook.Worksheets("Sheet1").Cells(i, 3)
Sheets("Sheet2").Select
Range("A1:B1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$47").AutoFilter Field:=1, Criteria1:=sapId
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
sht.Range("C2:C" & lr).Copy
session.findById("wnd[0]").resizeWorkingPane 186, 22, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nvf05"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtFACOM-KUNDE").Text = sapId
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT").Text = DATEto
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT_BIS").Text = DATEuntil
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT_BIS").SetFocus
session.findById("wnd[0]/usr/ctxtFACOM-FKDAT_BIS").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[33]").press
session.findById("wnd[1]/usr/ctxtFACOM-VKORG").Text = "PL12"
session.findById("wnd[1]/usr/ctxtFACOM-VKORG").caretPosition = 4
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/ctxtFACOM-KUNDE").caretPosition = 8
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell -1, "MATNR"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectColumn "MATNR"
session.findById("wnd[0]/tbar[1]/btn[38]").press
session.findById("wnd[1]/usr/ssub%SUBSCREEN_FREESEL:SAPLSSEL:1105/btn%%%DYN001_%APP%-VALU_PUSH").press
session.findById("wnd[2]/tbar[0]/btn[24]").press
session.findById("wnd[2]/tbar[0]/btn[8]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").setCurrentCell 5, "MATNR"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectedRows = "5"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\85111430\Desktop\Corrective invoices\VF05 EXPORT"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = sapId & ".XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 13
session.findById("wnd[1]/tbar[0]/btn[11]").press
Call opening
Next i
End Sub
Sub opening()
Dim sapId As String
Dim myPath As String
Dim myPath2 As String
Dim sExt As String
Dim sExt2 As String
Dim fullpath As String
Dim fullpath2 As String
Dim wb As Workbook
Dim wb2 As Workbook
Dim name As String
lr2 = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr2
sExt = ".xlsx"
myPath = "C:\Users***\Desktop\Corrective invoices\VF05 EXPORT"
sapId =Cells(i,1).Value
name =Cells(i,4).Value
fullpath = myPath & sapId & sExt
IfLen(Dir$(fullpath))<>0Then
Set wb =Workbooks.Open(fullpath)==> here it opens its result and after this makes the changes
wb.Sheets(1).Activate
EndIf
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-22
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("K3").Select
ActiveSheet.Paste
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("M3").Select
ActiveSheet.Paste
Range("K3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.Range("$K$3:$M$144").RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlNo
ActiveWindow.SmallScroll Down:=-132
Selection.Copy
Next i
2.
Dim name As String
Dim sapId As String
Dim myPath2 As String
Dim sExt2 As String
Dim fullpath2 As String
Dim wb2 As Workbook
sExt2 = ".xlsx"
myPath2 = "C:\Users***\Desktop\Corrective invoices\Invoices"
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
sapId = Cells(i, 1).Value
name = Cells(i, 4).Value
fullpath2 = myPath2 & name &" "& sapId & sExt2
IfLen(Dir$(fullpath2))<>0Then
Set wb2 =Workbooks.Open(fullpath2)
wb2.Sheets(2).Activate
How can I merge the 2 macros in order to work to open both documents using fullpath and to paste in wb2.Sheet 2 Range(B3:D &lr) the data copied at the end of the first macro?
Your help will be much appreciated