opening diff. worksheets in opened workbook

[AlexD]

How could I open another spreadsheet in the same workbook
if the workbook has been already opened?
I'm doing it from MS Access.

If logWorkbookIsOpened = False Then
'it's working good
Set xlBook = xlApp.Workbooks.Open(strWorkbookName)
Set xlSheet = xlBook.Worksheets(strWorksheetName)
xlSheet.Activate

xlApp.Visible = True

Else
'here is a mistake #0
Set xlSheet = Workbooks(strWorkbookName).Worksheets
(strWorksheetName)
xlSheet.Activate
xlApp.Visible = True

End If

Thanks

 

[Patrick Molloy]

from your main access vba call a sub that assigns the
workbook to a variable - assumes the workbook is open. If
its not then it would raise an error...that's trapped by
testing the err number , a non-zero value is an
error...in which case it then opes the book...either way,
it returns the workbook object assigned tot hat workbook
to the calling code


dim wb as workbook
Set WB = GetWorkbook("MyBook")
if WB Is Nothing then
msgbox "Cannot find " & "MyBook"
exit sub
end if
.... workbook assigned to wb, carry on...
.... your access code



Private Function GetWorkbook(sName as string) as workbook
on error resume next
dim wb as workbook
set wb = xlApp.Workbooks(sName)
if err.number<>0 then
err.clear
set wb = xlApp.Workbooks.Open(sName)
end if
set GetWorkbook = wb
End Function


HTH

Patrick Molloy
Microsoft Excel MVP

 

[Bob Phillips]

Set xlBook = xlApp.Workbooks.Open(strWorkbookName)
Set xlSheet 1= xlBook.Worksheets(strWorksheetName1)
Set xlSheet 2= xlBook.Worksheets(strWorksheetName2)



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[AlexD]

Thanks a lot.

But, if user press a button on some form to open the
workbook with a spreadsheet1 and didn't close the workbook
and wants to open another spreadsheet2 in the same
workbook by pressing the button the procedure will open
another instance of workbook with the spredshhet2. My
question is how to avoid openning the second and so on
workbooks if we just need to open another spreadsheet.

Regards,

Alex