Link to home
Start Free TrialLog in
Avatar of M Zahid
M ZahidFlag for United Arab Emirates

asked on

VB loop to open the file from the network drive

Hello Guys
Request for a VB loop to open the file from the network drive

Up to below underline is actually folders path won’t change

\\share\Group\DEPT\GPO\1. C411 Price Form\ MEA\D6463 - SAF V\REV C

This part of the folders would be changing as per the new project or Revision

MEA\D6463 - SAF V\REV C

need a loop to how to code this in Macro/VB?
if i have full VB code to open the file that would be gr8 help..

thx

<<I removed tags for C and VB Script. This is an Excel VBA problem. byundt--Microsoft Excel Zone Advisor, 15 Nov 2016>>
Avatar of Bill Prew
Bill Prew

Can you clarify a little further.  What is it you want to happen repeatedly in the loop?  Do you want to open every file in that folder?  Or find the one file to open, and if that is the acse what logic should be used to identify the correct file to open?

It sounds like you want this to execute as a macro in Excel using VBA, is that correct?

~bp
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Bill,
This is how I m joining the file name ('=$D$3&" - "&$D$4&" "&D9&" Rev "&$D$5&".xlsb") in E9
here is the hyperlink I m using in Excel {=HYPERLINK("\\share\Group\DEPT\GPO\1. C411 Price Form\"&$D$6&""&$D$3&" - "&$D$4&""&"REV "&$D$5&""&E9,"Link")
& this is mix of ( "&$D$6&""&$D$3&" - "&$D$4&""&"REV "&$D$5&) 3 folders {  MEA\D6463 - SAF V\REV C }
& I need to open specific .xlsb file form here e.g. D6463 - C411 - SAF V Linepipe Rev C.xlsb,
in here starting of the file name will be same e.g. D6463 - C411 - SAF V after that there will be Linepipe or Bulk or Equip accordingly the Rev A or B or so on… .xlsb
yes I I m looking for a macro to run in Excel using VBA

regards
Avatar of M Zahid

ASKER

Hi Brad,

thx for the code, this is the error I m having here {.Save   'Save the workbook after having made changes} after running code

[Run time error 91
Object variable or With block variable not set]

& I can hardcode this  { flPath = "\\share\Group\DEPT\GPO\1. C411 Price Form\" }   bcoz it wont change

but this can or will be change {subFolder = "MEA\D6463 - SAF V\"} as I have mentiond in above reply to Bill.

really appreciate your response guys :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Brad & Bill,

I'm really sorry for the confusion & with so many questions which are all relate to my nervousness, plz find the attached word doc with complete expiation what exactly Im looking for.

Plz help me out to close this & issue this template to the users.

Best Regards
MZ
Summary-Report-link.docx
Avatar of M Zahid

ASKER

hello brad,

have you got my message?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Brad,

Thanks a lot & really appreciates on your solid work & support. Plz find the attached word doc with test where I have runs codes with the small hitches

Once it works, will defiantly send you the both old & new files to see ;)

Best Regards,
MZ
Summary-Report-link2.docx
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Brad,

I’m really sorry for not sharing/sending the excel file but i understand i shd have sheared my workbook at the first you have requested for but I was & am terrified as its company’s property/policy but plz its my humble request to keep this information with your kind self only not to share with anyone & I have not posted my workbook with the question of HYPERLINKS formulas as well.

Given Code is been working for the Master Sheet only with the popup & yes you are right Adding the updating feature is easy but I realized formula in other worksheet is not updating as Im looking for.

Column F { =IFERROR(INDIRECT("'["&E9&"]Report 1'!$G$48"),0) } linked with the file name which come in Column E
Column G {=HYPERLINK("\\share\Group\DEPT\GPO\1. C411 Price Form\} its hyperlink to link the target file to get the data.

If file browser dialog pop up only for the first 4 / 3 time to link the data would be ok but it wont be ok if I have to do for all the connected cells. But one big problem once the file browser dialog pop up & user selected the rev C instead of rev D it will pick up the data from rev C… that not ok right?

Yes sir I did work on that space thing, after running the code I saw extra space was there.
MEA D6463 - SAF V REV C \         previous code returns this
MEA D6463 - SAF V REV C\          What you may be needing instead

as mentioned on single space and the backslash I have made small addition of {& “\” &} in subfolder
subFolder = .[D$6] & "\" & .[$D$3] & " - " & .[$D$4] & "\" & "REV " & .[$D$5] & "\"
Which reads as {MEA\D6463 - SAF V\REV C\}
Which is actually working & there is no popup file browser dialog once change the name of file it goes back to the target file & get the data from {Report 1'!$G$48}

Up to this point it’s working perfect

But file name is still not changing in column G{=HYPERLINK("\\share\Group...... REV D\D6463 - C411 - SAF V Bulks Rev C.xlsb","Link"} as per the changes in D3:D6 (unless run the macro twice)

Not sending you screen shot, plz find the attached workbook & necessities of the data requirements.

Original test file of Summary Report REV X.xlsb is exact file & formulas Im working right now.
Test file of Summary Report REV X.xlsb is where Im running the codes

1)      Linking all the 4 / 3 workbooks in master sheet to get the total value
2)      Linking the cost breakdown in the all the different sheets e.g. Category BD, SteelSummary etc.
3)      From all the sheets getting the data to SumReport sheet

With {=HYPERLINK("\\share\Group} which is/was always required to open the file then linked data in (column F, 9 to11) with the INDIRECT formula { =IFERROR(INDIRECT("'["&E9&"]Report 1'!$G$48"),0) }
& connect other worksheets with Master Sheet's (column E, 9 to11) {=INDIRECT("'["&Master!$E$9&"]Report 1'!D83")}
as u can see in the worksheets but the problem with INDIRECT is, it works only the source file is open otherwise #REF.

[There is INDIRECT.EXT function which says can extract data from the closed source files but I cant install as per the policy.]

I have worked a lot to Link all these formulas & extracting the data for the target files. but now trying to make is automated & user friendly

Original test file of Summary Report REV X.xlsb is exact file & formulas Im working right now.
test file of Summary Report REV X.xlsb is where Im running the codes

once again thanks a lot & really appreciates your work & support.
Best Regards,
MZ
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Brad,
Thanks for updated codes, before I do the test run the above codes,
Just couple of queries,
1.      You are saying Worksheet_Change event sub should be add in SumReport code pane, while our code is reading range D3:D6 in/from master sheet?
2.      I didn’t understand this (I only need worksheet SumReport to write the code. If you need to keep the rest of your workbook proprietary, just move that worksheet into a new workbook and post that.) plz explain?
3.      As I said & you can see in my file cost breakdown data is linked with in the all the different sheets e.g. Category BD, SteelSummary etc.
4.      SumReport have linked picture from the above mentioned sheets & If data get connected with those worksheets then I can/will link required data to SumReport with e.g. (='Category BD'!J5) formula. In that case no need to write any code for SumReport but need those code/codes for Category BD, Top Value SteelSummary etc.
Avatar of M Zahid

ASKER

Further to above

You are welcome Sir, Im glad it helps you to take us further on our project to getting final stages
Yes this "Original" workbook contains 100% correct path & formula, which connect the data with the whole file.

Yes Im using INDIRECT & its works/recalculate whenever anything changes in any cell if the target workbook open, that’s why need your help to eliminated those by using VBA code that updates when needed. Absolutely correct this is the technique I am looking for the one you are helping me with.

OFFSET can be eliminated by building the formula around INDEX instead. I didn’t understand that neither Im using OFFSET in my workbook??

Above code is perfectly working up to this point but now I have to move the extract the data in my other work sheets as well e.g. as mentioned in my file.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Brad,

Seems like I may have made you upset with any of my comments or question, I m really sorry & apologetic on what made you upset.
response on your answers,

1. Yes Sir I wanted the formulas and hyperlinks to update automatically when I change the inputs in D3:D6 & I do understand of Worksheet_Change event sub. Which I have placed in the Master sheets Code Pane & that sub is running after each change in inputs.

“For testing purposes, it is OK to not have such a sub. But once everything works to your satisfaction, the final part of the solution is putting that sub in the code pane for worksheet SumReport.”
I think we hv small mix-up in the name of worksheets (Master & SumReport) as you can see in the Original File you have.
(Column D, E, F & G rows 1 to 12) on code you are helping me those are in worksheet name Master & SumReport is the 2nd worksheet which is colored in purple.

2. Thanks you Sir & I don’t know how to express my gratitude on that you will keep protect our company’s property.
As per the two codes you have wrote in/for worksheet (Master) (Column D, E, F & G rows 1 to 12) Worksheet_Change & BuildHyperlinksAndGetValues are working fine & that return data in column F from a closed workbook name comes in column E.

3. & 4.  Yes Sir I still do need your help to which part of code or which code to modify so that we can extract the data from workbook name (e.g. D6463 - C411 - SAF Linepipe Rev A.xlsb) mentioned in Master worksheet under column E, I have tried a lot but not getting the solution to implement that codes to across multiple worksheets.
Sir If you plz help me with code/macro to get data the in next worksheet name Category BD, then I will do rest for all other worksheets.

Here is some conclusion
when we run the Worksheet_Change & BuildHyperlinksAndGetValues codes the result formula we are getting in column F is =IFERROR('\\share\Group\DEPT\GPO Bids\1. C411 Price Form\MEA\D6463 - SAF\REV B\[D6463 - C411 - SAF Equip Rev B.xlsb]Report 1'!$G$48,0) = $1235647 & not extracting the data to rest of the other sheets.
But if we click the "Link" or hyperlink in column G its changes the formula to this in column F =IFERROR('[D6463 - C411 - SAF V Bulks Rev B.xlsb]Report 1'!$G$48,0) = $1235647 & its open the source file & extract the data for rest of the other sheets as well

Therefore Can we modify below code
{frmlaF = "=IFERROR('" & flPath & subFolder & "[" & frmlaE & "]Report 1'!$G$48,0)"}
& which shd read as result {=IFERROR('[D6463 - C411 - SAF Bulks Rev D.xlsb]Report 1'!$G$48,0)}
Which might help to extract the data for rest of the other worksheets,

Request for your kind advice.
Avatar of M Zahid

ASKER

Hi Brad,

can you plz help me above or shall i post new question?

plz plz plz help me :(
I am on the road for the next week, and have limited availability.  I do plan to respond to your issues, but it will take longer than usual.
Avatar of M Zahid

ASKER

Hi Brad,

Thanks for your response, thats fine & have nice & safe road trip.
will wait for your response.

Best Regards,
M Zahid
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hi Brad,

Hope you had a nice road trip, thanks you very much for response back & really appreciates Help & Support. I have run the above code & its run perfectly with opening the source file but there is small hitch in Currency BD worksheet.

Im sorry it was over looked by me while working on this file, actually in [Report 1'!C54:G74]
{C, D, E, F & G} column E & F are merged & may be that’s why Value for column G is not reflecting in Column E, K & Q its show 0

Rest of all B5:D25, H5:J25 & N5:P25 are okay

Plz advice if we have to modify the below code to skip to column F & read column G or shall we make it to read up to B5:D25, H5:J25 & N5:P25

Worksheets("Currency BD").Range("B5:E25").FormulaArray = "=" & fullPath & "Report 1'!C54:G74"
Worksheets("Currency BD").Range("H5:K25").FormulaArray = "=" & fullPath & "Report 1'!C54:G74"
Worksheets("Currency BD").Range("N5:Q25").FormulaArray = "=" & fullPath & "Report 1'!C54:G74"

Best Regards,
M Zahid
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

after adding the above code getting error  "You cannot change part of an array"
& asking for Debug
" Run time error '1004':
Unable to set the FormulaArray property  of the Range Class"

Worksheets("Currency BD") .Range("N5:P25") .FormulaArray = "=" & fullPath & "Report 1'!C54:E74"

above is yellow highlighted..

plz advice
You need to clear the array formula from the cells prior to running the macro. You only need to perform this step once, and can run the macro as many times as you like afterwards.
Avatar of M Zahid

ASKER

Thank you very much Brad (byundt) & I don’t know how to express my gratitude for helping me out for the subject challenging situation.
I wish I could give 1,000,000 points on this…

Best Regards
M Zahid

PS. With humble request for discarding the file to protect the property plz
To clear the array formulas, select all the cells that contain the formulas (e.g. B5:E25). Then clear the formulas by holding the Shift key down and hitting Delete. I have done that on the attached file, then run the macro. So you should be able to run it again without encountering an error message.

I got tired of hitting the Cancel button 39 times (for each block of cells where the formulas were updated), so I modified the macro by turning alert messages off.
Sub BuildHyperlinksAndGetValues()
Dim flName As String, flPath As String, frmla As String, frmlaE As String, frmlaF As String, frmlaG As String, fullPath As String, _
    subFolder As String
Dim i As Long, iFirst As Long, iLast As Long
Application.ScreenUpdating = False  'Makes macro run faster
Application.Calculation = xlCalculationManual   'Makes macro run faster
Application.DisplayAlerts = False
'On Error Resume Next
With Worksheets("Master")
    flPath = "\\share\Group\DEPT\GPO Bids\1. C411 Price Form\"
    subFolder = .[D$6] & "\" & .[$D$3] & " - " & .[$D$4] & "\" & "REV " & .[$D$5] & "\"     'no space before last \
    iFirst = 9
    iLast = 12

    For i = iFirst To iLast
        frmlaE = .[$D$3] & " - C411 - " & .[$D$4] & " " & .Cells(i, "D").Value & " Rev " & .[$D$5] & ".xlsb"
        fullPath = "'" & flPath & subFolder & "[" & frmlaE & "]"
        frmlaF = "=IFERROR(" & fullPath & "Report 1'!$G$48,0)"
        frmlaG = "=HYPERLINK(""" & flPath & subFolder & """&E" & i & ",""Link"")"     'Use when fully debugged
        
            'Use once fully debugged
        .Range("E" & i).Value = frmlaE
        .Range("F" & i).Formula = frmlaF
        .Range("G" & i).Formula = frmlaG
        
            'Put IFERROR formulas to retrieve data on worksheets: _
    Category BD, Top Value, SteelSummary, Discipline, Client Approved Cat, NUMBER OF RFQ MTOs, Offers Validity Status & Currency BD
        Select Case i
        Case 9
            Worksheets("Category BD").Range("Q5:S20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
            
            Worksheets("Top Value").Range("B2:D2").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
            Worksheets("Top Value").Range("E134:J224").FormulaArray = "=" & fullPath & "Top Values'!G10:L100"
            Worksheets("Top Value").Range("B135").Formula = _
                "=IF(" & fullPath & "Top Values'!C11=0,B134," & fullPath & "Top Values'!C11)"
            Worksheets("Top Value").Range("B135").Copy
            Worksheets("Top Value").Range("B135:D224").PasteSpecial xlPasteFormulas
                
            Worksheets("SteelSummary").Range("C14:E16").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!Q12:S14,0)"
            
            Worksheets("Discipline").Range("K6:K11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("K6:K8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("I6:I7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("L6:L11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("N5:P25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("Q5:Q25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
        Case 10
            Worksheets("Category BD").Range("J5:L20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
            
            Worksheets("Top Value").Range("B40:D40").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
            Worksheets("Top Value").Range("E40:J130").FormulaArray = "=" & fullPath & "Top Values'!G10:L100"
            Worksheets("Top Value").Range("B41").Formula = _
                "=IF(" & fullPath & "Top Values'!C11=0,B40," & fullPath & "Top Values'!C11)"
            Worksheets("Top Value").Range("B41").Copy
            Worksheets("Top Value").Range("B41:D130").PasteSpecial xlPasteFormulas
            
            Worksheets("Discipline").Range("G6:G11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("G6:G8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("F6:F7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("H6:H11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("H5:J25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("K5:K25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
        Case 11
            Worksheets("Category BD").Range("C5:E20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
            
            Worksheets("Top Value").Range("B2:D2").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
            Worksheets("Top Value").Range("E2:J33").FormulaArray = "=" & fullPath & "Top Values'!G10:L41"
            Worksheets("Top Value").Range("B3").Formula = _
                "=IF(" & fullPath & "Top Values'!C11=0,B2," & fullPath & "Top Values'!C11)"
            Worksheets("Top Value").Range("B3").Copy
            Worksheets("Top Value").Range("B3:D33").PasteSpecial xlPasteFormulas
                
            Worksheets("SteelSummary").Range("C5:E8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!Q15:S18,0)"
            
            Worksheets("Discipline").Range("C6:C11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("C6:C8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("C6:C7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("D6:D11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("B5:D25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("E5:E25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
        End Select
    Next
End With
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

Open in new window

-LoopFilesInFolderQ28983140.xlsm
Avatar of M Zahid

ASKER

Thanks Brad & I m sorry I couldn’t response you at same as i have done the same last evening to clear the array formulas & it’s working perfect

On alert messages display off, actually I prefer to keep it on bcoz if someone put the wrong file name or path it should alert that there is something wrong & need to recheck, m I right Sir?
I agree with you on allowing the alert messages to occur. They may be irritating, but at least you know you have a problem and why.

Brad
Avatar of M Zahid

ASKER

yes sir but I had to respond back with the detailed description of the issues otherwise it won’t help right, Once again thank you sir on removal of the file & hope for the best for the further.

& let them get irritate ;) or otherwise I have to, if numbers came wrong.

Will keep in touch if come across any problem.

MZ