Unable to open network shared files with VBA (rootpath)

[Randa]

Hi Everyone,

I was wondering if anyone can help with this: I have a vba program that consolidates data from different workbooks. I used the rootpath method and it works perfectly on my computer. However, these files will be shared by the company team. Unfortunately, whenever I put them in the shared folders on the company network, giving full access rights and removing the inheritable permissions option, I get an error message saying that the file name is wrong. So basically, my vba program is not able to access the network and read/open the different sheets. Does anyone have a solution? I couldn't find anything online.

Thanks!

[OllieB]

Randa, please post either your workbook and/or the VBA code used with example values (path, object naming etc). Excel and VBA have no problems accessing objects in a shared folder.

[Randa]

This is the code I used. Hope this helps...

Private Sub Consolidate_Click()

ThisWorkbook.Worksheets("Order Funnel").Unprotect Password:="xxxx"
Sheets("Order Funnel").Range("A7:BI50000").Clear


Dim wb, iwb As Workbook

Dim i, j, k As Integer

Set iwb = ThisWorkbook
rootpath = ThisWorkbook.Path
rootpath = rootpath & "\"


' "i" is the row we start copying from
i = 7

' there is a sheet with the file names. "k" is the row where the names of the files start from
k = 2


While iwb.Sheets("Sheet1").Range("A" & k).Value <> ""
j = 7
Set wb = Workbooks.Open(rootpath & iwb.Sheets("sheet1").Cells(k, 2).Value)
wb.Activate

While wb.Sheets("order funnel").Range("A" & j).Value <> ""

wb.Sheets("order funnel").Range("A" & j, "BB" & j).Copy

iwb.Sheets("Order Funnel").Range("A" & i, "BB" & i).PasteSpecial (xlPasteFormats)
iwb.Sheets("Order Funnel").Range("A" & i, "BB" & i).Value = wb.Sheets("Order Funnel").Range("A" & j, "BB" & j).Value

iwb.Sheets("Order Funnel").Range("AQ" & i).Formula = "=IF(RC[-14]<> ""C"", RC[-14]*RC[-11], """")"
iwb.Sheets("Order Funnel").Range("AR" & i).Formula = "=IF(OR(RC[-15] = """",RC[-15] = ""C"", RC[-10]= """"), """", RC[-15]*RC[-10])"


j = j + 1
i = i + 1


Wend

ActiveWorkbook.Close False
k = k + 1
Wend

ThisWorkbook.Worksheets("Order Funnel").Protect Password:="xxxx", _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub

[OllieB]

Randa, the code is correct at least the part of the rootpath. What would be the contents of iwb.Sheets("sheet1").Cells(k, 2).Value ? This cell should contain the file name to be opened including the extension.

[Randa]

exactly... it DOES contain the file name to be opened... and that's why my code works if the file is on my computer. However, it doesn't when it's on the shared folder.. Do you think there should be specific access rights?

[OllieB]

Randa, as you say that the error message received is "Invalid file name" I am not sure whether it would be a permission problem. I assume your parent workbook (running the code) is also stored in the same network folder as the workbooks to be processed, right ?

[Randa]

yes.. it is in the same folder.. this is frustrating.. it's as if it's not recognizing the path. that's why I assumed it's an access right problem. I am completely lost...

[OllieB]

Can you add the following statement just before the Open instruction

Please Login or Register  to view this content.

Tell me what value is shown in the immediate window when the error occurs

[JosephP]

are you using a mapped network drive? if so do the others have the same drive mappings?

[Randa]

Ollie, I'm not getting anything except the following error message..

Error Message.png

[Randa]

I have no clue.. It's my company's network drive. What is a mapped network drive (sorry.. ignorant a bit)?
I have tried opening the folder from someone else's computer and it gave the same error message. The path in the error message is correct... I just don't get it...

[OllieB]

Where is the extension of the Excel workbook? I am missing .xls or .xls* at the end of the file name. Maybe try

Please Login or Register  to view this content.

[Randa]

It works!! I love you!
Thanks for the help

[OllieB]

You are very welcome