vba partial names variables

[andreeaiancu]

Hello,

I want to open more documents with VBA one by one and to make some changes in it after I extract them from sap with the following name :' sap id name of the client" eg. "546930 XXX"
The documents are xls files that are called " sap id name of the client". How can I open them if I defined 2 variables : sap id= cells(i,2) and name= cells(i,3)?

Can you please give me an idea?
Thanks.

Sub Extract()

Dim fname As Variant
Dim myPath As String

lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr

myPath = "C:\Users\*****\Desktop\Client"
fname = Cells(i, 4)

If fname <> "" Then
Workbooks.Open (myPath & fname)
End If


In that path I will have had several reports extracted from SAP.

[arnelgp]

test if the file exists, using Dir$()


Sub Extract()
Dim fname As Variant
Dim myPath As String
myPath = "C:\Users\*****\Desktop\Client"
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
fname = Cells(i, 4)
If Dir$(mypath & fname) <> "" Then
Workbooks.Open (myPath & fname)
End If
Next

[andreeaiancu]

test if the file exists, using Dir$()

the code doesn't open me the document... even if it is there

[Bob Phillips]

You don't say if there is one sap id for all, or you have spa id/name pairs. Assuming the former, is this what you mean?


Sub Extract()
Dim fname As Variant
Dim sapId As String
Dim myPath As String

myPath = "C:\Users\*****\Desktop\Client\"
sapId = Cells(1, 2).Value

lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To lr

fname = Cells(i, 3).Value
If fname <> vbNullString Then Workbooks.Open myPath & sapId & fname
Next i
End Sub

[andreeaiancu]

Hello,

I tried also your variant and for pairs of IDs and Names I can't open the excel files (I also added the ".xls" folder type)


Dim fname As Variant
Dim sapId As String
Dim myPath As String
Dim sExt As String
myPath = "C:\Users\******\Desktop\Client"
sapId = Cells(1, 2).Value
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To lr
sExt = ".xls"
fname = Cells(i, 3).Value
If fname <> vbNullString Then Workbooks.Open myPath & sapId & fname & sExt
Next i

[Bob Phillips]

I think you need to give more details, it is not clear what your setup is or what is going wrong.

[andreeaiancu]

Starting from cell B2 I have the ID, and in cell C2 I have the Name, the extension is ".XLS". Also, the document exists in the folder but it doesn't open it, I don't know maybe the combination is wrong
It appears that msgbox I ve set but the document is not opened

Sub Extract()


Dim fname As Variant
Dim sapId As String
Dim myPath As String
Dim sExt As String


myPath = "C:\Users\*****\Desktop\Client"
sapId = Cells(1, 2).Value
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To lr
sExt = ".xls"
fname = Cells(i, 3).Value
If fname <> vbNullString Then Workbooks.Open myPath & sapId & fname & sExt
Next i


MsgBox "Done"


End Sub

The name of the client from thefolder:

109765 Lundberg H.xls

[Bob Phillips]

Are you simply missing the space in the constructed filename?

[andreeaiancu]

I added it also but no chance :

Sub Extract()
Dim fname As Variant
Dim sapId As String
Dim myPath As String
Dim sExt As String
myPath = "C:\Users\****\Desktop\Client"
sapId = Cells(1, 2).Value
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To lr
sExt = ".xls"
fname = Cells(i, 3).Value
If fname <> vbNullString Then Workbooks.Open myPath & sapId & " " & fname & sExt
Next i
MsgBox "Done"
End Sub

[arnelgp]

Sub Extract()




Dim fname As Variant
Dim sapId As String
Dim myPath As String
Dim sExt As String
Dim fullpath as string
sExt = ".xls"
myPath = "C:\Users\*****\Desktop\Client\"
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
sapId = Cells(i, 2).Value
fname = Cells(i, 3).Value


fullpath=mypath & sapid & " " & fname & sExt
if len(dir$(fullpath)) <> 0 then
Workbooks.Open fullpath
end if
Next i




MsgBox "Done"




End Sub

[andreeaiancu]

Thank you!!
is there a way in order to activate this workbooks?
i am new to this and I didn’t activate workbooks opened like this before
I mean I want to activate the first sheet of this workbook defined by the fullpath below






Sub Extract()




Dim fname As Variant
Dim sapId As String
Dim myPath As String
Dim sExt As String
Dim fullpath as string
sExt = ".xls"
myPath = "C:\Users\*****\Desktop\Client\"
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
sapId = Cells(i, 2).Value
fname = Cells(i, 3).Value


fullpath=mypath & sapid & " " & fname & sExt
if len(dir$(fullpath)) <> 0 then
Workbooks.Open fullpath
end if
Next i




MsgBox "Done"




End Sub

[arnelgp]

you can active the First sheet by Selecting it:


Sub Extract()




Dim fname As Variant
Dim sapId As String
Dim myPath As String
Dim sExt As String
Dim fullpath as string
dim wb As Workbook
sExt = ".xls"
myPath = "C:\Users\*****\Desktop\Client\"
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
sapId = Cells(i, 2).Value
fname = Cells(i, 3).Value


fullpath=mypath & sapid & " " & fname & sExt
if len(dir$(fullpath)) <> 0 then
Set wb = Workbooks.Open(fullpath)
wb.Sheets(1).Activate
end if
Next i




MsgBox "Done"



End Sub

[andreeaiancu]

thank you!!!

I encounter an error:

I want to do: copy data from wb and paste it to wb2 after I open it in the same way (with fullpath2). If I open the wb2 separately, it opens, but when I add :

sapId = Cells(i, 1).Value
name = Cells(i, 4).Value

fullpath2 = myPath2 & name & " " & sapId & sExt2
If Len(Dir$(fullpath2)) <> 0 Then
Set wb2 = Workbooks.Open(fullpath2)
wb2.Sheets(2).Activate

End If

into the first macro It didn't opens and it didn't paste the data.

Can you help?



Sub VF05_PL12()

Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
Set Connection = App.Children(0)
Set session = Connection.Children(0)

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
........................................................................

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


If Len(Dir$(fullpath)) <> 0 Then
Set wb = Workbooks.Open(fullpath)
wb.Sheets(1).Activate
End If

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
...........................................................(changes done by me in wb excel file)

Selection.Copy ==? the data I want to copy in wb2 in range (B2:D &LR)

sExt2 = ".xlsx"
myPath2 = "C:\Users\****\Desktop\Corrective invoices\Invoices"
lr = Cells(Rows.Count, "A").End(xlUp).Row


sapId = Cells(i, 1).Value
name = Cells(i, 4).Value

fullpath2 = myPath2 & name & " " & sapId & sExt2
If Len(Dir$(fullpath2)) <> 0 Then
Set wb2 = Workbooks.Open(fullpath2)
wb2.Sheets(2).Activate

End If
Next i


MsgBox "Done"
End Sub

[arnelgp]

one comment is that you always end your Paths with backslash "", like:

myPath = "C:\Users\*********\Desktop\Corrective invoices\VF05 EXPORT\"

[andreeaiancu]

The issue is that it opens the first document, but not the second even if I set \

But id doesn't perform the command :it doessn't open the document if I add it to the complex macro, If i run t alone it works