I have excel file 1 with a drop down menu in in that allows me to select the mon

Question

I have excel file 1 with a drop down menu in in that allows me to select the month and the week number. I also have a folder with multiple excel workbooks in it for every month of the year and in each one there is data filled in for each week. What I need to happen is when I select a month from the drop down menu in excel file 1 and a week number then I need the data to be pulled over from whichever corresponding workbook it needs to be. So if i select October week 2 then I need the data filled in from my October workbook and the week 2 column. How would I do that and the October workbook would be closed. I have excel file 1 with a drop down menu in in that allows me to select the month and the week number. I also have a folder with multiple excel workbooks in it for every month of the year and in each one there is data filled in for each week. What I need to happen is when I select a month from the drop down menu in excel file 1 and a week number then I need the data to be pulled over from whichever corresponding workbook it needs to be. So if i select October week 2 then I need the data filled in from my October workbook and the week 2 column. How would I do that and the October workbook would be closed.

Explanation / Answer

Getting data from a closed workbook is little complicated. The only way to do this using VBA code.

Step 1: Create a VBA Macro as follow

open your destination file

press Alt+F11 - a window will open Visual Basic Editor

Right click on your workbook name in the project-VBAProject pane

in insert select Module option

click on module copy the code (code given at bottom)

press Ctr+S and save the file. Give a name to macro and choose file type Excel macro-enabled workbook

close VBE editor

Step 2:

Create a Button in your destination Workbook

Add the Macro created earlier to this button

VBA code :

Private Function GetData(path, file, sheet, ref)

Dim arg As String

'code to check does the file exist or not)
If Right(path, 1) <> “” Then path = path & “”
If Dir(path & file) = “” Then
GetValueFromClosedWorkbook = “File Not Found”
Exit Function
End If

‘ We create the argument
arg = “‘” & path & “[” & file & “]” & sheet & “‘!” & _
Range(ref).Range(“A1”).Address(, , xlR1C1)

Sub GetData ()
Dim p As String, f As String
Dim s As String, a As String

'here we are getting data from A1 Cell from source and placing in C2 of destination file , if you want add multiple rows use loop
p = "path of your work book"
f = “October.xlsx”
s = “Sheet1”
a = “A1” ActiveSheet.Range(“C2”) = GetValueFromClosedWorkbook(p, f, s, a)

End Sub

Modify VBA code as per your requirement.

If you still have proble place it in comments section

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.