XL97 Workbook Issue 2

[vaneagle]

Hi,

I am having an issue with the following code. The wb2 is not recognising the 2nd workbook (or file) i have open. As far as the code is concerned, wb and wb2 are the same file!!

Private Sub Workbook_Open()
Dim WB As Workbook
    Dim WB2 As Workbook
    Dim JunkData As New DataObject ' Declare Variable to clear out clipboard
    Dim newfile As String
    
    Application.DisplayAlerts = False
    Set WB = Workbooks(Dir(ActiveWorkbook.FullName))
    Debug.Print WB.FullName
    Sheets("Menu").Select
    Range("A1").Select
    Workbooks.Open FileName:="C:\temp\PAWeek7.xls", ReadOnly:=True
Set WB2 = Workbooks(Dir(ActiveWorkbook.FullName))
    Debug.Print WB2.FullName
    WB2.Activate

As far as wb is concerned it is the current file that is open. the code is then to go and open the second file (being PAWEEK7.xls) and it should then recognise this as wb2...

Any ideas on how to fix this ?

 

[tgerits]

Hi,

I tested your code in Excel XP, and the code runs right.
The file will be always opend in read-only mode.

Are there any errors reported when you run the code?

 

[xlbo]

To make it clearer - when you want to refer to the workbook that houses the code, you can use

set WB = THISworkbook
workbooks.open FileName:="C:\temp\PAWeek7.xls", ReadOnly:=True
Set WB2 = activeworkbook

Why you are using Dir(workbook.name) I don't know - being as you have dimmed WB and WB2 as workbooks

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vaneagle]

I tried your suggestion above XLBO and it gives me the following error:

Run-time error '-2147352565 (8002000b)':
application-defined or object-defined error

of which I have no idea what it means!!!

Its weird as when I step through the code, the right file is active and 'on top' in excel, yet in the code it does not recognise the file.

Private Sub Workbook_Open()
Dim WB As Workbook
    Dim WB2 As Workbook
    Dim JunkData As New DataObject ' Declare Variable to clear out clipboard
    Dim newfile As String
    
    Application.DisplayAlerts = False
    set WB = THISworkbook
    'Debug.Print WB.FullName
    Sheets("Menu").Select
    Range("A1").Select
    Workbooks.Open FileName:="C:\temp\PAWeek7.xls", ReadOnly:=True
    Set WB2 = activeworkbook
    'Debug.Print WB2.FullName
    WB2.Activate
    Sheets("Week No").Select
    Range("b2").Select
    Selection.Copy

Within the file PAWeek7.xls there is a sheet called "Week No". The code always errors at that point. In the file where the code is stored, there is no sheet called "Week No".

What is weirder is that I pulled my original code out of 'ThisWorkbook' and placed it in a module. It works!!!
Why would there be a difference between a module and 'ThisWorkbook'?

If I run it in 'ThisWorkbook' it gives me a run-time error '9': Subscript out of range which why I assumed that it was not recognsing

any ideas?

 

[tgerits]

Hi,

You say that in the file were the code is stored, that there is no sheet called "Week No". But in your code snippet you have a line "Sheets("Week No&quot...". I think that this line refers to the sheets in "thisworkbook" of the file where the code is in. Maybe you can try the following:

WB2.Sheets("Week No").Select

WB2 refers to the PAWeek7.xls, in which there is a sheet called "Week No".

regards,
T.

 

[xlbo]

Ok - the Thisworkbook MODULE is relevant to the workbook - it houses events that happen like open / close / save etc
Unless you are working with these events, your code should be in a standard module

set WB = thisworkbook
will work...it is correct...there is no question about it.
Thisworkbook will ALWAYS refer to the workbook that houses the code. When you open a workbook, it automatically becomes the ACTIVE workbook therefore, the snippet I posted will work but you may need to work on your other references

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vaneagle]

Thanks for your help guys.. have a star each...

Thanks XLBO for your help with

 set wb = thisworkbook

I will use this going forward....

Yes I want the workbook on open to update some information in the file beofre proceeding... which it now does!!

Thanks Tgerits with you tip as well...

I don't know exactly why but between changing my workbook selection and using

 WB2.Sheets("Week No").Select

it works now...

Again, thanks..