I have excel file 1 with a drop down menu in in that allows me to select the mon
ID: 3589142 • Letter: I
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