Using the Getvalue Function & ExecuteExcel4Macro

Hi! I am needing to pull information from multiple workbooks and have
found code from John in one of the EEE newsletters about the GetValue
Function that uses ExecuteExcel4Macro.

Now I know nothing about ExecuteExcel4Macro, but this was his code for
the GetValue Function taken from:
http://j-walk.com/ss/excel/eee/eee009.txt

From John:

The GetValue function, listed below takes four arguments:


path: The drive and path to the closed file (e.g., "d:\files")
file: The workbook name (e.g., "99budget.xls")
sheet: The worksheet name (e.g., "Sheet1")
ref: The cell reference (e.g., "C4")


Private Function GetValue(path, file, sheet, range_ref)

' Retrieves a value from a closed workbook

Dim arg As String

' Make sure the file exists

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function


Ok so my question is this, the worksheets in each work book are named
differently but all still have the code name of Sheet1. I was
wondering if there is a way to use the code name instead of the user
defined sheet name? Thanks for you help!!


--
solidunityPosted from http://www.pcreview.co.uk/ newsgroup access

Hi

See
http://www.j-walk.com/ss/excel/tips/tip82.htm

You can also use ADO
http://www.rondebruin.nl/ado.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"solidunity" <solidunity.1s2den@> wrote in message news:[email protected]...
>
> Hi! I am needing to pull information from multiple workbooks and have
> found code from John in one of the EEE newsletters about the GetValue
> Function that uses ExecuteExcel4Macro.
>
> Now I know nothing about ExecuteExcel4Macro, but this was his code for
> the GetValue Function taken from:
> http://j-walk.com/ss/excel/eee/eee009.txt
>
> From John:
>
> The GetValue function, listed below takes four arguments:
>
>
> path: The drive and path to the closed file (e.g., "d:\files")
> file: The workbook name (e.g., "99budget.xls")
> sheet: The worksheet name (e.g., "Sheet1")
> ref: The cell reference (e.g., "C4")
>
>
> Private Function GetValue(path, file, sheet, range_ref)
>
> ' Retrieves a value from a closed workbook
>
> Dim arg As String
>
> ' Make sure the file exists
>
> If Right(path, 1) <> "\" Then path = path & "\"
>
> If Dir(path & file) = "" Then
> GetValue = "File Not Found"
> Exit Function
> End If
>
> ' Create the argument
>
> arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
> Range(range_ref).Range("A1").Address(, , xlR1C1)
>
> ' Execute an XLM macro
>
> GetValue = ExecuteExcel4Macro(arg)
>
> End Function
>
>
> Ok so my question is this, the worksheets in each work book are named
> differently but all still have the code name of Sheet1. I was
> wondering if there is a way to use the code name instead of the user
> defined sheet name? Thanks for you help!!
>
>
> --
> solidunityPosted from http://www.pcreview.co.uk/ newsgroup access
>

solidunity wrote...
>Hi! I am needing to pull information from multiple workbooks and have
>found code from John in one of the EEE newsletters about the GetValue
>Function that uses ExecuteExcel4Macro.
....
>Ok so my question is this, the worksheets in each work book are named
>differently but all still have the code name of Sheet1. I was
>wondering if there is a way to use the code name instead of the user
>defined sheet name? Thanks for you help!!

There's no way to use worksheets' code names when accessing closed
workbooks. Most of Excel's object model is only usable in open
workbooks. And if these workbooks would be open, there's no need to use
GetValue - INDIRECT calls would suffice, and you could use a function
like the following to return the worksheet index corresponding to a
given code name.


Function wscn2i(cn As String, Optional wb As Workbook) As Long
'returns index or 0 if no worksheet with CodeName cn exists
Dim ws As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
If ws.CodeName = cn Then
wscn2i = ws.Index
Exit Function
End If
Next ws
End Function