PDA

View Full Version : Open Workbook when the workbook name is combination of cells



vagofone
07-19-2016, 10:09 AM
Hey people,


I would be really happy if somebody could help me with the following problem:


I have some excel files. The Team file and the Team members files. I am trying to create a macro in The Team File to do the following:



For every Row in the Team file go to the Team members file find some information and copy them. The Members files are in the same folder and they are named after the people's names. It seams that VBA does not accept the name of the Workbook of every member, since I am trying to add it with cell references of the members names:



for r = 2 To 240
workbook ("Filepath" & Cell(r, 2).Value & "_" & Cell(r,3).Value & ".xls").Activate
etc.
next r


Any Ideas how I could make it work?


I would be grateful for your help.


Cheers

jolivanes
07-19-2016, 02:50 PM
Re: VBA does not accept the name of the Workbook of every member
Why would it not?
If the code opens some and not others it might be because of slight differences in spelling.
Easy to do, certainly in the German (Umlauts etc)
One way to remedy that is to have code put all the file names in a sheet.
Otherwise it would be a good idea to show potential and willing helpers the whole code instead of just a couple of lines.

jolivanes
07-19-2016, 06:40 PM
This works for me.

Sub Vielleicht()
Dim file_Open As String, i As Long
With Application
.DisplayAlerts = False
.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
file_Open = Dir(ThisWorkbook.Path & "\" & Cells(i, 1).Value & "_" & Cells(i, 2).Value & ".xl*")
Workbooks.Open ThisWorkbook.Path & "\" & file_Open
With ActiveWorkbook.Sheets(1)
.Range("A1:A20").Copy ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1)
End With
Workbooks(file_Open).Close False
file_Open = Dir
Next i
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub


Put all 6 attachments in the same folder.
Open "Vagofone on vbaexpress.xlsm" and click on the button on Sheet2

jolivanes
06-04-2017, 09:49 AM
It's just about a year. Did you get it to work in this time.
It is nice to know that people appreciate the help they are getting "umsonst"