Get data from dynamic location and filename on closed workbook using VBA

[Joe006]

Hi, I got a lot of excel files in different location where each files is put in folder named month and year. I need to get the data from it while it's closed and data is pulled based on the drop down list for the month and the year. I have tried using the INDIRECT function and it work only when the file is opened. I just discovered that need to use VBA script to pull while it closed, if using VBA, how to call the data and set formula for the folder name and file dynamically according the chosen drop down list ?

Any helps much appreciated, Thank you

 

[eiloken]

if I change (filPath, 1) this will update link right?

Set wb = Workbooks.Open(filPath, 1) 'open with not update link

- 0, don’t update
- 1, update external links but not remote links
- 2, update remote links but not external links
- 3, update all links

 

[Joe006]

- 0, don’t update
- 1, update external links but not remote links
- 2, update remote links but not external links
- 3, update all links

Thank you!

 

[Joe006]

Hi @eiloken , i am currently working to add progress bar during vba code execution, i already create user form, is it right if i put main code at line below in form section? main code got button sub

Private Sub UserForm_Activate()

For i = 1 To 30
For j = 1 To 30
Cells(i, j) = 5 'excel main code put here'?
Next j
UserForm1.Label1.Caption = Str(i) + "% Completed"
UserForm1.Label2.Width = i * 2
UserForm1.Repaint 'Updating Screen
Next i

End Sub

Module 4 code for the progress bar for the form:

Sub Progress_Bar()

UserForm1.Caption = "Progress Bar"
UserForm1.Label1.Caption = "0% Completed"
UserForm1.Label2.Caption = ""
UserForm1.Label2.BackColor = vbHighlight
UserForm1.Frame1.Caption = ""
Load UserForm1
UserForm1.Show

End Sub

 

[eiloken]

Hi @eiloken , i am currently working to add progress bar during vba code execution, i already create user form, is it right if i put main code at line below in form section? main code got button sub

View attachment 102600

Private Sub UserForm_Activate()

For i = 1 To 30
For j = 1 To 30
Cells(i, j) = 5 'excel main code put here'?
Next j
UserForm1.Label1.Caption = Str(i) + "% Completed"
UserForm1.Label2.Width = i * 2
UserForm1.Repaint 'Updating Screen
Next i

End Sub

Module 4 code for the progress bar for the form:

Sub Progress_Bar()

UserForm1.Caption = "Progress Bar"
UserForm1.Label1.Caption = "0% Completed"
UserForm1.Label2.Caption = ""
UserForm1.Label2.BackColor = vbHighlight
UserForm1.Frame1.Caption = ""
Load UserForm1
UserForm1.Show

End Sub

yes, you can, but you can call it from Click event of button too, no need to move it to userform code