• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help referring to a range in another workbook in UDF vba

S

ssuresh98

Member
Hello Ninjas,

Can someone please help me? TIA.

Say I have a workbook with a worksheet "Report". I have the following UDF which works fine when I have the "Report" worksheet on the same workbook. I would like to modify this UDF to lookup a worksheet ("Range") in another workbook on the server(say filesReport.csv) but do not know how to refer to that range, everything else being the same.


Public Function Name(datacell As Range)

Dim Column As Integer

Dim table As Range

Column = 2

Set table = Range("Report!a1:au1525")

Name = Application.WorksheetFunction.VLookup(datacell, table, Column, 0)

End Function
 
Hi, ssuresh98!

You can define ranges in many ways:

a) same worksheet: set table = Range("a1:au1525")

b) same workbook, another worksheet: set table = Worksheets("SheetX").Range("a1:au1525")

c) another workbook: set table = Workbooks("WorkbookX.xlsx").Worksheets("SheetX").Range("a1:au1525")

Hope it helps.

Regards!
 
Hi SirJB7,

Thanks for helping out. I tried the your solution (c) but still got an error (#Value). Could this be because I am trying to access an file on the server and have not defined the path? Can you please help me define the path?

For eg: "FilesReport"
 
Hi SirJB7,

An update, solution (c) works as long as I have the server file open and I don't define the path. Can this UDF work with the reference file closed?


TIA
 
If the workbook is not open, you'll have to define the full file path name.

Code: 
set table = Workbooks("C:My DocumentsWorkbookX.xlsx").Worksheets("SheetX").Range("a1:au1525")
 
Hi, ssuresh98!

Check Luke M correction, as I assumed that the server workbook would be opened.

Regards!


@Luke M

Hi!

Thanks for the catch, didn't think it might be closed.

Regards!
 
Thanks LukeM,

I tried that but it does not work.

set table = Workbooks("FilesReport.csv").Worksheets("Report").Range("a1:au1525").

As soon as I change to full path the UDF fails (whether the workbook is open or closed does not matter)
 
Hmm, upon further digging this is tricker than I thought. Have a read here:

http://chandoo.org/forums/topic/get-the-value-of-named-ranges-from-a-closed-workbooks


or here:

http://www.dailydoseofexcel.com/archives/2012/04/07/access-data-in-a-closed-workbook-containing-a-protected-worksheet/
 
Thanks All. I am at work and will take a look at the suggested reading material and get back later this evening (PST).

This community is rich with a lot of knowledgeable and helpful people. The combination of which makes it unique.
 
Hi, ssuresh98!

Wait till you get Luke M's bill... :)

Regards!


@Luke M

Hi!

Don't forget my 50%... :p

Regards!
 
You must log in or register to reply here.
Back
Top