Project Name vs. Workbook Name

[Chris Laycock]

Hi

I need to write some VBA that refers to another Workbook - who's name
will keep changing but who's Project Name will always remain the same.

Can you use a Project Name in place of a Workbook Name in an

Application.Workbooks("Book1.xls).Worksheets("Sheet1").Range("A1").Value

style reference?

Chris Laycock

 

[keepITcool]

but if you use object variables there should be no problem:

set wkbDATA = workbooks("mydata 13072004.xls")

with wkbdata.worksheets(1)
.Range("a1").value = thisworkbook.Range("constants!c3").value

etc..

note i've used a numeric index to reference the worksheet.
If your users use a French/Dutch or other international excel version you
cannot depend on the name... .. but since you have to depend on
something.. it must be the first worksheet




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepITcool]

note i've used a numeric index to reference the worksheet.
If your users use a French/Dutch or other international excel version
you cannot depend on the name... .. but since you have to depend on
something.. it must be the first worksheet

hmmm.. ofcourse worksheet names dont change by themselves,
i was thinking about newly created sheets ..

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Chris Laycock]

Apologies for my lack of clarity

What I meant to describe was an ongoing scenario whereby Excel is
Open, Used and Closed many times over time and while one file remains
unchanged (and I don't want to have to keep changing it) the other
file name will change many times a day as I increment up the versions.

Hence an Object Variable solution would only work for the same
instance of Excel and not when that instance is closed and the name is
then changed.

An example might help: Imagine I wish to keep a track of my shares –
IBM, Apple etc, but the files that I receive for these each day have
the Date included in the FileName so that it's always changing – but
the Project name remains the same as the company isn't changing

 

[keepITcool]

Ah..

you COULD do it by the project name,
however if the user has not enabled "trust access to VB Project" in
macro security options it will fail with a 'generic' 1004 error:
Programmatic Access to Visual Basic Project not trusted".
(error message localized in intl versions of excel)


IMO a better approach would be to rename ThisWorkbook..
then following will work nicely:

Function GetBookByCode(CodeName As String) As Workbook
Dim wb As Workbook
For Each wb In Workbooks
If StrComp(wb.CodeName, CodeName, vbTextCompare) = 0 Then
Set GetBookByCode = wb
Exit Function
End If
Next
End Function

HTH


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >