Opening EXCEL with additional Parameters

[edfollett]

Hey all,

Yet another question from me!

Basically i have an excel spreadsheet that will be distributed an
generated by a bespoke system the company i work for has created.

I have been asked to make a few alterations to the file and am (
believe) a the last hurdle before i am finished. So please help if yo
can:

We want to open the Excel workbook i have created and pass additiona
parameters when opening the book

is there a way of doing somehting like the following:

open(C:\<file location>\test.xls <parameter>='test')

and then retrieve the parameter values (in this case test) within th
VBA code?


i really hop e that makes sense!

e

 

[ppyxl]

If you only want the file name, u can use:

dim name as string

name=activeworkbook.nam

 

[NickHK]

ed,
Not that way, but
Dim WS as worksheet
set ws=application.worksheets.open(FullPathToFile)
Dim RetVal as variant
RetVal=Application.Run(ws.name & "!Your_Function", 1, 5, "Whatever")

NickHK

 

[Norman Jones]

Hi Ed,

Not that way, but
Dim WS as worksheet
set ws=application.worksheets.open(FullPathToFile)
Dim RetVal as variant
RetVal=Application.Run(ws.name & "!Your_Function", 1, 5, "Whatever")

I suspect that Nick intended Workbook rather than Worksheet, e.g.:

Dim WB As Workbook
Dim RetVal As Variant

Set WB = Application.Workbooks.Open(FullPathToFile)
RetVal = Application.Run("'" & WB.Name _
& "'!Your_Function", 1, 5, "Whatever")

I have also encased the workbook name in single quotes to allow for possible
spaces in the wb name.

 

[NickHK]

Norman,
Yea, too quick on the typing and too slow on the checking.

Cheers.

NickHK