set function

[Tren2k]

Hi Guys

I'm opening another workbook via vb and it want it to remain open and accessible whilst this workbook is open

Set databook = CreateObject("excel.Application")

databook.Workbooks.Open Filename:= _
("N:\databaseinfo.xls"), _
local:=True
databook.Visible = False
databook.Application.DisplayAlerts = False

im opening the wb as above, but when i move onto a different module 'databook' is no longer accessing the open book. Is there anyway to keep databook open for all modules?

Thanks

 

[Ruddles]

How do you declare databook? It should be in a general code module:-

Public databook As Excel.Application

Then declare a Public variable As Workbook and open the workbook thus:-

Set [I]variable[/I]=databook.Workbooks.Open

I think that should do what you're asking.

PS. The name databook is a bit misleading as this actually points to the Excel application itself, rather than a workbook.

 

[RichardMGreen]

Why are using another session of Excel?
If you dim database as workbook at the top, you can then use?
set databook=workbooks.open("N:\databaseinfo.xls")

This will open the workbook for you and you can then use things like:-
databook.visible=false
so it's still hidden.

 

[Tren2k]

Thanks guys, basically this is just a small company contacts database which was all stored in one file, however different people saving it over the network on x64 and x86 machines are causing it to crash for others when reopening, therefore I was planning to just keep the data in a separate sheet so the file with all the code in is never saved over.

it doesnt have to be called databook. i just want it to open when i open the main workbook with the code in and stay open so the data can be updated until i close the main workbook

 

[RichardMGreen]

As long as you keep the name consistent through the code and dim it as a workbook, the code will work fine.

You could even make it a public variable is you have lots of code that might refer to it.