AnswerSeeker
04-05-2012, 02:04 AM
Hello
I am trying to open from the main workbook several other workbooks and than read a certain cell value via vlookup.
The numbers of workbooks and are changing and also the the place and where they are saved and names might be changed in the future wherefore I used several variables
Application.Workbooks(MainWorkbook).Worksheets(1).Activate
For a = 1 To CountMonth Step 1
wholePath = myPath & MyMonthName(a) & " " & myYearFile & myFileName
Workbooks.Open Filename:=wholePath
Application.Workbooks(MainWorkbook).Worksheets(1).Activate
Range("E26").Offset(0, j).Value = Application.WorksheetFunction.VLookup(SearchCriteria, Workbooks(MyMonthName(a) & " " & myYearFile & myFileName).Sheets("Pivot").Range("A:E"), 5, False)
Workbooks(MyMonthName(a) & " " & myYearFile & myFileName).Close SaveChanges:=False
j = j + 2
Next a
The other workbook is working which means the variable wholePath is correct, thus variables myPath, MyMonthName, myYearFile an myFileName are correct.
does anyone knows my mistake?
I am trying to open from the main workbook several other workbooks and than read a certain cell value via vlookup.
The numbers of workbooks and are changing and also the the place and where they are saved and names might be changed in the future wherefore I used several variables
Application.Workbooks(MainWorkbook).Worksheets(1).Activate
For a = 1 To CountMonth Step 1
wholePath = myPath & MyMonthName(a) & " " & myYearFile & myFileName
Workbooks.Open Filename:=wholePath
Application.Workbooks(MainWorkbook).Worksheets(1).Activate
Range("E26").Offset(0, j).Value = Application.WorksheetFunction.VLookup(SearchCriteria, Workbooks(MyMonthName(a) & " " & myYearFile & myFileName).Sheets("Pivot").Range("A:E"), 5, False)
Workbooks(MyMonthName(a) & " " & myYearFile & myFileName).Close SaveChanges:=False
j = j + 2
Next a
The other workbook is working which means the variable wholePath is correct, thus variables myPath, MyMonthName, myYearFile an myFileName are correct.
does anyone knows my mistake?