0

I'm working on a master file that contains a list of cells. For each cell, there is a corresponding Excel file (whose filename contains the text in the reference cell) which contains data I need to import into the Master file. At the moment, I have to run a macro individually to browse each file and click on the corresponding Excel file so that a file path is obtained:

Sub GetFile()
'Dim the variables
Dim FileSelect As Variant
Dim Wb As Workbook
Dim i As Integer
'on error statement
On Error GoTo errHandler:
'hold in memory
Application.ScreenUpdating = False
'locate the file path
FileSelect = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*", _
MultiSelect:=False)
'check if a file is selected
If FileSelect = False Then
MsgBox "Select the file name"
Exit Sub

End If
'send the path to the worksheet
Sheet1.Range("C4").Value = FileSelect

'open the workbook
Set Wb = Workbooks.Open(FileSelect)

'add the sheet names to the workbook
Sheet1.Range("K4:K100").ClearContents
For i = 1 To Sheets.Count
Sheet1.Range("K" & i + 3) = Sheets(i).Name
Next i

'close the workbook
Wb.Close False
Application.ScreenUpdating = True
Exit Sub

'error block
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the Ada'a Central Team"

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
R. Khrais
  • 1
  • Can you give an example of file names and what has to be matched? – Pᴇʜ Jun 23 '21 at 09:51
  • So for example, I have a cell D9 that contains "YYY-GEN-4" and in my downloads folder I have an Excel file with a filename like "POD-GEN-YYY-GEN-4.xlsx". The code I pasted above is one that I use manually to select the xlsx file myself and it would then extract the filename such as: "C:\Users\212775907\Downloads\POD-GEN-YYY-GEN-4.xlsx" in a different cell C4 – R. Khrais Jun 23 '21 at 11:08
  • Please see the link above your question. This has already been answered. – Pᴇʜ Jun 23 '21 at 11:24

0 Answers0