View Full Version : vba partial names variables
andreeaiancu
02-15-2022, 04:38 AM
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
02-15-2022, 05:13 AM
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
02-15-2022, 05:47 AM
test if the file exists, using Dir$()
the code doesn't open me the document... even if it is there
Bob Phillips
02-15-2022, 07:02 AM
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
02-16-2022, 12:27 AM
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
02-16-2022, 04:55 AM
I think you need to give more details, it is not clear what your setup is or what is going wrong.
andreeaiancu
02-16-2022, 05:11 AM
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
02-16-2022, 05:29 AM
Are you simply missing the space in the constructed filename?
andreeaiancu
02-16-2022, 05:42 AM
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
02-16-2022, 06:18 AM
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
02-16-2022, 11:51 AM
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
02-16-2022, 06:24 PM
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
02-17-2022, 03:37 AM
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
02-17-2022, 03:43 AM
one comment is that you always end your Paths with backslash "", like:
myPath = "C:\Users\*********\Desktop\Corrective invoices\VF05 EXPORT\"
andreeaiancu
02-17-2022, 04:10 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.